Concatenate Date & Time

I have 2 fields, dtDate and dtTime. dtDate is datetime and dtTime is
nvarchar(8) type. I have 2 problems.
1. I need a way to concatenate the 2 fields into 1 datetime field with a
select statement
2.In my example data below, you can see that dtTime is in a "military" time
format. Is there a way within SQL to convert it to a normal time format with
the AM/PM?
Any ideas or help would be greatly appreciated.
Example of Data ******************
dtDate dtTime
---
3/14/2006 12:00:00 AM 01:21:57
3/15/2006 12:00:00 AM 14:42:53Why are these separate? Anyway, try this, untested...
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
Of course, this will only work if all of your dtTime values are valid times.
Since you chose NVARCHAR for some reason, this is an extra hassle to
validate / constrain.
"Scott Bailey" <sbailey@.mileslumber.com> wrote in message
news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>I have 2 fields, dtDate and dtTime. dtDate is datetime and dtTime is
>nvarchar(8) type. I have 2 problems.
> 1. I need a way to concatenate the 2 fields into 1 datetime field with a
> select statement
> 2.In my example data below, you can see that dtTime is in a "military"
> time format. Is there a way within SQL to convert it to a normal time
> format with the AM/PM?
> Any ideas or help would be greatly appreciated.
>
> Example of Data ******************
> dtDate dtTime
> ---
> 3/14/2006 12:00:00 AM 01:21:57
> 3/15/2006 12:00:00 AM 14:42:53
>|||Your code returns just the date part like:
2006-03-14
Can you modify it to display the date and time? Also, can you have the whole
result converted to datetime format?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
> Why are these separate? Anyway, try this, untested...
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
> Of course, this will only work if all of your dtTime values are valid
> times. Since you chose NVARCHAR for some reason, this is an extra hassle
> to validate / constrain.
>
> "Scott Bailey" <sbailey@.mileslumber.com> wrote in message
> news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||One last note, I didn't create this db, I just inherited it or I would never
have split the Date and Time into different fields.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
> Why are these separate? Anyway, try this, untested...
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
> Of course, this will only work if all of your dtTime values are valid
> times. Since you chose NVARCHAR for some reason, this is an extra hassle
> to validate / constrain.
>
> "Scott Bailey" <sbailey@.mileslumber.com> wrote in message
> news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||I was basing it on this:
SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
Which returns:
2006-04-19 7:40:33 PM
Maybe it will work better like this:
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
I would put this into a view so you don't have to repeat this calculation
everywhere.
If that still doesn't yield the correct results, then please post DDL and
sample data so we can actually try and reproduce your issue.
"scott" <sbailey@.mileslumber.com> wrote in message
news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Your code returns just the date part like:
> 2006-03-14
> Can you modify it to display the date and time? Also, can you have the
> whole result converted to datetime format?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||I was basing it on this:
SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
Which returns:
2006-04-19 7:40:33 PM
Maybe it will work better like this:
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
I would put this into a view so you don't have to repeat this calculation
everywhere.
If that still doesn't yield the correct results, then please post DDL and
sample data so we can actually try and reproduce your issue.
"scott" <sbailey@.mileslumber.com> wrote in message
news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Your code returns just the date part like:
> 2006-03-14
> Can you modify it to display the date and time? Also, can you have the
> whole result converted to datetime format?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||thank you. it works.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ug$RJsAZGHA.1228@.TK2MSFTNGP02.phx.gbl...
>I was basing it on this:
> SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
> Which returns:
> --
> 2006-04-19 7:40:33 PM
> Maybe it will work better like this:
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
> I would put this into a view so you don't have to repeat this calculation
> everywhere.
> If that still doesn't yield the correct results, then please post DDL and
> sample data so we can actually try and reproduce your issue.
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
>

Concatenate 2 date fields to show long date

How can I concatenate 2 fields (both declared as datetime) to show them in long date format.
I tried placing a hidden text box with each individual field formatted as long date. This works. However when I try joining these 2 fields in a new text box I get invlaid expression using the following syntax:
me.hidDateFrom.Value & me.HidDateTo.Value

