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.

---

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

---

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

quote:

---

Originally posted by SwePeso

Are you using SQL Server 2012?

---

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

---

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

---

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)

---

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)

---

---

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

---

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

---

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

---

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;