A collection of multiple columns based on a specific date range per month

Question

I need to aggregate Amountsto display by date range per month. To illustrate this, see 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 noticed, I want to group them based on Customer_idand display the dates as start and end. In addition, this should be done only for each month.

The 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 the above query, I get Outputas:

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 it. SQL Fiddle

Customer_id = 10, Invoice_date July,2016 August,2016. . Amount Invoice_date .

:

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

? .

Answer 1

. YEAR MONTH 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 ;

Answer 2

customer_id,

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,month(Invoice_date), year(Invoice_date)
) I
order by customer_id;