SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Rolling Average
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Briceston
Yak Posting Veteran

54 Posts

Posted - 09/11/2013 :  23:44:52  Show Profile  Reply with Quote
Hi,

I need some help calculating a rolling 3 month average cost from the two dataset below. Which is the 3 month Average of Dataset1 / Dataset 2. Thanks.

Dataset 1:

SELECT(factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode) AS [Contract Code],
factAdmissions.AdmitCCYYMM,
ISNULL(sum(AmountPaid),0)As [Amount Paid]
FROM factAdmissions

WHERE factAdmissions.AdmitCCYYMM >='200701'
AND factAdmissions.AcuteSNFIndAdmit ='a'
AND factAdmissions.ContractCode Is Not Null
AND factAdmissions.BenefitPlanCode Is Not Null
AND BusinessUnitCode In('EP','HF', 'VN')
AND AdmitCCYYMM BETWEEN (@StartDate) AND (@EndDate)

AND factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode IN (@Product)

Group by factAdmissions.ContractCode,
factAdmissions.BenefitPlanCode,
factAdmissions.AdmitCCYYMM
Order by factAdmissions.ContractCode,
factAdmissions.BenefitPlanCode,
factAdmissions.AdmitCCYYMM;



Dataset2:

Select

(factMembership.ContractCode+'-'+ factMembership.BenefitPlanCode) As Product,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership
Where EffectiveCCYYMM >= '200701'
AND BusinessUnitCode In('EP','HF', 'VN')
AND EffectiveCCYYMM BETWEEN (@StartDate) AND (@EndDate)

AND factMembership.ContractCode + '-' +factMembership.BenefitPlanCode IN (@Product)


Group by
ContractCode,
BenefitPlanCode,
EffectiveCCYYMM

Order by 1;

divya.ce
Starting Member

India
16 Posts

Posted - 09/12/2013 :  07:24:06  Show Profile  Reply with Quote
It will be good if you give the sample output of the datasets as well as the output you require.
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 09/12/2013 :  09:22:16  Show Profile  Reply with Quote
quote:
Originally posted by divya.ce

It will be good if you give the sample output of the datasets as well as the output you require.






Dataset 1: Output 201101 201102 201103
Cost $420,627 $73,608 $93,792


Dataset 2:Output 201103
Memberscount 400



Desired Output: 201103
3 Month Rolling Average $490.02
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/12/2013 :  16:08:47  Show Profile  Reply with Quote
why are you bringing it in different datasets? why cant they all be calculated in same dataset?

Also I didnt understand how you got $490.02 as monthly avg for prev 3 months. can you explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 09/12/2013 :  16:58:59  Show Profile  Reply with Quote
Well, I need a rolling 3 month average cost from dataset 1, which is my cost dataset. Dataset 2 is the count of members.

I got $490.02 by taking the 3 month average cost and dividing it by count of members. This is a standard healthcare insurance metric.

This may not be the right approach with both dataset, so please advise.

quote:
Originally posted by visakh16

why are you bringing it in different datasets? why cant they all be calculated in same dataset?

Also I didnt understand how you got $490.02 as monthly avg for prev 3 months. can you explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 09/12/2013 :  17:32:04  Show Profile  Reply with Quote
Since I don't have access to your data and tables or any knowledge of the schema, I am unable to offer specifics. But in general, the query below shows how you would do it.

In cte1, I am calculating two columns: 1, the cost for a given month, and 2, the beginning of that month (so I can associate the cost with a month)
In cte2, I am calculating two columns: 1, the average number of members, and 2, the beginning of that month (so I can associate the member count with a month).

Now that I have those two tables, I joining the two tables, but also joining to cte1 two more times to pick up the values from the prior two months.

