pkumar pkumar - 3 months ago 13
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:

effective_date
expire_date
amount_value
state_id
amount_entry_type


Case 1, Input values:

input_date


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:

input_start_date
input_end_date


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
2016-07-07
and
2016-07-08
:

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.

Options:

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

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;
Comments