SQLTeam.com | Weblogs | Forums

Iterate dates for monthly average

#1

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

#2

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
#3

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

#4

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

#5

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...
#6

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
#7

@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.

#8

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.