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

[sunny_talwar]

I suggest using Interval Match for this. You will basically interval match Date In and Date Out with your master calendar. and then using Date as your dimension, you can just do Count(DISTINCT [Patient No.]) to get the result you are looking for.

[Buddika]

Thanks, Sunny.

[sunny_talwar]

Your welcome. I hope you were able to figure it out

[Saravanan_Desingh · Accepted solution]

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])
;

[Buddika]

Thanks, Saravanan.

That works very well.

[Saravanan_Desingh]

If u think your query is answered, please mark the right answer and close the thread.