0

I have a table as below:

TABLE A:

id|  on_date |off_date
1 |2020-09-10|
2 |2020-09-09|
3 |2020-09-08|
4 |2020-09-09|2020-09-10
5 |2020-09-08|

I want to get the count of ids which are in on mode till a particular date (2020-09-11)

Expected output:

count|date
2    |2020-09-08 (because id 3 and 5 has on_date of 2020-09-08)
4    |2020-09-09 (because id 2,3 and 5 has on_date till 09-09-2020)
4    |2020-09-10 (because id 4 not counted because off_date is present and id 1 is added)

I am not really sure how to get this output.

  • oh you are right my bad. corrected it. – shrey Oct 8 at 8:19
0

It can be, for example,

SELECT SUM(dates.a_date >= test.on_date AND (dates.a_date < test.off_date OR test.off_date IS NULL)) `count`, 
       dates.a_date `date`
FROM ( SELECT on_date a_date FROM test 
       UNION 
       SELECT off_date FROM test WHERE off_date IS NOT NULL ) dates
CROSS JOIN test
GROUP BY 2
ORDER BY 2;

fiddle

dates subquery generats dates list when the amount of ON ids might have changed.

SUM tests each row against the conditions for particular date and counts the amount of matched rows (testing result is 1 while matching and 0 otherwise).

If some date is absent then look the row with closest lower date.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.