SQL SUM hours between two dates & GROUP BY Column Name?

Question

I need to display the total amount of hours elapsed for an action within a month and the previous month before it like this:

 ___________________________________________
| Rank |  Action  |   Month    | Prev Month |
|------|----------|------------|------------|
| 1    | Action1  |      580.2 |      200.7 |
| 2    | Action8  |      412.5 |      550.2 |
| 3    | Action10 |      405.0 |       18.1 |
---------------------------------------------

I have a SQL table in the format of:

 _____________________________________________________
| Action  |     StartTime       |       EndTime       |
|---------|---------------------|---------------------|
| Action1 | 2015-02-03 06:01:53 | 2015-02-03 06:12:05 |
| Action1 | 2015-02-03 06:22:16 | 2015-02-03 06:25:33 |
| Action2 | 2015-02-03 06:36:07 | 2015-02-03 06:36:49 |
| Action1 | 2015-02-03 06:36:46 | 2015-02-03 06:48:10 |
| ..etc   | 20..-..-..   ...etc | 20..-..-..   ...etc |
-------------------------------------------------------

What would the query look like?

EDIT:

A ツ's answer got me headed in the right direction however I solved the problem using a JOIN. See below for my solution.

How do you know which month? Will that be coming from the user? — Felix Pamittan, Apr 14 '15 at 07:19
@wewesthemenace The month will be entered from a calendar date widget and only the top 10 results will be shown from the generated table. — ChiMo, Apr 14 '15 at 07:29

score 1 · Answer 1 · answered Apr 14 '15 at 09:11

i changed the values a bit since only one day is rather boring

INSERT INTO yourtable
    ([Action], [StartTime], [EndTime])
VALUES
    ('Action1', '2015-02-18 06:01:53', '2015-02-18 06:12:05'),
    ('Action1', '2015-02-18 06:22:16', '2015-02-18 06:25:33'),
    ('Action2', '2015-04-03 06:36:07', '2015-04-03 06:36:49'),
    ('Action1', '2015-03-19 06:36:46', '2015-03-19 06:48:10'),
    ('Action2', '2015-04-13 06:36:46', '2015-04-13 06:48:10'),
    ('Action2', '2015-04-14 06:36:46', '2015-04-14 06:48:10')
;

now define the date borders:

declare @dateEntry datetime = '2015-04-03';

declare @date1 date
      , @date2 date
      , @date3 date;

set @date1 = @dateEntry;               -- 2015-04-03
set @date2 = dateadd(month,-1,@date1); -- 2015-03-03 
set @date3 = dateadd(month,-1,@date2); -- 2015-02-03

the selected date will include all action which starts before 2015-04-03 00:00 and starts after 2015-02-03 00:00

select date1 = @date1 
     , date2 = @date2
     , date3 = @date3
     , [Action] 
     , thisMonth = 
       sum(
       case when Starttime between @date2 and @date1 
            then datediff(second, starttime, endtime)/360.0
       end)
     , lastMonth =
       sum(
       case when Starttime between @date3 and @date2
            then datediff(second, starttime, endtime)/360.0
       end)
  from yourtable 
  where starttime between @date3 and @date1
  group by [Action]

http://sqlfiddle.com/#!6/35784/5

You were very helpful! I did not realise you can place CASE statements within a SUM field. The only problem is it is collecting all results even if the current month's value is NULL (Gathering data from previous month as well). Setting the WHERE clause to `WHERE (StartTime BETWEEN @date2 AND @date1)` removes data from the `lastMonth` field and adding `WHERE thisMonth IS NOT NULL` does not work as lastMonth is _not a valid Column Name_ — ChiMo, Apr 15 '15 at 00:36
NULL results could be omitted by using `HAVING` after the `GROUP BY` statement. However in order to display the correct results the `SUM` with `CASE` statement needs to be repeated. — ChiMo, May 28 '15 at 06:05

score 1 · Accepted Answer · edited May 23 '17 at 10:27

I'm just revisiting this question after researching a bit more about SQL Server.

A temporary table can be created from a query and then used inside another query - a nested query if you like.
This way the results can JOIN together like through any other normal table without nasty CASE statements. This is also useful to display other datas required of the first query like COUNT(DISTINCT ColumnName)

JOIN two SELECT statement results

SELECT TOP 10
    t1.Action, t1.[Time],
    COALESCE(t2.[Time],0) AS [Previous Period 'Time'],
    COALESCE( ( ((t1.[Time]*1.0) - (t2.[Time]*1.0)) / (t2.[Time]*1.0) ), -1 ) AS [Change]
FROM 
    (
        SELECT 
            Action, 
            SUM(DATEDIFF(SECOND, StartTime, EndTime)) AS [Time],
        FROM Actions
        WHERE StartTime BETWEEN @start AND @end
        GROUP BY Action
    ) t1
LEFT JOIN
    (
        SELECT 
            Action, 
            SUM(DATEDIFF(SECOND, StartTime, EndTime)) AS [Time]
        FROM Actions
        WHERE StartTime BETWEEN @prev AND @start
        GROUP BY Action
    ) t2
ON
    t1.Action = t2.Action
ORDER BY t1.[Time] DESC

Hopefully this information is helpful for someone.

score 0 · Answer 3 · answered Apr 14 '15 at 07:37

0

Probably you should check use grouping on preprocessed data, like this:

select Action, SUM(Hours)
  from (select Action, DATEDIFF('hh',StartTime, EndTime) as Hours
  FROM Actions)
group by Action

answered Apr 14 '15 at 07:37

suvroc

3,058
1
15
29

score 0 · Answer 4 · answered Apr 14 '15 at 07:37

My assumption is that you're start - end time span is so short that you don't have to worry about dates spanning 2 months, so then you'll probably need something like this:

select 
  dense_rank() over (order by Month desc) as Rank,
  action,
  Month,
  PrevMonth
from
(
  select
    action,
    sum(case when StartTime >= @curMonth then hours else 0 end) as Month,
    sum(case when StartTime >= @prevMonth and StartTime < @curMonth then hours else 0 end) as PrevMonth
  from
  (
    select 
      action, 
      StartTime,
      datediff(second, StartTime, EndTime) / 3600.0 as hours
    from
      yourtable
  ) T1
    group by
       action
) T2

This calculates the duration is seconds and then divides it with 3600 to get hours. The rank is based just on current month. This expects that you have 2 variables @curMonth and @prevMonth that have the dates for the limit, and that there is no data for future.

SQL Fiddle for testing: http://sqlfiddle.com/#!6/d64b7d/1

SQL SUM hours between two dates & GROUP BY Column Name?

4 Answers4