This is only a template. You will need to refine it - for example, if there is no data for any of the 3 months how that should be handled etc.
;WITH cte1 AS 
(
	SELECT
		SUM(cost) AS cost,
		beginningOfMonthDate
	FROM
		SomeTable
	GROUP BY
		beginningOfMonthDate
),
cte2 AS
(
	SELECT
		AVG(numberofMembers) AS numberofMembers,
		beginningofMonthDate
	FROM
		AnotherTable
	GROUP BY
		beginningOfMonthDate
)
SELECT
	(COALESCE(a1.Cost,0) + COALESCE(a2.cost,0) + COALESCE(a3.cost,0))/(3.0*b.numberofMembers)
FROM
	cte1 a1
	INNER JOIN cte2 b ON a1.beginningOfMonthDate = b.beginningOfMonthDate	
	LEFT JOIN cte1 a2 ON a1.beginningOfMonthDate = DATEADD(mm,1,a2.beginningOfMonthDate)
	LEFT JOIN cte1 a3 ON a1.beginningOfMOnthDate = DATEADD(mm,2,a3.beginningOfMonthDate)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/12/2013 :  17:41:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are you using SQL Server 2012?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 09/12/2013 :  23:59:06  Show Profile  Reply with Quote
Sql Server 2008.

quote:
Originally posted by SwePeso

Are you using SQL Server 2012?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Go to Top of Page

divya.ce
Starting Member

India
16 Posts

Posted - 09/13/2013 :  07:18:46  Show Profile  Reply with Quote
One question, can you expand your dataset to give how the rolling average should behave? in your current output you have given fixed output for 3 months, if in case it has more months, how do you need the output to be? Datasets eg are given below,

Dataset 1: Output
dt cost
201101 $420,627
201102 $73,608
201103 $93,792
201104 $234
201105 $23424
201106 $5454
201107 $5454



Dataset 2:Output
dt Memberscount
201101 400
201102 100
201103 40
201104 30
201105 560
201106 100

Desired output??
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 09/14/2013 :  00:40:29  Show Profile  Reply with Quote
The examples below would replicate an excel formula where the formula moves one cell over to calculate the 3 month Average.

Dataset1
AmoutPaid:
201102 $73,608
201103 $93,792
201104 $234

Dataset2
Memberscount:
201104 30

3 month Average cost = $1,863.
---------------------------------------------

Dataset1
AmoutPaid:
201103 $93,792
201104 $234
201105 $23424

Dataset2
Memberscount:
201105 560
3 month Average cost = $69.91
----------------------------------------------

Dataset1
AmoutPaid:
201104 $234
201105 $23,424
201106 $5,454

Dataset2
Memberscount:
201106 100

3 month Average cost $97.04
-------------------------------------------




quote:
Originally posted by divya.ce

One question, can you expand your dataset to give how the rolling average should behave? in your current output you have given fixed output for 3 months, if in case it has more months, how do you need the output to be? Datasets eg are given below,

Dataset 1: Output
dt cost
201101 $420,627
201102 $73,608
201103 $93,792
201104 $234
201105 $23424
201106 $5454
201107 $5454



Dataset 2:Output
dt Memberscount
201101 400
201102 100
201103 40
201104 30
201105 560
201106 100

Desired output??


Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 09/14/2013 :  00:56:37  Show Profile  Reply with Quote
Thanks for your input. However,I tried your suggested code and got
this error: "Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Warning: Null value is eliminated by an aggregate or other SET operation."

quote:
Originally posted by James K

Since I don't have access to your data and tables or any knowledge of the schema, I am unable to offer specifics. But in general, the query below shows how you would do it.

In cte1, I am calculating two columns: 1, the cost for a given month, and 2, the beginning of that month (so I can associate the cost with a month)
In cte2, I am calculating two columns: 1, the average number of members, and 2, the beginning of that month (so I can associate the member count with a month).

Now that I have those two tables, I joining the two tables, but also joining to cte1 two more times to pick up the values from the prior two months.

