March 30, 2011 at 6:33 am
Hi
I have an hourly report which is easy enough to create. Just wanted to know how to turn it into a 2 hourly or 3 hourly report without using case statements.
select List, datepart(hh, DateofCall) as 'CallHour', count(*) as 'DialCount' from AllResults
group by List, datepart(hh, DateofCall)
I want to break down the work day into say 4 segments instead of 12.
Has anyone managed to do this without case statements in the select clause?
I'm using the above statement as part of 3 in a CTE and I feel that using the case statements would make the code abit untidy.
March 30, 2011 at 6:54 am
You could always use pivot.
But I don't find case to be hard to read.
March 30, 2011 at 8:50 am
Thanks.
I just had a look at pivots and I see that for Pivots you would have to know the column names beforehand - the same as for case statements, so no drawback there then - but it would have been good if for Pivots you could specify 2 values for a column and alias that.
So in the code below, instead of [0], [1], [2], [3], [4], we could have
[0,1] as 'First Segment], [2,3] as 'Second Segment, [4,5] as 'Third Segment'
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
The case statements are fine and working. I was just looking for an alternative as "datepart(hh, DateofCall) as 'CallHour' " for each hour is neat but trying to combine the hours using case statements adds on quite a few lines.
Thanks again, I will dig abit deeper into Pivots now.
March 30, 2011 at 8:55 am
In cases like this I usually do the pivoting in a matrix in SSRS (assuming you are doing some sort of report).
Maybe you need to head out that way.
March 30, 2011 at 9:19 am
Just divide the hour by the number of hours you want to group by.
Examples:
select
Group3Hours = datepart(hh,a.DateofCall)/3,
Group4Hours = datepart(hh,a.DateofCall)/4,
a.DateofCall
from
( -- Test Data
select
DateofCall = dateadd(hh,a2.H,a1.DT)
from
(select DT = getdate()) a1
cross join
(
select H = 0union all
select H = 1union all
select H = 2union all
select H = 3union all
select H = 4union all
select H = 5union all
select H = 6union all
select H = 7union all
select H = 8union all
select H = 9union all
select H = 10union all
select H = 11union all
select H = 12union all
select H = 13union all
select H = 14union all
select H = 15union all
select H = 16union all
select H = 17union all
select H = 18union all
select H = 19union all
select H = 20union all
select H = 21union all
select H = 22union all
select H = 23union all
select H = 24
) a2
) a
Results:
Group3Hours Group4Hours DateofCall
----------- ----------- -----------------------
3 2 2011-03-30 11:15:10.283
4 3 2011-03-30 12:15:10.283
4 3 2011-03-30 13:15:10.283
4 3 2011-03-30 14:15:10.283
5 3 2011-03-30 15:15:10.283
5 4 2011-03-30 16:15:10.283
5 4 2011-03-30 17:15:10.283
6 4 2011-03-30 18:15:10.283
6 4 2011-03-30 19:15:10.283
6 5 2011-03-30 20:15:10.283
7 5 2011-03-30 21:15:10.283
7 5 2011-03-30 22:15:10.283
7 5 2011-03-30 23:15:10.283
0 0 2011-03-31 00:15:10.283
0 0 2011-03-31 01:15:10.283
0 0 2011-03-31 02:15:10.283
1 0 2011-03-31 03:15:10.283
1 1 2011-03-31 04:15:10.283
1 1 2011-03-31 05:15:10.283
2 1 2011-03-31 06:15:10.283
2 1 2011-03-31 07:15:10.283
2 2 2011-03-31 08:15:10.283
3 2 2011-03-31 09:15:10.283
3 2 2011-03-31 10:15:10.283
3 2 2011-03-31 11:15:10.283
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply