How to group by hour properly. [modified]
-
I've just found a another report query by my favourite genii, which gives call volumns per hour. You see, datetime columns are once again frowned upon, so we store the call time formatted as 'hh:mm:ss', then we 'group' by the call time as follows:
, CASE WHEN Sum(CASE WHEN TimeText>='01:00:00' And TimeText<='01:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='01:00:00' And TimeText<='01:59:59' THEN Cost ELSE 0 END) END AS T1_2
, CASE WHEN Sum(CASE WHEN TimeText>='02:00:00' And TimeText<='02:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='02:00:00' And TimeText<='02:59:59' THEN Cost ELSE 0 END) END AS T2_3
, CASE WHEN Sum(CASE WHEN TimeText>='03:00:00' And TimeText<='03:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='03:00:00' And TimeText<='03:59:59' THEN Cost ELSE 0 END) END AS T3_4
, CASE WHEN Sum(CASE WHEN TimeText>='04:00:00' And TimeText<='04:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='04:00:00' And TimeText<='04:59:59' THEN Cost ELSE 0 EN -
I've just found a another report query by my favourite genii, which gives call volumns per hour. You see, datetime columns are once again frowned upon, so we store the call time formatted as 'hh:mm:ss', then we 'group' by the call time as follows:
, CASE WHEN Sum(CASE WHEN TimeText>='01:00:00' And TimeText<='01:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='01:00:00' And TimeText<='01:59:59' THEN Cost ELSE 0 END) END AS T1_2
, CASE WHEN Sum(CASE WHEN TimeText>='02:00:00' And TimeText<='02:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='02:00:00' And TimeText<='02:59:59' THEN Cost ELSE 0 END) END AS T2_3
, CASE WHEN Sum(CASE WHEN TimeText>='03:00:00' And TimeText<='03:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='03:00:00' And TimeText<='03:59:59' THEN Cost ELSE 0 END) END AS T3_4
, CASE WHEN Sum(CASE WHEN TimeText>='04:00:00' And TimeText<='04:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='04:00:00' And TimeText<='04:59:59' THEN Cost ELSE 0 ENJust as a matter of interest, assuming you are not in control of the database (i.e. you are constrained by the fact that the time is stored as a text string and you cannot change that fact), how would you implement this requirement? I've been scratching my head trying to think of the easiest, most elegant way to achieve it. I would probably try to take a substring of the TimeText to get the hour and then group by that. Does that work?
select time_hour as substring(TimeText, 1, 2), sum(Cost)
from MyTable
group by time_hourI haven't tried that, it's just off the top of my head. Does it achieve the same end result?
-
Just as a matter of interest, assuming you are not in control of the database (i.e. you are constrained by the fact that the time is stored as a text string and you cannot change that fact), how would you implement this requirement? I've been scratching my head trying to think of the easiest, most elegant way to achieve it. I would probably try to take a substring of the TimeText to get the hour and then group by that. Does that work?
select time_hour as substring(TimeText, 1, 2), sum(Cost)
from MyTable
group by time_hourI haven't tried that, it's just off the top of my head. Does it achieve the same end result?
Something very close works like a dream:
select
isnull(sum(case when Call = 1 then Cost else 0 end), 0) as Incoming
, isnull(sum(case when Call = 2 then Cost else 0 end), 0) as Outgoing
, left(TimeText, 2) as Hour
FROM
CallRecords crd
group by
left(TimeText, 2) -
Something very close works like a dream:
select
isnull(sum(case when Call = 1 then Cost else 0 end), 0) as Incoming
, isnull(sum(case when Call = 2 then Cost else 0 end), 0) as Outgoing
, left(TimeText, 2) as Hour
FROM
CallRecords crd
group by
left(TimeText, 2)You don't need to check for nulls, because SUM will automatically exclude them. Don't know why they wouldn't use a TIME column, unless they were using SQLite, but even there you can just do:
SELECT strftime('%H', TimeText) AS Hour, SUM(Cost) FROM MyTable GROUP BY Hour;
-
I've just found a another report query by my favourite genii, which gives call volumns per hour. You see, datetime columns are once again frowned upon, so we store the call time formatted as 'hh:mm:ss', then we 'group' by the call time as follows:
, CASE WHEN Sum(CASE WHEN TimeText>='01:00:00' And TimeText<='01:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='01:00:00' And TimeText<='01:59:59' THEN Cost ELSE 0 END) END AS T1_2
, CASE WHEN Sum(CASE WHEN TimeText>='02:00:00' And TimeText<='02:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='02:00:00' And TimeText<='02:59:59' THEN Cost ELSE 0 END) END AS T2_3
, CASE WHEN Sum(CASE WHEN TimeText>='03:00:00' And TimeText<='03:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='03:00:00' And TimeText<='03:59:59' THEN Cost ELSE 0 END) END AS T3_4
, CASE WHEN Sum(CASE WHEN TimeText>='04:00:00' And TimeText<='04:59:59' THEN Cost ELSE 0 END) IS Null THEN 0 ELSE Sum(CASE WHEN TimeText>='04:00:00' And TimeText<='04:59:59' THEN Cost ELSE 0 EN