This seems like an easy feat, but obviously not...

Thanks for any pointers.

You have to explain this better. Are you trying to get 2 column values (both datetime type) in one TextBox control by simply concatenating the string values? You may want to show what the column values are in your table as well as what you expect to see within the TextBox.|||

OK.

I'm trying to concatenate to datetime parameter values so that I get:
Report for the period 15 March 2006 to 20 March 2006.
15 March 2006 being the data returned from the startDate parameter whose value using this example is 15/03/2006 and 20 March 2006 being the data returned from the EndDate parameter whose value using this example is 20/03/2006.

I need to format the 2 datetime parameters to long dates AFTER input to show in a text box.
Hope this is more clear.

Concat and convert datetime

Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
ImranCan you show us the table structure and some sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATET
IME,col2,120)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> datetime
>

Concat and convert datetime

Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
Imran
Can you show us the table structure and some sample data?
http://www.aspfaq.com/5006
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>
|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>
|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,1 20)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> datetime
>
sqlsql

Concat and convert datetime

Hi,
I have two columns name indate and intime. Both of these fields are in
varchar type. I need to concat two such a way that it looks like a datetime
type. Or if there is a way to convert this varchar type into datetime
datatype then that would work too.
Thanks.
ImranCan you show us the table structure and some sample data?
http://www.aspfaq.com/5006
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||Imran
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
FROM #Test
"Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> Hi,
> I have two columns name indate and intime. Both of these fields are in
> varchar type. I need to concat two such a way that it looks like a
datetime
> type. Or if there is a way to convert this varchar type into datetime
> datatype then that would work too.
> Thanks.
> Imran
>|||CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES ('20040101','15:00')
INSERT INTO #Test VALUES ('20040102','22:00')
GO
SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
FROM #Test
SELECT CONVERT(DATETIME,col1 + ' ' + col2)
FROM #Test
DROP TABLE #Test
--? Is that what you're wanting?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eWRjUGrtEHA.1336@.tk2msftngp13.phx.gbl...
> Imran
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 VARCHAR(10)
> )
> GO
> INSERT INTO #Test VALUES ('20040101','15:00')
> INSERT INTO #Test VALUES ('20040102','22:00')
> GO
> SELECT CONVERT(DATETIME,col1,120)+CONVERT(DATETIME,col2,120)
> FROM #Test
> "Imran Prasla" <ImranPrasla@.discussions.microsoft.com> wrote in message
> news:A67474DE-36E9-48DA-A07C-A583F159E7DB@.microsoft.com...
> > Hi,
> > I have two columns name indate and intime. Both of these fields are in
> > varchar type. I need to concat two such a way that it looks like a
> datetime
> > type. Or if there is a way to convert this varchar type into datetime
> > datatype then that would work too.
> >
> > Thanks.
> > Imran
> >
>

Concantenating Dates

I am transferring data between DB's. One has DATE and TIME as two individual fields. I need to concantenate them into a single DATETIME field. I am sure the solution is simple but I can't figure it out.
Any advice?
Thanks!If you select from your time column, you should see that it's date is set to January 1st, 1900, like this:
1900-01-01 14:59:27.293

Your date column should show a date as of midnight like this:
2003-07-14 00:00:00.000

If this is the case, you can just add these value together to concatenate them:
select @.Yourdate + @.Yourtime

If this is not the case, you will need to concatenate them as formatted strings and then cast or convert the result to a datetime value.

blindman

Computing SUM on DATETIME datatype

hi everybody,
i'm trying to calculate the 'SUM' of time spent in hrs. n min. How can i do this using SQL Server?
What i mean is, i've a column 'TIME_SPENT' that has 'datetime' datatype. This column saves time spent for an activity in format 'hh:mm'. Suppose a user spends 45min for activity 'A' and say 1hr 25 min for activity 'B' then i want to calculate the 'SUM' of 'TIME_SPENT' for the user which should appear as 'Total time spent =2:10'

Can somebody pls help me with this?

Thnx in advance.create table #timetable (username varchar(50),timespend varchar(8))

