Patrick Gregorio Patrick Gregorio - 1 year ago 60
SQL Question

SQL Getting Date Range

I need to aggregate costs to be displayed by date range per month. To illustrate please take a look at the following:

Sample Data

invoice_id usage_date cost
123 May 31, 2016 $4
150 June 01, 2016 $4
150 June 02, 2016 $4
150 June 03, 2016 $4
150 June 04, 2016 $5
150 June 05, 2016 $5
150 June 30, 2016 $5
240 July 01, 2016 $8

Taking into account the data above, I want to get

Desired Result

range total
June 01-03, 2016 $12
June 04-30, 2016 $135

As you'll notice I want to group them and add those with the same
but display the dates as start to end. But this has to be done for each month only. In this particular case, let's say
May 31, 2016
incurred a cost of
, it wouldn't be included even though it has the same cost with the next day (June 01) because they're from different months.

I feel like I need to provide more information but I'm not sure what you guys still need so just comment what you want me to add.


I don't know if this matters but let me just add a query I used to generate the sample data I provided above.

Sample Data - SQL

SELECT id.invoice_id, di.usage_date, SUM(di.item_cost) AS cost
FROM detail_items di
LEFT JOIN invoice_details id
ON = di.detail_id
WHERE di.group_id = 123456
GROUP BY id.invoice_id, di.usage_date

vkp vkp
Answer Source

Aggregate by year, month and cost and get the min and max dates for a given cost. Then multiply the cost with the total rows in a given month to get the total.

select to_char(mindt,'mm/dd/yyyy')||'-'||to_char(maxdt,'mm/dd/yyyy') daterange,
cnt*cost total
from (
cost, min(usage_date) mindt, max(usage_date) maxdt, count(*) cnt,
to_char(usage_date,'mm') usg_mth,to_char(usage_date,'yyyy') usg_yr
from tablename
group by to_char(usage_date,'mm'),to_char(usage_date,'yyyy'),cost
) t 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download