Some avanced SQL Query's
Hi,
i am trying to get some good info out of one of my tables which stores call detail records.
i would like to group calls a based on date and time but the catch is that the value is not the same for any given record.
in my case i have a field which sores DATE and Time like 04/21/2004 12:20 AM
so what i would like to do is group all calls based on the date portion of that field...
then i also want to group in another query all calls based on the Date + Hr portion of that field
so at the end i would like to see groups for 04/21/2004 12 AM, 04/21/2004 01 AM, 04/21/2004 02 AM and so on
hopefully there is a way to group like this without having to create new fields in the table and then group on them..
i am trying to get some good info out of one of my tables which stores call detail records.
i would like to group calls a based on date and time but the catch is that the value is not the same for any given record.
in my case i have a field which sores DATE and Time like 04/21/2004 12:20 AM
so what i would like to do is group all calls based on the date portion of that field...
then i also want to group in another query all calls based on the Date + Hr portion of that field
so at the end i would like to see groups for 04/21/2004 12 AM, 04/21/2004 01 AM, 04/21/2004 02 AM and so on
hopefully there is a way to group like this without having to create new fields in the table and then group on them..
Ooops...
I forgot you need to group by both parts....
Here is the right querry...
select convert(varchar(8), CallDate,112) , Datepart(hh,Calldate) Callhour from calls group by convert(varchar(8), CallDate,112) , Datepart(hh,Calldate)
I forgot you need to group by both parts....
Here is the right querry...
select convert(varchar(8), CallDate,112) , Datepart(hh,Calldate) Callhour from calls group by convert(varchar(8), CallDate,112) , Datepart(hh,Calldate)
ASKER
Ok, that works great except a couple of things...
a) isnt there a way to group by date without having to convert the date to a string ?
b) how can i create an output like this..
Date 1:00 2:00 3:00 4:00 5:00 6:00 7:00 and so on
05/23/04 154 12 15 157 189 192 158
05/24/04 151 18 17 125 149 158 124
a) isnt there a way to group by date without having to convert the date to a string ?
b) how can i create an output like this..
Date 1:00 2:00 3:00 4:00 5:00 6:00 7:00 and so on
05/23/04 154 12 15 157 189 192 158
05/24/04 151 18 17 125 149 158 124
You would need like 24 subquerries here.... It will be a lot of typing :)
select convert(varchar(8), CallDate,112) OuterDate, (select count(*) from Calls H1 where h1.Calldate between convert(varchar(8), OuterDate,112) + ' 1:00:00' and convert(varchar(8), OuterDate,112) + ' 2:00:00' ) H1 , (select count(*) from Calls H2 where h2.Calldate between convert(varchar(8), OuterDate,112) + ' 1:00:00' and convert(varchar(8), OuterDate,112) + ' 2:00:00' ) H2 from calls X group by convert(varchar(8), CallDate,112)
Something like this...... It will look ugly and this is not tested... Also it will most likely hurt performance...
select convert(varchar(8), CallDate,112) OuterDate, (select count(*) from Calls H1 where h1.Calldate between convert(varchar(8), OuterDate,112) + ' 1:00:00' and convert(varchar(8), OuterDate,112) + ' 2:00:00' ) H1 , (select count(*) from Calls H2 where h2.Calldate between convert(varchar(8), OuterDate,112) + ' 1:00:00' and convert(varchar(8), OuterDate,112) + ' 2:00:00' ) H2 from calls X group by convert(varchar(8), CallDate,112)
Something like this...... It will look ugly and this is not tested... Also it will most likely hurt performance...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I see a typo.
Near the beginning, change "Sum(#11am) #12am", to "Sum(#11am) #11am,", otherwise the column name #12am will duplicated.
BTW: Column names can't begin with numbers as in your example, that is why the leading # is there.
Near the beginning, change "Sum(#11am) #12am", to "Sum(#11am) #11am,", otherwise the column name #12am will duplicated.
BTW: Column names can't begin with numbers as in your example, that is why the leading # is there.
You can do it like this
select CallDate , Datepart('hh',Calldate) Callhour from calls group by CallDate , Datepart(hh,Calldate)
Just add fields as needed...
Hope this helps