Cross Tab Query Help

Sorry to bother you all, I have a Cross Tab query issue which i am not sure
how to resolve in SQL2000
Table stuff(
StartDateTime datetime
EndDateTime datetime
CodeRef varchar(20)
)
I want a report which shows
Month(e.g. Jan) Seconds between Date1 and
Date2
Coderef1 X Y
Coderef2 A B
Coderef3 S T
e.g
Jan Jantime Feb FebTime March MarchTime .
.....
AAAA 1202 22 876 18 288 34
......
BBBB 564 10 299 1 143 2
CCCC
etc etc but I have no clue on how to do in SQL.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Hi
Please post sample data
"stumpy_uk via SQLMonster.com" <u15773@.uwe> wrote in message
news:798020420af3c@.uwe...
> Sorry to bother you all, I have a Cross Tab query issue which i am not
> sure
> how to resolve in SQL2000
> Table stuff(
> StartDateTime datetime
> EndDateTime datetime
> CodeRef varchar(20)
> )
> I want a report which shows
> Month(e.g. Jan) Seconds between Date1 and
> Date2
> Coderef1 X Y
> Coderef2 A B
> Coderef3 S T
> e.g
> Jan Jantime Feb FebTime March MarchTime
> .
> .....
> AAAA 1202 22 876 18 288 34
> ......
> BBBB 564 10 299 1 143 2
> CCCC
> etc etc but I have no clue on how to do in SQL.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Uri Dimant wrote:
>Hi
>Please post sample data
>> Sorry to bother you all, I have a Cross Tab query issue which i am not
>> sure
>[quoted text clipped - 23 lines]
>> etc etc but I have no clue on how to do in SQL.
StartDateTime EndDateTime CodeRef
21/01/07 11:58 21/01/07 13:20 AAAA
22/01/07 09:00 22/01/05 12:00 AAAA
22/01/07 09:04 22/01/05 09:15 AAAA
22/01/07 10:14 22/01/05 11:24 BBBB
22/01/07 10:18 22/01/05 11:29 BBBB
Thanks
etc etc
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||Hi
How could it poosible StartDateTime is older than EndDateTime ? If you want
an accurate advise please post valied data and an expected result
Like ...
create table [stuff](
StartDateTime datetime,
EndDateTime datetime,
CodeRef varchar(20)
)
insert into [stuff] values ('20070121 11:58','20070121 13:20',' AAAA')
insert into [stuff] values ('20070122 09:00','20050122 12:00',' AAAA')
insert into [stuff] values ('20070122 09:04','20050122 09:15',' AAAA')
insert into [stuff] values ('20070122 10:14','20050122 11:24',' bbbb')
insert into [stuff] values ('20070122 10:18','20050122 11:29',' bbbb')
"stumpy_uk via SQLMonster.com" <u15773@.uwe> wrote in message
news:79857273db1e7@.uwe...
> Uri Dimant wrote:
>>Hi
>>Please post sample data
>> Sorry to bother you all, I have a Cross Tab query issue which i am not
>> sure
>>[quoted text clipped - 23 lines]
>> etc etc but I have no clue on how to do in SQL.
> StartDateTime EndDateTime CodeRef
> 21/01/07 11:58 21/01/07 13:20 AAAA
> 22/01/07 09:00 22/01/05 12:00 AAAA
> 22/01/07 09:04 22/01/05 09:15 AAAA
> 22/01/07 10:14 22/01/05 11:24 BBBB
> 22/01/07 10:18 22/01/05 11:29 BBBB
> Thanks
> etc etc
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Uri Dimant wrote:
>Hi
>How could it poosible StartDateTime is older than EndDateTime ? If you want
>an accurate advise please post valied data and an expected result
>Like ...
>create table [stuff](
>StartDateTime datetime,
>EndDateTime datetime,
>CodeRef varchar(20)
>)
>insert into [stuff] values ('20070121 11:58','20070121 13:20',' AAAA')
>insert into [stuff] values ('20070122 09:00','20050122 12:00',' AAAA')
>insert into [stuff] values ('20070122 09:04','20050122 09:15',' AAAA')
>insert into [stuff] values ('20070122 10:14','20050122 11:24',' bbbb')
>insert into [stuff] values ('20070122 10:18','20050122 11:29',' bbbb')
>>Hi
>>Please post sample data
>[quoted text clipped - 15 lines]
>> etc etc
Uri,
Sorry its just my cut and paste all the years should be 007 so taking your
example into account its just substitute the 2005 with 2007.
--
Message posted via http://www.sqlmonster.com|||Hi
select CodeRef, sum(case when month(StartDateTime)=1 then
datediff(s,StartDateTime,EndDateTime) end) as 'Jan',
sum(case when month(StartDateTime)=2 then
datediff(s,StartDateTime,EndDateTime) end) as 'Feb'
--
from [stuff]
group by CodeRef
"stumpy_uk via SQLMonster.com" <u15773@.uwe> wrote in message
news:7985b7bee8653@.uwe...
> Uri Dimant wrote:
>>Hi
>>How could it poosible StartDateTime is older than EndDateTime ? If you
>>want
>>an accurate advise please post valied data and an expected result
>>Like ...
>>create table [stuff](
>>StartDateTime datetime,
>>EndDateTime datetime,
>>CodeRef varchar(20)
>>)
>>insert into [stuff] values ('20070121 11:58','20070121 13:20',' AAAA')
>>insert into [stuff] values ('20070122 09:00','20050122 12:00',' AAAA')
>>insert into [stuff] values ('20070122 09:04','20050122 09:15',' AAAA')
>>insert into [stuff] values ('20070122 10:14','20050122 11:24',' bbbb')
>>insert into [stuff] values ('20070122 10:18','20050122 11:29',' bbbb')
>>Hi
>>Please post sample data
>>[quoted text clipped - 15 lines]
>> etc etc
> Uri,
> Sorry its just my cut and paste all the years should be 007 so taking your
> example into account its just substitute the 2005 with 2007.
> --
> Message posted via http://www.sqlmonster.com
>|||Uri Dimant wrote:
>Hi
>select CodeRef, sum(case when month(StartDateTime)=1 then
> datediff(s,StartDateTime,EndDateTime) end) as 'Jan',
> sum(case when month(StartDateTime)=2 then
> datediff(s,StartDateTime,EndDateTime) end) as 'Feb'
> --
> from [stuff]
>group by CodeRef
>>Hi
>>How could it poosible StartDateTime is older than EndDateTime ? If you
>[quoted text clipped - 24 lines]
Thank you Uri why is it so blinking obvious when someone posts it but for
days I have been trying temporary tables / updates etc etc...Thanks once
again!
>> Sorry its just my cut and paste all the years should be 007 so taking your
>> example into account its just substitute the 2005 with 2007.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1