Hi everyone,
I am not sure how I would accomplish this without a loop of some sort but
The Dates I need to iterate are
PTS.STATUS_DATE <= '2013-07-11'
AND
PTS.STATUS_END_DATE >= '2013-07-11'
If there is some way to get the row Count for each day of the month and average the total for the month
SELECT DISTINCT
PB.CUSTOMER_CODE
FROM PT_STATUS AS PTS LEFT JOIN
PT_ADMISSION AS PTA ON PTS.ADMISSION_ID = PTA.ADMISSION_ID FULL OUTER JOIN
A_CUSTOMER_STATUS AS APS ON PTS.ADMIN_SET_ID = APS.ADMIN_SET_ID AND PTS.STATUS_CODE = APS.STATUS_CODE INNER JOIN
A_CUSTOMER_CLASS AS APC ON PTS.ADMIN_SET_ID = APC.ADMIN_SET_ID AND PTS.CUSTOMER_CLASS = APC.CUSTOMER_CLASS_CODE INNER JOIN
PT_BASIC AS PB ON PTS.CUSTOMER_ID = PB.CUSTOMER_ID AND PTA.CUSTOMER_ID = PB.CUSTOMER_ID INNER JOIN
O_DATASET AS OD ON PB.DATASET_ID = OD.DATASET_ID
WHERE (OD.DATASET_NAME = 'Business Name') AND (OD.DATASET_ID = '3')
AND PTA.ADMIT_DATE <= '2013-07-11'
AND PTS.STATUS_DATE <= '2013-07-11'
AND (PTS.STATUS_END_DATE >= '2013-07-11')
AND (PTS.CUSTOMER_CLASS IN ('HO'))
Thanks everyone,
Matt