SQL: SUM after GROUP BY

Question

Sample Table

CustomerId | VoucherId | CategoryId | StartDate | EndDate
-------------------------------------------------------------
        10 |         1 |          1 | 2013-09-01| 2013-09-30
        10 |         1 |          2 | 2013-09-01| 2013-09-30
        11 |         2 |          1 | 2013-09-01| 2013-11-30
        11 |         2 |          2 | 2013-09-01| 2013-11-30
        11 |         2 |          3 | 2013-09-01| 2013-11-30
        10 |         3 |          1 | 2013-10-01| 2013-12-31
        10 |         3 |          2 | 2013-10-01| 2013-12-31
        11 |         4 |          1 | 2013-12-01| 2014-04-30

In above sample record, I want to find out total No. of months customer's vouchers cover

I need the output in the form

CustomerId | Months
--------------------
        10 | 4
        11 | 8

The catch is that a voucher can have multiple rows for different CategoryIds...

I calculated months covered by a voucher as DATEDIFF(MM, StartDate, EndDate) + 1...

When i apply SUM(DATEDIFF(MM, StartDate, EndDate)) GROUP BY VoucherId, StartDate, EndDate I give wrong result because of multiple rows for a VoucherId....

I get something like this...

CustomerId | Months
--------------------
        10 | 8
        11 | 14

CategoryId is useless in this scenario

Thanks

score 4 · Answer 1 · answered Oct 11 '13 at 17:00

actually, in your case (when periods for each category are equal) you can use this query:

with cte as (
    select distinct
        CustomerId, StartDate, EndDate
    from Table1
)
select CustomerId, sum(datediff(mm, StartDate, EndDate) + 1) as diff
from cte
group by CustomerId

sql fiddle demo

score 0 · Answer 2 · answered Oct 11 '13 at 17:00

0

You're grouping by un-needed columns in your query.

SQLFiddle!

answered Oct 11 '13 at 17:00

Andrew

8,445
3
28
46

The answer is not correct for the query... CustomerId | Months -------------------- 10 | 4 11 | 10 It's because you did not add 1 to the DATEDIFF function – Muhammad Ibraheem Oct 11 '13 at 17:16

score 0 · Answer 3 · answered Oct 11 '13 at 17:05

Try this:

SELECT
   T.CustomerId,
   T.VoucherId,
   SUM(DATEDIFF(MM, T1.FirstStartDate, T1.LastEndDate)) AS Months
FROM #YourTable T
JOIN
(
    SELECT
        CustomerId,
        VoucherId,
        MIN(StartDate) AS FirstStartDate,
        MAX(EndDate) AS LastEndDate
    FROM #YourTable T1
    GROUP BY CustomerId, VoucherId
) ON T.CustomerId = T1.CustomerId AND T.VoucherId = T1.VoucherId

This is assuming you don't have gaps between the first start date and last end date. If you do, you may need to edit the inner select. But the idea is to have the inner select determine your date ranges (and ignore the categories), and then have the outer one sum up your months.

score 0 · Accepted Answer · answered Oct 11 '13 at 17:08

sql fiddle demo

This SQL Fiddle addresses your concerns. You need to generate a Calendar table so that you have something to join your dates to. Then you can do a count of distinct MonthYears for each Customer.

create table test(
  CustomerId int,
  StartDate date,
  EndDate date
  )

insert into test
values 
  (10, '9/1/2013', '9/30/2013'),
  (10, '9/1/2013', '9/30/2013'),
  (11, '9/1/2013', '11/30/2013'),
  (11, '9/1/2013', '11/30/2013'),
  (11, '9/1/2013', '11/30/2013'),
  (10, '10/1/2013', '12/31/2013'),
  (10, '10/1/2013', '12/31/2013'),
  (11, '12/1/2013', '4/30/2014')

create table calendar(
  MY varchar(10),
  StartDate date,
  EndDate date
  )

insert into calendar
values 
  ('9/2013', '9/1/2013', '9/30/2013'),
  ('10/2013', '10/1/2013', '10/31/2013'),
  ('11/2013', '11/1/2013', '11/30/2013'),
  ('12/2013', '12/1/2013', '12/31/2013'),
  ('1/2014', '1/1/2014', '1/31/2014'),
  ('2/2014', '2/1/2014', '2/28/2014'),
  ('3/2014', '3/1/2014', '3/31/2014'),
  ('4/2014', '4/1/2014', '4/30/2014')

select
  t.CustomerId, 
  count(distinct c.MY)
from
  test t
  inner join calendar c
    on t.StartDate <= c.EndDate
      and t.EndDate >= c.StartDate
group by
  t.CustomerId

If you have the possibility of having overlapping date ranges then it might be necessary. Say customerid 1 has date ranges of 9/1-10/4, 12/1-12/30, 10/1-11/30. Roman's solution would give you a sum of 5 (2 + 1 + 2) when it should be 4 (september through december = 4). — Derek, Oct 11 '13 at 17:37

SQL: SUM after GROUP BY

4 Answers4

Linked