This is only a template. You will need to refine it - for example, if there is no data for any of the 3 months how that should be handled etc.
;WITH cte1 AS 
(
	SELECT
		SUM(cost) AS cost,
		beginningOfMonthDate
	FROM
		SomeTable
	GROUP BY
		beginningOfMonthDate
),
cte2 AS
(
	SELECT
		AVG(numberofMembers) AS numberofMembers,
		beginningofMonthDate
	FROM
		AnotherTable
	GROUP BY
		beginningOfMonthDate
)
SELECT
	(COALESCE(a1.Cost,0) + COALESCE(a2.cost,0) + COALESCE(a3.cost,0))/(3.0*b.numberofMembers)
FROM
	cte1 a1
	INNER JOIN cte2 b ON a1.beginningOfMonthDate = b.beginningOfMonthDate	
	LEFT JOIN cte1 a2 ON a1.beginningOfMonthDate = DATEADD(mm,1,a2.beginningOfMonthDate)
	LEFT JOIN cte1 a3 ON a1.beginningOfMOnthDate = DATEADD(mm,2,a3.beginningOfMonthDate)


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/15/2013 :  03:45:24  Show Profile  Reply with Quote
quote:
Originally posted by Briceston

Thanks for your input. However,I tried your suggested code and got
this error: "Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Warning: Null value is eliminated by an aggregate or other SET operation."

quote:
Originally posted by James K

Since I don't have access to your data and tables or any knowledge of the schema, I am unable to offer specifics. But in general, the query below shows how you would do it.

In cte1, I am calculating two columns: 1, the cost for a given month, and 2, the beginning of that month (so I can associate the cost with a month)
In cte2, I am calculating two columns: 1, the average number of members, and 2, the beginning of that month (so I can associate the member count with a month).

Now that I have those two tables, I joining the two tables, but also joining to cte1 two more times to pick up the values from the prior two months.

This is only a template. You will need to refine it - for example, if there is no data for any of the 3 months how that should be handled etc.
;WITH cte1 AS 
(
	SELECT
		SUM(cost) AS cost,
		beginningOfMonthDate
	FROM
		SomeTable
	GROUP BY
		beginningOfMonthDate
),
cte2 AS
(
	SELECT
		AVG(numberofMembers) AS numberofMembers,
		beginningofMonthDate
	FROM
		AnotherTable
	GROUP BY
		beginningOfMonthDate
)
SELECT
	(COALESCE(a1.Cost,0) + COALESCE(a2.cost,0) + COALESCE(a3.cost,0))/(3.0*b.numberofMembers)
FROM
	cte1 a1
	INNER JOIN cte2 b ON a1.beginningOfMonthDate = b.beginningOfMonthDate	
	LEFT JOIN cte1 a2 ON a1.beginningOfMonthDate = DATEADD(mm,1,a2.beginningOfMonthDate)
	LEFT JOIN cte1 a3 ON a1.beginningOfMOnthDate = DATEADD(mm,2,a3.beginningOfMonthDate)





Thats because one or more of your beginningOfMonthDate fields is not of datetime type and has values in all the different formats

see

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/15/2013 :  14:01:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Costs TABLE
	(
		Period INT NOT NULL,
		Cost INT NOT NULL
	);

INSERT	@Costs
	(
		Period,
		Cost
	)
VALUES	(201101, 420627),
	(201102,  73608),
	(201103,  93792),
	(201104,    234),
	(201105,  23424),
	(201106,   5454),
	(201107,   5454);

DECLARE	@Members TABLE
	(
		Period INT NOT NULL,
		Number INT NOT NULL
	);

INSERT	@Members
	(
		Period,
		Number
	)
VALUES	(201101, 400),
	(201102, 100),
	(201103,  40),
	(201104,  30),
	(201105, 560),
	(201106, 100);

-- SwePeso (pre SQL Server 2012)
SELECT		c.Period,
		f.Average / m.Number AS MovingAverage
FROM		@Costs AS c
CROSS APPLY	(
			SELECT	AVG(Cost) AS Average
			FROM	(
					SELECT TOP(3)	x.Cost
					FROM		@Costs AS x
					WHERE		x.Period <= c.Period
					ORDER BY	x.Period DESC
				) AS d
		) AS f
