Patrick Gregorio Patrick Gregorio - 6 months ago 12
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
cost
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
$4
, 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.

EDIT



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 id.id = di.detail_id
WHERE di.group_id = 123456
GROUP BY id.invoice_id, di.usage_date

vkp vkp
Answer

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 (
select 
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