Iterate dates for monthly average

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

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
1 Like

Thanks for the reply James

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'))

Sorry if I wasn't descriptive enough

UPDATE

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'))

END

output
ACTIVE|MonthlyAverage
184 184
182 182
176 176

You could use a CTE with a ROW_NUMBER() function for distinct rows and add an AVG of MonthyAverage from the CTE for aggregated average.

For example:

WITH CTE
AS
(
your query
)
SELECT AVG(Active) ActiveAverage, AVG(MonthlyAverage) MonthAverage 
FROM CTE
WHERE some condition meets...
1 Like

Yes this looks good! I'll give this a try!

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'))

END

SELECT * FROM #Temp

@mgmar,

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.

Seriously. This isn't an attack on you. If you post the whole code so that I can see what's going on, I can show you a better way than a While Loop.