INNER JOIN	@Members AS m ON m.Period = c.Period
ORDER BY	c.Period;

-- SwePeso (SQL Server 2012 and 2014)
SELECT		c.Period,
		AVG(c.Cost) OVER (ORDER BY c.Period ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) / m.Number AS MovingAverage
FROM		@Costs AS c
INNER JOIN	@Members AS m ON m.Period = c.Period
ORDER BY	c.Period;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 09/16/2013 :  13:08:36  Show Profile  Reply with Quote
SwePeso, thanks for the below example. However, this does not calculate a rolling 3 month average. I'm looking to take the average of every three months and divide it by the count of members by the month.

Your result set below calculates one months cost and divides it by the count of member for that period.

Result from SwePeso:

Period MovingAverage
201101 1051
201102 2471
201103 4900
201104 1862
201105 69
201106 97





quote:
Originally posted by SwePeso

DECLARE	@Costs TABLE
	(
		Period INT NOT NULL,
		Cost INT NOT NULL
	);

INSERT	@Costs
	(
		Period,
		Cost
	)
VALUES	(201101, 420627),
	(201102,  73608),
	(201103,  93792),
	(201104,    234),
	(201105,  23424),
	(201106,   5454),
	(201107,   5454);

DECLARE	@Members TABLE
	(
		Period INT NOT NULL,
		Number INT NOT NULL
	);

INSERT	@Members
	(
		Period,
		Number
	)
VALUES	(201101, 400),
	(201102, 100),
	(201103,  40),
	(201104,  30),
	(201105, 560),
	(201106, 100);

-- SwePeso (pre SQL Server 2012)
SELECT		c.Period,
		f.Average / m.Number AS MovingAverage
FROM		@Costs AS c
CROSS APPLY	(
			SELECT	AVG(Cost) AS Average
			FROM	(
					SELECT TOP(3)	x.Cost
					FROM		@Costs AS x
					WHERE		x.Period <= c.Period
					ORDER BY	x.Period DESC
				) AS d
		) AS f
INNER JOIN	@Members AS m ON m.Period = c.Period
ORDER BY	c.Period;

-- SwePeso (SQL Server 2012 and 2014)
SELECT		c.Period,
		AVG(c.Cost) OVER (ORDER BY c.Period ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) / m.Number AS MovingAverage
FROM		@Costs AS c
INNER JOIN	@Members AS m ON m.Period = c.Period
ORDER BY	c.Period;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/16/2013 :  13:54:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Where do you think the number of 1862 comes from?

I believe the number of 1862 is the average from the three months 201302 to 201304 (73608+93792+234) which averages to 55878.
Then I believe the average number of 55787 is divided by the number of members for 201304 (30) to result in 1862.

What do you believe?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 09/16/2013 :  15:31:07  Show Profile  Reply with Quote
SwePeso, My bad, I apologize your code is correct. I mistakenly caluclated against the wrong cells in excel when I was verifying your result set. My apologize once again.

The data used was a sample, how would I use the results from dataset1 and dataset2 to get the final result? Without having to do a insert value.

quote:
Originally posted by SwePeso

Where do you think the number of 1862 comes from?

I believe the number of 1862 is the average from the three months 201302 to 201304 (73608+93792+234) which averages to 55878.
Then I believe the average number of 55787 is divided by the number of members for 201304 (30) to result in 1862.

What do you believe?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA


Edited by - Briceston on 09/16/2013 15:34:14
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/17/2013 :  00:29:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- SwePeso (pre SQL Server 2012)
SELECT		c.Period,
		f.Average / m.Number AS MovingAverage
FROM		{Your Cost Table Schema Here}.{Your Cost Table Name Here} AS c
CROSS APPLY	(
			SELECT	AVG(1E * Cost) AS Average
			FROM	(
					SELECT TOP(3)	x.Cost
					FROM		{Your Cost Table Schema Here}.{Your Cost Table Name Here} AS x
					WHERE		x.Period <= c.Period
					ORDER BY	x.Period DESC
				) AS d
		) AS f