insert into #timetable values ('joe','03:01')
insert into #timetable values ('joe','00:01')
insert into #timetable values ('foo','00:03')
insert into #timetable values ('foo','01:02')



select username,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+timespend+':00')),'19000101'),8)
as t_timespend
from #timetable
group by username|||hey thnx for ur reply mallier,

ur code works great for the example u explained. But when i try to run it for my table in the database it gives following error:-
'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'

My code goes as follows:-

select empid,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+time_spent+':00')),'19000101'),8) as 'Time spent'
from timesheet
where empid=9
group by empid
can u pls guide me on this?

thnx once again.|||try this select query on ur table (I hope data is in 'hh:mm' format)


create table #timetable (username varchar(50),timespend char(8))

insert into #timetable values ('joe','03:01')
insert into #timetable values ('joe','00:01')
insert into #timetable values ('foo','00:03')
insert into #timetable values ('foo','01:02')



select username,case when days>0 then cast(days*24+cast(left(times,2) as int)as varchar)+':'+right(times,2)
else
times
end as total_time
from
(
select username,
datediff(day,'19000101',dateadd(second,sum(datedif f(second,'19000101','1900-01-01T'+ltrim(rtrim(timespend))+':00')),'19000101'))
as days,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+ltrim(rtrim(timespend))+':00')),'19000101'),8 ) as times
from #timetable
group by username
) as tm|||create table #babu ( names varchar(10),times varchar(10))

insert into #babu values ('babu0','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu0','01:20')

select names, dateadd(second,sum(datediff(second,'1900-01-01',convert(datetime,times))),'1900-01-01') from #babu group by names|||create table #babu ( names varchar(10),times varchar(10))

insert into #babu values ('babu0','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu0','01:20')


select names, dateadd(second,sum(datediff(second,'1900-01-01',convert(datetime,times))),'1900-01-01') from #babu group by namesthat wont help him,b'cos datatype of time column is char and he dont want see the date value too.

Compute sum of count(*) with group by

Hi,
Given the following table and test data:
CREATE TABLE test (
recordId numeric(18, 0) NOT NULL,
spId int NOT NULL,
startTime datetime NULL,
endTime datetime NULL )
INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33')
INSERT INTO test VALUES (2,2,'2005-01-01 12:26','2005-01-01 14:00')
INSERT INTO test VALUES (3,1,'2005-01-01 14:00','2005-01-01 14:33')
INSERT INTO test VALUES (4,2,'2005-01-01 14:00','2005-01-01 15:15')
INSERT INTO test VALUES (5,1,'2005-01-01 15:15','2005-01-01 15:20')
INSERT INTO test VALUES (6,2,'2005-01-01 15:15','2005-01-01 16:00')
INSERT INTO test VALUES (7,3,'2005-01-01 12:00','2005-01-01 14:30')
the following query lists only the spid's with non-unique spid's and
their respective counts:
SELECT spid, count(*) AS 'Count'
FROM test
GROUP BY spid
HAVING count(*) > 1 ORDER BY spid
I'm new to SQL and am having difficulty with a couple of things:
1. Modify the above query to compute the grand total for the Count, or
indeed a separate SQL statement to return just the grand total (= 6 in
this example).
2. This is the big challenge :). Taking the grouping returned by the
above query, write a query/stored procedure which looks for records
with identical spId's and the endtime of one spid equal to the
startTime of another. With the above test data, recordIds 3, 5, and 2,
4, 6 match this criteria.
Thanks very much for any help with this.1. Use a derived table construct:
SELECT SUM( total )
FROM ( SELECT spid, COUNT(*)
FROM tbl
GROUP BY spid
HAVING COUNT(*) > 1 ) D ( spid, total ) ;
2. Not sure if your requirements are clear since . Something like:
SELECT recordId, spId, ...
( SELECT MIN( t2.startTime )
FROM tbl t2 WHERE t2.spId = t1.spId
AND t2.startTime >= t1.endtime )
FROM tbl t1
ORDER BY t1.spid, startTime ;
If, not please post the sample resultset for the dataset you posted.
Anith|||Anith Sen wrote:
> 1. Use a derived table construct:
> SELECT SUM( total )
> FROM ( SELECT spid, COUNT(*)
> FROM tbl
> GROUP BY spid
> HAVING COUNT(*) > 1 ) D ( spid, total ) ;
>
Thanks. What does the 'D' mean above?

