How can I change the value for "WHERE startdate >= date" clause and get data for a range of dates?

Question

I am querying a property_table that looks like below, showing between which date to which date the property were active.

fid     startdate   enddate
---     ----------  --------
2588727 2019-01-11  2019-05-09
2591038 2019-01-11  2019-01-18
2587420 2019-03-11  2019-04-09
2592269 2019-03-29  2019-03-09

fid is the flat_id for a house/flat.

I am trying to find out how many flats were active on each day between 2019-03-15 and 2019-04-30 in the below format.

Date        active_count
----------   -----------  
2019-03-15  235631
2019-03-16  234545
2019-03-17  234334
2019-03-29  342123
..
..
2019-04-30  344322

I am able to find active flats on a single day by using the below query. Please help to find for the entire date range as above.

-- Number of flats on '2019-03-20' Date
SELECT COUNT(*)
FROM invent_table 
WHERE startdate <= '2019-03-20' 
AND enddate >= '2019-03-20'

update your question and add a clear data sample your expected result and your actual result — ScaisEdge, May 11 '19 at 08:58

forpas · Accepted Answer · 2019-05-11T09:52:00.287

2

With generate_series() you create the series of the days you are interested in, join to the table and group:

select d.Date, count(i.fid) active_count
from (
 select generate_series(date '2019-03-15', date '2019-04-30', '1 day') as Date
) d left join invent_table i
on d.Date between i.startdate and i.enddate
group by d.Date
order by d.Date

See the demo.

edited May 11 '19 at 09:52

answered May 11 '19 at 09:16

forpas

160,666
10
38
76

This is what I was looking for. Any idea how it can be done in the MySQL server. Just asking. – Bikash Behera May 11 '19 at 11:01
Check this: https://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql – forpas May 11 '19 at 11:03
@BikashBehera . . . This question is about Postgres. If you have another question about MySQL, then you should ask a *new* question. Similarly, if you have a question about performance, you can ask a *new* question. This is a fine answer but performance degrades even for moderately long date ranges. – Gordon Linoff May 11 '19 at 11:33

score 0 · Answer 2 · answered May 11 '19 at 09:02

0

Are your looking for something like this?

SELECT COUNT(*)
FROM invent_table 
WHERE startdate >= '2019-03-15' 
AND enddate <= '2019-04-30'

answered May 11 '19 at 09:02

mkRabbani

16,295
2
15
24

How can I change the value for "WHERE startdate >= date" clause and get data for a range of dates?

2 Answers2