June 5, 2009 at 12:27 pm
Everything works but I need output in a Matrix like format.
Below is a sample structure
create Table DateMatrix
(id int,
TestDate datetime)
insert DateMatrix
values
(1,'2009-05-01 03:20:00.000')
insert DateMatrix
values
(1,'2009-05-01 03:30:00.000')
insert DateMatrix
values
(1,'2009-05-01 03:20:10.000')
insert DateMatrix
values
(2,'2009-05-02 04:20:00.000')
insert DateMatrix
values
(2,'2009-05-02 04:30:00.000')
insert DateMatrix
values
(3,'2009-05-03 15:20:00.000')
insert DateMatrix
values
(3,'2009-05-02 15:20:00.000')
insert DateMatrix
values
(1,'2009-05-01 02:20:00.000')
insert DateMatrix
values
(1,'2009-05-01 02:30:00.000')
insert DateMatrix
values
(1,'2009-05-01 02:20:10.000')
insert DateMatrix
values
(2,'2009-05-02 03:20:00.000')
insert DateMatrix
values
(2,'2009-05-02 03:30:00.000')
insert DateMatrix
values
(3,'2009-05-03 2:20:00.000')
Here is the query:
select Totalcount
,DateHour
,convert(varchar,Datehour,110) as DATEVALUE
,convert(varchar,Datehour,108) as TIMEVALUE
From (select distinct count(ID) as TotalCount
,dateadd(hh, datediff(hh, 0, Testdate), 0) as DateHour
From DateMatrix s with (nolock)
group by dateadd(hh, datediff(hh, 0, Testdate), 0) )t
However I want the results like this:
Date 2.00AM 3.00AM 4.00AM ....3.00PM
5-1-2009 3 3
5-2-2009 1 2 2 1
5-3-2009 1 1
Any ideas?
TIA,
Natasha
June 5, 2009 at 2:41 pm
If anyone is interested I have the solution:
I am populating results of the query in a post in a #temp1 table and then workign off it.
SELECT [TimeValue/DateValue]=CASE WHEN GROUPING(DateValue)=0
THEN CAST(dateValue AS VARCHAR(10))
ELSE 'Sum'
END,
'00:00:00'=SUM(CASE WHEN TimeValue='00:00:00' THEN TotalCount ELSE 0 END),
'02:00:00'=SUM(CASE WHEN TimeValue='02:00:00' THEN TotalCount ELSE 0 END),
'03:00:00'=SUM(CASE WHEN TimeValue='03:00:00' THEN TotalCount ELSE 0 END),
'04:00:00'=SUM(CASE WHEN TimeValue='04:00:00' THEN TotalCount ELSE 0 END),
'05:00:00'=SUM(CASE WHEN TimeValue='05:00:00' THEN TotalCount ELSE 0 END),
'06:00:00'=SUM(CASE WHEN TimeValue='06:00:00' THEN TotalCount ELSE 0 END),
'07:00:00'=SUM(CASE WHEN TimeValue='07:00:00' THEN TotalCount ELSE 0 END),
'08:00:00'=SUM(CASE WHEN TimeValue='08:00:00' THEN TotalCount ELSE 0 END),
'09:00:00'=SUM(CASE WHEN TimeValue='09:00:00' THEN TotalCount ELSE 0 END),
'total'=SUM(TotalCount)
FROM #temp1
GROUP BY DateValue WITH ROLLUP ORDER BY GROUPING(DateValue),DateValue
** Note: To this Code add more time to cover 24 hours of a day.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply