Golden Gate Golden Gate - 4 months ago 8
SQL Question

Aggregate multiple columns based on specific date range with in a month

I need to aggregate

Amounts
to be displayed by date range per month. To illustrate please take a look at the following table:

Invoice_Payment

Customer_id Invoice_no Invoice_date Amount
---------------------------------------------------
10 10023 2016-07-08 60
10 10018 2016-08-04 90
11 10016 2016-07-01 110
11 10021 2016-07-05 120
12 10028 2016-07-11 10
12 10038 2016-07-31 5


As you'll notice, I want to group them based on
Customer_id
and display the dates as start to end. Furthermore, this has to be done for each month only.

Following query I have tried so far:

select Customer_id, (mindate + ' to ' + maxdate) Date_Range, Amount
from (
select Customer_id, sum(Amount) Amount, min(Invoice_date) mindate, max(Invoice_date) maxdate
from Invoice_Payment
group by Customer_id
) I ;


From above query I'm getting
Output
like:

Customer_id Date_Range Amount
10 2016-07-08 to 2016-08-04 150
11 2016-07-01 to 2016-07-05 230
12 2016-07-11 to 2016-07-31 15


Please check this.. SQL Fiddle Working Demo

Let's say
Customer_id = 10
who has Invoice_date in
July,2016
and
August,2016
. I need to sum up all payments of that particular Customer for the month of July and August separately within specific date range. But I am getting sum of
Amount
of all
Invoice_date
from above endeavor.

Desired output :

Customer_id Date_Range Amount
10 2016-07-08 to 2016-07-08 60
10 2016-08-04 to 2016-08-04 90
11 2016-07-01 to 2016-07-05 230
12 2016-07-11 to 2016-07-31 15


How could I get over this ? Any help would be greatly appreciated.

Answer

You are almost done. Just add YEAR and MONTH to GROUP BY.

select Customer_id, (mindate + ' to ' + maxdate) Date_Range, Amount
from (
       select Customer_id, 
       sum(Amount) Amount, min(Invoice_date) mindate, max(Invoice_date) maxdate  
       from #Invoice_Payment
group by 
  Customer_id,
  YEAR(Invoice_date),
  MONTH(Invoice_date)
) I ;