CODEWITHSUNDEEP
sqlgroup-bysum
Muhammad Ibraheem
Muhammad Ibraheem

Reputation: 285

SQL: SUM after GROUP BY

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

Upvotes: 3

Views: 6449

Answers (4)

Derek
Derek

Reputation: 23268

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

Upvotes: 0

mayabelle
mayabelle

Reputation: 10014

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.

Upvotes: 0

roman
roman

Reputation: 117465

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

Upvotes: 4

Andrew
Andrew

Reputation: 8758

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

SQLFiddle!

Upvotes: 0

Related Questions