> 2. Not sure if your requirements are clear since . Something like:
> SELECT recordId, spId, ...
> ( SELECT MIN( t2.startTime )
> FROM tbl t2 WHERE t2.spId = t1.spId
> AND t2.startTime >= t1.endtime )
> FROM tbl t1
> ORDER BY t1.spid, startTime ;
> If, not please post the sample resultset for the dataset you posted.
CREATE TABLE test (
recordId numeric(18, 0) NOT NULL,
spId int NOT NULL,
startTime datetime NULL,
endTime datetime NULL )
INSERT INTO test VALUES (1,1,'2005-01-01 12:00','2005-01-01 14:33')
INSERT INTO test VALUES (3,1,'2005-01-01 14:00','2005-01-01 14:33')
INSERT INTO test VALUES (5,1,'2005-01-01 14:33','2005-01-01 15:20')
INSERT INTO test VALUES (2,2,'2005-01-01 12:26','2005-01-01 14:00')
INSERT INTO test VALUES (4,2,'2005-01-01 14:00','2005-01-01 15:15')
INSERT INTO test VALUES (6,2,'2005-01-01 15:15','2005-01-01 16:00')
INSERT INTO test VALUES (7,3,'2005-01-01 12:00','2005-01-01 14:30')
(Sorry, no wonder it wasn't clear as there was mistake in my original
test data. I've corrected the data above and put records with the same
spId together to make the grouping more obvious.)
So, from the above test data the expected results contain 2 sets of
matching data:
1. recordIds 3 and 5 because they have the same spId (1) and the
endTime of recordId 3 is the same as the startTime of recordId 5.
2. recordIds 2, 4 and 6 because they have the same spId (2) and the
endTime of recordId 2 is the same as the startTime of recordId 4; the
endTime of 4 is the same as the startTime of 6.
I hope that makes sense now. cheers,|||On 11 Nov 2005 09:30:25 -0800, "J Williams"
<johnwilliams_esquire@.hotmail.com> wrote:
>SELECT spid, count(*) AS 'Count'
>FROM test
>GROUP BY spid
WITH ROLLUP
>HAVING count(*) > 1 ORDER BY spid
If that does the job, great, otherwise you can always store the
results of the first query in a table an do further summations against
it.
J.|||>SELECT spid, count(*) AS 'Count'
>FROM test
>GROUP BY spid
WITH ROLLUP
>HAVING count(*) > 1 ORDER BY spid
Thanks, but that doesn't give the expected result. The basic SELECT:
SELECT spid, count(*) AS 'Count'
FROM test
GROUP BY spid
HAVING count(*) > 1 ORDER BY spid
returns:
spid Count
1 3
2 3
The grand total of Count in the above resultset is 6 and the SQL posted
earlier by Anith Sen gives this result:
SELECT SUM( total )
FROM ( SELECT spid, COUNT(*)
FROM tbl
GROUP BY spid
HAVING COUNT(*) > 1 ) D ( spid, total )|||>> What does the 'D' mean above?
D in the query stands for an alias for the derived table ( some folks
explicitly use AS keyword before the alias as well. )
Can you post the sample resultset here ( as you'd want to see on the QA
results pane ).
Anith|||Anith Sen wrote:

> Can you post the sample resultset here ( as you'd want to see on the QA
> results pane ).
First recordId, Second recordId, spId, endTime, startTime
3 5 1 2005-01-01 14:33 2005-01-01 14:33
2 4 2 2005-01-01 14:00 2005-01-01 14:00
4 6 2 2005-01-01 15:15 2005-01-01 15:15
The resultset shows pairs of 'matching' records, which is slightly
different (and better) to how I first envisioned it.
Thanks.|||This is one way of getting it:
SELECT MAX( t1.recordid ),
t2.recordid, t1.spid, t1.endtime
FROM test t1
INNER JOIN test t2
ON t1.spId = t2.spId
AND t1.endTime = t2.starttime
GROUP BY t1.spid, t2.recordid, t1.endtime ;
Anith|||That's excellent, thanks.

