Number of Patients on a Day

[Buddika]

I want to count the number of patients on a day.

Patients can be discharged on any given day (see sample table).

I have used following set function to calculate number of patients on a day, Rangesum(Above(Aggr(Count(DC),[date in]),0,RowNo())) –

Rangesum(Above(Aggr(Count(DC),[date out]),1,RowNo()))

but failed to get the correct numbers. 

Patient No.	Date in	Date out
1	2021-08-18 00:00:00	2021-08-18 00:00:00
2	2021-08-18 00:00:00	2021-08-22 00:00:00
3	2021-08-18 00:00:00	2021-08-25 00:00:00
4	2021-08-18 00:00:00	2021-08-18 00:00:00
5	2021-08-18 00:00:00	2021-08-29 00:00:00
6	2021-08-18 00:00:00	2021-08-18 00:00:00
7	2021-08-18 00:00:00	2021-08-29 00:00:00
8	2021-08-18 00:00:00	2021-08-18 00:00:00
9	2021-08-18 00:00:00	2021-08-18 00:00:00
10	2021-08-18 00:00:00	2021-08-19 00:00:00
11	2021-08-18 00:00:00	2021-08-19 00:00:00
12	2021-08-18 00:00:00	2021-08-22 00:00:00
13	2021-08-18 00:00:00	2021-08-18 00:00:00
14	2021-08-18 00:00:00	2021-08-18 00:00:00
15	2021-08-19 00:00:00	2021-08-19 00:00:00
16	2021-08-19 00:00:00	2021-08-22 00:00:00
17	2021-08-19 00:00:00	2021-08-19 00:00:00
18	2021-08-19 00:00:00	2021-08-19 00:00:00
19	2021-08-20 00:00:00	2021-08-30 00:00:00
20	2021-08-20 00:00:00	2021-08-22 00:00:00

[Saravanan_Desingh]

Try like this,

SET TimestampFormat='M/D/YYYY h:mm:ss';

tab1:
LOAD * INLINE [
    Patient No., Date in, Date out
    1, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    2, 8/18/2021 00:00:00, 8/22/2021 00:00:00
    3, 8/18/2021 00:00:00, 8/25/2021 00:00:00
    4, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    5, 8/18/2021 00:00:00, 8/29/2021 00:00:00
    6, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    7, 8/18/2021 00:00:00, 8/29/2021 00:00:00
    8, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    9, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    10, 8/18/2021 00:00:00, 8/19/2021 00:00:00
    11, 8/18/2021 00:00:00, 8/19/2021 00:00:00
    12, 8/18/2021 00:00:00, 8/22/2021 00:00:00
    13, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    14, 8/18/2021 00:00:00, 8/18/2021 00:00:00
    15, 8/19/2021 00:00:00, 8/19/2021 00:00:00
    16, 8/19/2021 00:00:00, 8/22/2021 00:00:00
    17, 8/19/2021 00:00:00, 8/19/2021 00:00:00
    18, 8/19/2021 00:00:00, 8/19/2021 00:00:00
    19, 8/20/2021 00:00:00, 8/30/2021 00:00:00
    20, 8/20/2021 00:00:00, 8/22/2021 00:00:00
];

Left Join(tab1)
LOAD *,   Date(Floor([Date in])+IterNo()-1) As Dt
Resident tab1
While Date(Date(Floor([Date in]))+IterNo()-1) <= Date([Date out])
;