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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Estimatation Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sunriser
Starting Member

14 Posts

Posted - 12/18/2012 :  05:52:32  Show Profile  Reply with Quote
Hi All,

I have a requirement like , i will be getting the reading of a machine for every month.
If i did not get any reading then i have to estimate the reading by taking the average of the
readings of that particular machine where last 3 CONSECUTIVE column is "NO" in descending order.
Say
for April-2012, i did not get the reading, then i have to take the average readings of
(march+Feb+Jan) as March,Feb and Jan estimated column is "No".

for July-2012 ,
As i have only 2 consecutive "NO", BEFORE July, i have to check where there are 3 consecutive zeros.
so again i will be getting (march+Feb+Jan)

Similarly for Sept.


Machine  Month-Year  Reading           estimated
 X        Nov-2011   44                   No  
 X        Dec-2011   58                   No
 X        Jan-2012   45                   No
 X        Feb-2012   60                   No
 X        Mar-2012   51                   No
 X        Apr-2012  (51+60+45)/3          Yes
 X        May-2012   65                   No
 X        Jun-2012   69                   No
 X        Jul-2012  (51+60+45)/3          Yes
 X        Aug-2012   33                   No
 X        Sep-2012  (51+60+45)/3          Yes
 X        Oct-2012   54                   No
 X        Nov-2012   66                   No
.

Can any one help me getting the query

sunriser

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/18/2012 :  09:02:20  Show Profile  Reply with Quote
You wouldn't happen to be on SQL 2012, would you? If you were, this would be so much easier than trying to cook up something for earlier versions of SQL Server.
Go to Top of Page

sunriser
Starting Member

14 Posts

Posted - 12/18/2012 :  09:18:39  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

You wouldn't happen to be on SQL 2012, would you? If you were, this would be so much easier than trying to cook up something for earlier versions of SQL Server.


Sorry for not mentioning the Version.
We are using 2008 Server.
Yes in SQL 2012 , we have lead and lag function i suppose, which makes it easier.

sunriser

Edited by - sunriser on 12/18/2012 09:21:17
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/18/2012 :  09:29:02  Show Profile  Reply with Quote
Here is a possibility. I don't particularly want to stand behind this - seems more convoluted than it needs to be. Hopefully, now that I have created the DDL for the test data, someone else will post a more elegant and simpler solution (It always helps to post code that someone can copy and paste to generate the test data as I have done here)
--- TEST DATA
CREATE TABLE #tmp(dt DATETIME, reading INT);
INSERT INTO #tmp VALUES 
	('20111101',44),('20111201',58),('20120101',45),('20120201',60),('20120301',51),
	('20120401',NULL),('20120501',65),('20120601',69),('20120701',NULL),('20120801',33),
	('20120901',NULL),('20121001',54),('20121101',66);
-------------------------
--- QUERY
;WITH cte AS
(
	SELECT *,
		   COUNT(*) OVER(PARTITION BY(N)) NC
	FROM   (
			   SELECT ROW_NUMBER() OVER(ORDER BY dt) AS rn,
					  ROW_NUMBER() OVER(ORDER BY dt) -
					  ROW_NUMBER() OVER(
						  PARTITION BY CASE 
											WHEN reading IS NULL THEN 1
											ELSE 0
									   END ORDER BY dt
					  ) N,
					  *
			   FROM   #tmp t
		   )s
)
SELECT
	a.dt,
	COALESCE(a.reading,SUM(b.reading) /3.0) AS Reading
FROM
	cte a
	OUTER APPLY
	(
		SELECT TOP (3) reading
		FROM cte b
		WHERE b.rn < a.rn
		AND b.NC >= 3
		ORDER BY rn DESC 
	) b
GROUP BY
	a.rn,
	a.dt,
	a.reading
ORDER BY a.rn;
-----------------------------------

DROP TABLE #tmp;
Go to Top of Page

sunriser
Starting Member

14 Posts

Posted - 12/18/2012 :  23:44:32  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

Here is a possibility. I don't particularly want to stand behind this - seems more convoluted than it needs to be. Hopefully, now that I have created the DDL for the test data, someone else will post a more elegant and simpler solution (It always helps to post code that someone can copy and paste to generate the test data as I have done here)
--- TEST DATA
CREATE TABLE #tmp(dt DATETIME, reading INT);
INSERT INTO #tmp VALUES 
	('20111101',44),('20111201',58),('20120101',45),('20120201',60),('20120301',51),
	('20120401',NULL),('20120501',65),('20120601',69),('20120701',NULL),('20120801',33),
	('20120901',NULL),('20121001',54),('20121101',66);
-------------------------
--- QUERY
;WITH cte AS
(
	SELECT *,
		   COUNT(*) OVER(PARTITION BY(N)) NC
	FROM   (
			   SELECT ROW_NUMBER() OVER(ORDER BY dt) AS rn,
					  ROW_NUMBER() OVER(ORDER BY dt) -
					  ROW_NUMBER() OVER(
						  PARTITION BY CASE 
											WHEN reading IS NULL THEN 1
											ELSE 0
									   END ORDER BY dt
					  ) N,
					  *
			   FROM   #tmp t
		   )s
)
SELECT
	a.dt,
	COALESCE(a.reading,SUM(b.reading) /3.0) AS Reading
FROM
	cte a
	OUTER APPLY
	(
		SELECT TOP (3) reading
		FROM cte b
		WHERE b.rn < a.rn
		AND b.NC >= 3
		ORDER BY rn DESC 
	) b
GROUP BY
	a.rn,
	a.dt,
	a.reading
ORDER BY a.rn;
-----------------------------------

DROP TABLE #tmp;




Thank You sunitabeck.
This query works fine. But i want to get only Dec-2012 value reading, which i have to Estimate.

sunriser
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/19/2012 :  09:31:24  Show Profile  Reply with Quote
If your data has a row for Dec 2012 with NULL for reading, it should calculate the average for that as well
--- TEST DATA
CREATE TABLE #tmp(dt DATETIME, reading INT);
INSERT INTO #tmp VALUES 
	('20111101',44),('20111201',58),('20120101',45),('20120201',60),('20120301',51),
	('20120401',NULL),('20120501',65),('20120601',69),('20120701',NULL),('20120801',33),
	('20120901',NULL),('20121001',54),('20121101',66),('20121201',NULL);
Go to Top of Page

sunriser
Starting Member

14 Posts

Posted - 12/24/2012 :  04:06:02  Show Profile  Reply with Quote
Tq...

sunriser
Go to Top of Page

gtopawb
Starting Member

5 Posts

Posted - 12/27/2012 :  02:12:10  Show Profile  Reply with Quote
unspammed

Edited by - gtopawb on 12/27/2012 02:13:24
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.38 seconds. Powered By: Snitz Forums 2000