composite primary keys versus composite unique indexes

Hello,

I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.

The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold

1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.

A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.

I would like to have some tips, recommendations and alternatives for what I should do in this case.

One item that is not always immediately apparent for this type of scenario has to do with clustering. Is your primary key also presently your clustered index? If the answer is yes and you change to a primary key based on an identity column and leave the CLUSTERED / NOT CLUSTERED aspect of this primary key to be defaulted then any query that still accesses data based on your current primary key will experience a new performance problem -- these accesses will now also require a bookmark lookup.

The good news is that this is might not grieve you for most OLTP activity, but it is likely to impact some reports and perhaps some record lookups; however, this is rarely enough of a drawback to preclude the change your are considering. Just file keep this in mind and if some reports or lookups become slower there is a chance that this modification might be the reason.

The bigger problem usually is modifying tables that reference your table with foreign key constraints. You have two alternatives: First, you can replicate the identity column into the foreign table and now use this value as the foreign key reference; however, if you do this you should also drop the columns that references the old foreign key columns of the table that you are changing. The second alternative is to continue to reference your modified table by what has now become the UNIQUE constraint rather than the primary key. This works and I have used this a number of times but it can be unsavory. Also, if you choose this alternative you need to know the that REFERENCES clause of the foreign key constraint will now need to explicitly list the columns of the UNIQUE constraint or your FK constraint will not compile.

Also, you might have some reports, functions, or stored procedures that filter based on one of these columns in one of your foreign tables. Changing the foreign key reference might be an impact here also.

The moral of this second problem is that you want to know all tables that have foreign key constraints to the table you are considering modifying. There might be a lot of hidden additional work if your table is referenced from a couple of dozen other tables.

complex sql server 2005 query

Hi,
A sql server table is populated with records every 2 minutes. See below sample table
In the table, the Import_Date is a datetime field.

create table tblData
(
ID int identity(1, 1),
SourceID int,
SourceCode varchar(255)
Security varchar(255),
Bprice decimal(12, 8),
Aprice decimal(12, 8),
ImportDate datetime
)

Here is a populated table.
I have left gaps for better visual checks for you.

ID SourceID SourceCode Security Bprice BpriceSize Aprice ApriceSize ImportDate

1 1 sourceA SecA 100.2 2 99.12 1 2007-11-07 16:24:31.297
2 2 sourceW SecH 95.7 89.43 2007-11-07 16:24:31.297
3 3 SourceX SecS 50.56 1 76.44 4 2007-11-07 16:24:31.297
4 4 SourceQ SecZ 87.98 2007-11-07 16:24:31.297
5 5 SourceJ SecH 100.2 99.12 2 2007-11-07 16:24:31.297
6 6 SourceK SecU 2007-11-07 16:24:31.297
7 7 SourceT SecA 50.56 3 87.11 2007-11-07 16:24:31.297

8 1 sourceA SecA 100.2 6 99.12 2 2007-11-07 16:26:15.123
9 2 sourceW SecH 99.54 4 89.43 2007-11-07 16:26:15.123
10 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
11 4 SourceQ SecZ 16.98 87.98 2007-11-07 16:26:15.123
12 5 SourceJ SecH 100.2 1 99.12 2 2007-11-07 16:26:15.123
13 6 SourceK SecU 2007-11-07 16:26:15.123
14 7 SourceT SecA 50.56 2 87.11 1 2007-11-07 16:26:15.123

15 1 sourceA SecA 100.2 1 87.11 1 2007-11-07 16:26:15.123
16 2 sourceW SecH 99.66 89.43 2 2007-11-07 16:26:15.123
17 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
18 4 SourceQ SecZ 16.98 3 87.98 3 2007-11-07 16:26:15.123
19 5 SourceJ SecH 100.2 3 99.12 3 2007-11-07 16:26:15.123
20 6 SourceK SecU 2007-11-07 16:26:15.123
21 7 SourceT SecA 101.32 5 87.11 3 2007-11-07 16:26:15.123
...

