pkumar pkumar - 1 year ago 56
SQL Question

Sql query for date range for individual date values

My question is specific to my problem at hand, so I would try to explain the scenario first. I need to write a sql query. Following is the scenario:

Table columns for table1:


Case 1, Input values:


I've achieved it using following sql query:

Sample Query:

select state_id, sum(amount)
from table1
where state_id=3 and (input_date between effective_date and expiry_date)
group by state_id;

My Question:

Now I've a date range and I wish to achieve the above for all the dates between date range.

Input values 2:


Expected Output:

Find the sum of
amount_value grouped by states where input_date between effective and expire_date for input_date between input_start_date and input_end_date

So the query would give following sample result for date range

state amount_sum date
California 100 2016-07-07
Florida 200 2016-07-08

I'm using postgres as database and django for querying and processing the result.


1. Fetch all the data and process using python.
2. Loop over given date range and fire the query above as:

for input_date in date_range(input_start_date, input_end_date):
//Execute above query

Both above solutions might have performance issues so I was wondering if I could achieve it using single sql query.

Answer Source

You can indeed do this with a single query, using the generate_series() set-returning-function to make the list of days. If you are sure that all dates have corresponding rows for the state then you can you use a regular JOIN, otherwise use a LEFT JOIN as below.

SELECT state_id, sum(amount), dt AS "date"
FROM generate_series(input_start_date, input_end_date, '1 day') dates(dt)
LEFT JOIN table1 ON state_id = 3 AND (dt BETWEEN effective_date AND expiry_date) 
GROUP BY state_id, dt;