[Fixed]-Sql query for date range for individual date values

by

1👍

✅

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;
👤Patrick

Leave a comment