I am trying to build a sql query to show which source is offering the max(Bprice) and who is offering the min(Aprice).
In addition if more than one sources are offering the same prices then they should be shown as shown below in the first record i.e. (SourceA, SourceT) --> 3 + 1 = 4
This is what I would like to see:

Security Max_Bprice Bprice_Size Bprice_SourceCode Min_Aprice Aprice_Size Aprice_SourceCode

SecA 101.32 5 SourceT 87.11 4 SourceA, SourceT
SecH 100.2 3 SourceJ 89.43 2 SourceW
SecS 50.56 2 SourceX 19.33 SourceX
SecZ 16.98 3 SourceQ 87.98 3 SourceQ

What is the sql query to do this please?

This is what I have started with but it is not correct...

select
Security,
max(Bprice) as 'Max_Bprice',
SourceCode as 'Bprice_SourceCode',
min(Aprice) as 'Min_Aprice',
SourceCode as 'Aprice_SourceCode'
from
tblData
group by
Security,
SourceCodeHi

You are almost there but not quite. Could you provide your sample data as point 3 here please:
http://www.dbforums.com/showthread.php?t=1196943

Also, your DDL does not match the data you have supplied.

Not really related, but there looks to be a third normal form issue here.

Cheers|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92298|||Here it is.
Thanks

DECLARE @.Sample TABLE (ID INT, SourceID INT, SourceCode VARCHAR(20), Security VARCHAR(20), Bprice MONEY, BpriceSize INT, Aprice MONEY, ApriceSize INT, ImportDate DATETIME)

INSERT @.Sample
SELECT 1, 1, 'sourceA', 'SecA', 100.2 , 2, 99.12, 1, '2007-11-07 16:24:31.297' UNION ALL
SELECT 2, 2, 'sourceW', 'SecH', 95.7 , NULL, 89.43, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 3, 3, 'SourceX', 'SecS', 50.56, 1, 76.44, 4, '2007-11-07 16:24:31.297' UNION ALL
SELECT 4, 4, 'SourceQ', 'SecZ', 87.98, NULL, NULL, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 5, 5, 'SourceJ', 'SecH', 100.2 , NULL, 99.12, 2, '2007-11-07 16:24:31.297' UNION ALL
SELECT 6, 6, 'SourceK', 'SecU', NULL, NULL, NULL, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 7, 7, 'SourceT', 'SecA', 50.56, 3, 87.11, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 8, 1, 'sourceA', 'SecA', 100.2 , 6, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 9, 2, 'sourceW', 'SecH', 99.54, 4, 89.43, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 10, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 11, 4, 'SourceQ', 'SecZ', 16.98, NULL, 87.98, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 12, 5, 'SourceJ', 'SecH', 100.2 , 1, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 13, 6, 'SourceK', 'SecU', NULL, NULL, NULL, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 14, 7, 'SourceT', 'SecA', 50.56, 2, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
SELECT 15, 1, 'sourceA', 'SecA', 100.2 , 1, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
SELECT 16, 2, 'sourceW', 'SecH', 99.66, NULL, 89.43, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 17, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 18, 4, 'SourceQ', 'SecZ', 16.98, 3, 87.98, 3, '2007-11-07 16:26:15.123' UNION ALL
SELECT 19, 5, 'SourceJ', 'SecH', 100.2 , 3, 99.12, 3, '2007-11-07 16:26:15.123' UNION ALL
SELECT 20, 6, 'SourceK', 'SecU', NULL , NULL, NULL, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 21, 7, 'SourceT', 'SecA', 101.32, 5, 87.11, 3, '2007-11-07 16:26:15.123'|||I'll leave it to Peso. He'll get it soon enough I would imagine.|||Thanks anyway|||I suggest that you break it up

Do one, then the other, then combine them

I think you can do a union or a join of 2 derived table.

Since each derived table is going to be a single row, you wont have to worry about a cartesian product