How use BETWEEN operator in CASE without start and end date-CodePudding

have start and end dates:

   oa.entity_created            oa.lagg

  id 1|  start |2022-07-20     end |2022-08-15
  id 2|  start |2022-06-15     end |2022-08-09
  id 3|  start |2022-08-12     end |blank

How to count between this dates without start date and end date, like this

   oa.entity_created            oa.lagg

   id 1| start |2022-07-21     end |2022-08-14
   id 2| start |2022-06-16     end |2022-08-08
   id 3| start |2022-08-13     end |blank

If blank count until current_date

count(case when (oa.entity_created < cred.entity_created and oa.lagg > cred.entity_created) then 1 end)

doesn't work correctly

CodePudding user response：

To correct interpret the blank value in the predicate (I assume this is a null value) use COALESCE with the default value, which is the CURRENT_DATE in your case.

  coalesce(oa.lagg,CURRENT_DATE)

So for example to get all rows from the table that match the date of 2022-08-14 use the following predicate (see the WHERE clause, rest is sample data).

select * from (
values
(date'2022-07-20',date'2022-08-15'),
(date'2022-06-15',date'2022-08-09'),
(date'2022-08-12',null)
) oa(entity_created,lagg)
where oa.entity_created < DATE'2022-08-14' and coalesce(oa.lagg,CURRENT_DATE) >  DATE'2022-08-14'

entity_created|lagg      |
-------------- ---------- 
    2022-07-20|2022-08-15|
    2022-08-12|          |

Adapt this predicate in your CASE statement accordingly.