INNER JOIN	{Your Member Table Schema Here}.{Your Member Table Name Here} AS m ON m.Period = c.Period
ORDER BY	c.Period;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Briceston
Yak Posting Veteran

54 Posts

Posted - 09/18/2013 :  01:02:48  Show Profile  Reply with Quote
I used my table and the actual fields within the below example, but my result set was wrong. I think reason it's off is the AmountPaid field should be sum first then Average, and the Memberid is a count of the Member id. When I tried these updates I got syntax errors.

SELECT c.AdmitCCYYMM,
f.Average / m.MemberId AS MovingAverage
FROM factAdmissions AS c
CROSS APPLY (
SELECT AVG(1E * AmountPaid) AS Average
FROM (
SELECT TOP(3) x.AmountPaid
FROM factAdmissions AS x
WHERE x.AdmitCCYYMM <= c.AdmitCCYYMM
ORDER BY x.AdmitCCYYMM DESC
) AS d
) AS f
INNER JOIN factMembership AS m ON m.EffectiveCCYYMM = c.AdmitCCYYMM
ORDER BY c.AdmitCCYYMM;

Result set from query: About 278,000 Rows of the samething.

AdmitCCYYMM MovingAverage
200701 0.0122584090551209
200701 0.0122584090551209
200701 0.0122584090551209
200701 0.0122584090551209
200701 0.0122584090551209
200701 0.0122584090551209
200701 0.0122584090551209

quote:
Originally posted by SwePeso

-- SwePeso (pre SQL Server 2012)
SELECT		c.Period,
		f.Average / m.Number AS MovingAverage
FROM		{Your Cost Table Schema Here}.{Your Cost Table Name Here} AS c
CROSS APPLY	(
			SELECT	AVG(1E * Cost) AS Average
			FROM	(
					SELECT TOP(3)	x.Cost
					FROM		{Your Cost Table Schema Here}.{Your Cost Table Name Here} AS x
					WHERE		x.Period <= c.Period
					ORDER BY	x.Period DESC
				) AS d
		) AS f
INNER JOIN	{Your Member Table Schema Here}.{Your Member Table Name Here} AS m ON m.Period = c.Period
ORDER BY	c.Period;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Go to Top of Page

divya.ce
Starting Member

India
16 Posts

Posted - 09/18/2013 :  03:53:00  Show Profile  Reply with Quote
Hey Briceston,
If you want the result to behave like the rolling average in excel, please read my article on the same. i have faced a similar situation earlier. Link to the article: http://www.sqlservercentral.com/articles/Iterative/64699/
Let me if it helped you.
Go to Top of Page

zhaodandan
Starting Member

3 Posts

Posted - 09/18/2013 :  04:10:06  Show Profile  Reply with Quote
unspammed
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 09/18/2013 :  04:20:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Look at your query. You did something very wrong when adapting it to your environment.
SELECT		c.AdmitCCYYMM,
		f.Average / m.MemberID AS MovingAverage	--< What is wrong here?
FROM		dbo.factAdmissions AS c
CROSS APPLY	(
			SELECT	AVG(1E * AmountPaid)
			FROM	(
					SELECT TOP(3)	x.AmountPaid
					FROM		dbo.factAdmissions AS x
					WHERE		x.AdmitCCYYMM <= c.AdmitCCYYMM
					ORDER BY	x.AdmitCCYYMM DESC
				) AS d
		) AS f(Average)
INNER JOIN	factMembership AS m ON m.EffectiveCCYYMM = c.AdmitCCYYMM
ORDER BY	c.AdmitCCYYMM;
Why divide by m.MemberID? Aren't you supposed to divide by m.MemberCount or similar? A value that actually changes for every month?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.27 seconds. Powered By: Snitz Forums 2000