Sql: Sum After Group By

Sample Table CustomerId | VoucherId | CategoryId | StartDate | EndDate ------------------------------------------------------------- 10 | 1 | 1 | 2013-09-0

Solution 1:

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

with cte as (
    selectdistinct
        CustomerId, StartDate, EndDate
    from Table1
)
select CustomerId, sum(datediff(mm, StartDate, EndDate) + 1) as diff
from cte
groupby CustomerId

sql fiddle demo

Solution 2:

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.

createtable test(
  CustomerId int,
  StartDate date,
  EndDate date
  )

insertinto 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')

createtable calendar(
  MY varchar(10),
  StartDate date,
  EndDate date
  )

insertinto 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
  innerjoin calendar c
    on t.StartDate <= c.EndDate
      and t.EndDate >= c.StartDate
groupby
  t.CustomerId

Solution 3:

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

SQLFiddle!

Solution 4:

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
    GROUPBY 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.