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'))
You have only one day in the range - July 11, 2013. So the iteration would be just a single step.
If you want to get the daily count and average over a whole month:
SELECT
PTA.ADMIT_DATE,
COUNT(*) AS DailyCount,
AVG(COUNT(*)) OVER() AS MonthlyAverage
FROM
YourTablesAndJoinsHere
WHERE
YourWhereClauseHere to select all the dates in the month, or
the date range of interest
GROUP BY
PTA.ADMIT_DATE
I need take the of DISTINCT COUNT(PB.CUSTOMER_CODE) to get the accurate number
would there be a way to run a loop with a parameter or maybe even a pivot
maybe a parameter on the day
'2013-07-'+@daymonth
ex; AND PTA.ADMIT_DATE <= '2013-07-'+@daymonth AND PTS.STATUS_DATE <= '2013-07-'+@daymonth AND (PTS.STATUS_END_DATE >= '2013-07-'+@daymonth
to iterate <=31
SELECT DISTINCT
COUNT(PB.CUSTOMER_CODE) AS ACTIVE
FROM
Tables joined here
WHERE (OD.DATASET_NAME = 'Business Name') AND (OD.DATASET_ID = '3')
AND PTA.ADMIT_DATE <= '2013-07-01'
AND PTS.STATUS_DATE <= '2013-07-01'
AND (PTS.STATUS_END_DATE >= '2013-07-01')
AND (PTS.CUSTOMER_CLASS IN ('HO'))
This code is showing the results but in every row and not averaging out all rows of data
showing multiple queries if I could somehow combine all these results and average them out
DECLARE @dayMonth INT
DECLARE @startDate DATETIME = '2013-06-30'
SET @daymonth = 0
WHILE @dayMonth <= 30
BEGIN
SET @dayMonth = @dayMonth + 1
SELECT DISTINCT
COUNT() AS ACTIVE, AVG(COUNT()) OVER() As MonthlyAverage
FROM
Tables joined here
WHERE (OD.DATASET_NAME = 'Business Name') AND (OD.DATASET_ID = '3')
AND PTA.ADMIT_DATE <= DATEADD(Day, @dayMonth,@startDate)
AND PTS.STATUS_DATE <= DATEADD(Day, @dayMonth,@startDate)
AND PTS.STATUS_END_DATE >= DATEADD(Day, @dayMonth,@startDate)
AND (PTS.CUSTOMER_CLASS IN ('HO'))
I found another way with #temporary tables because that while loop
was throwing me off..
CREATE TABLE #Temp
(
COLUMNS int
)
DECLARE @dayMonth INT
DECLARE @endDate DATETIME = '2013-07-31'
DECLARE @startDate DATETIME = '2013-06-30'
DECLARE @Census INT
SET @daymonth = 0
WHILE @dayMonth <= 30
BEGIN
SET @dayMonth = @dayMonth + 1
INSERT INTO #Temp (COLUMNS) -- inserted data from every iteration into temp
SELECT DISTINCT
SUM(COUNT(*)) over() AS TOTAL
FROM
Tables joined here
WHERE (OD.DATASET_NAME = 'Business Name') AND (OD.DATASET_ID = '3')
AND PTA.ADMIT_DATE <= DATEADD(Day, @dayMonth,@startDate)
AND PTS.STATUS_DATE <= DATEADD(Day, @dayMonth,@startDate)
AND PTS.STATUS_END_DATE >= DATEADD(Day, @dayMonth,@startDate)
AND (PTS.CUSTOMER_CLASS IN ('HO'))
Please post the entire code that you ended up with because, so far, you've selected the second slowest method there is. Only a recursive CTE would be slower or use more resources than the loops you currently have posted.