Facing Problem to find out the occupancy rate of a healthcare department

[mohitd910]

Deal All,

stalwar1‌

gwassenaar

mto‌

loveisfail‌

mayilvahanan‌

In my data 3 things are main

MOVED_IN_DATE,

OUT_DATE,

IH_EMR_VST_ID

Now Moved_IN_Date is admission Date and Out_Date is Discharge date

Now i want to calculate the Occupancy rate for the Hospital by per day ,per month, per year

Example:-

Like for 1 Jan 2014 my  Distinct Count IH_EMR_VST_ID is 40

Next day 2 Jan 2014  - 20 more admissions happens but 3 discharge. Now i want as a result for  2 Jan 2014 = 57

Please Find the attached Qvds and  and data to the query.

Regards,

Mohit Dahiya

[CELAMBARASAN · Accepted solution]

Hi Mohit,

Please use the below script , also check the attached qv file

Admissions:
LOAD IH_EMR_VST_ID, 
     MOVED_IN_DATE, 
     OUT_DATE, 
     IH_PHL_BED_IH_PHL_PHL_ID, 
     IH_PHL_BED_BED_LABEL, 
     LOS, 
     IH_EMR_PAT_ID, 
     IH_EMR_OUT_ID, 
     IH_EMR_INV_ID, 
     IH_EMR_CMV_ID, 
     IH_EMR_VST_ID_MAS, 
     AGE, 
     AGE_UNIT, 
     STAGE, 
     VST_STATUS, 
     NOTE_TYPE, 
     IH_EMR_VST_DATE, 
     VST_DATE_CREATED, 
     VST_DATE_MODIFIED, 
     TYPE_OF_VISIT, 
     IH_POS_POS_ID, 
     IH_HCP_HCP_ID
FROM

(qvd);


LinkTable:
LOAD 
  IH_EMR_VST_ID, 
  Date(MOVED_IN_DATE + IterNo() -1) as CalendarDate
Resident Admissions
While 
     (MOVED_IN_DATE + IterNo() -1) <= OUT_DATE;

[mohitd910]

Bro data is not matching

[Anonymous]

Hi Mohit

I thought it will help you.So i am sharing similar logic. i applied for my requirement.

try this it will help you.

-----------------------------------------------------------------------

Rawtable:

Date	Hired	Terminated
01-01-2011	6	0
01-02-2011	4	2
01-03-2011	6	1

IF  (  row()=1,   Hired-Terminated  , PEEK (EmployeeCount,-1)  +  (Hired-Terminated)  )   = EmployeeCount

1^ST-> row=1 ,            6-0 ,                       #                                     =6

2^nd->        =2,             # ,                    6+(4-2)                                 =8

3^rd->        =3,             # ,                    8+(6-1)                                 =13

Table1:

Row	Date	Hired	Terminated	Employee Count
1	01-01-2011	6	0	6
2	01-02-2011	4	2	8
3	01-03-2011	6	1	13

IF  (  ROWNO()=1 , 0,  EmployeeCount -  PREVIOUS ( EmployeeCount )  )  =   EmployeeVariance

1^st->     row=1,                 0,               #                                              =0

2^nd->           =2                  #,             8 -  6                                        =2

3^rd->          =3,                  #,             13 - 8                                        =5

Table2:

Row	Date	Employee Count	Employee Var
1	01-01-2011	6	0
2	01-02-2011	8	2
3	01-03-2011	13	5