Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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])
;