CODEWITHSUNDEEP
sqldjangopostgresql
pkumar
pkumar

Reputation: 4801

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.

Upvotes: 0

Views: 255

Answers (1)

Patrick
Patrick

Reputation: 32326

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;

Upvotes: 1

Related Questions