Author |
Topic |
sunriser
Starting Member
14 Posts |
Posted - 2012-12-18 : 05:52:32
|
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.Sayfor 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 querysunriser |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-18 : 09:02:20
|
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. |
|
|
sunriser
Starting Member
14 Posts |
Posted - 2012-12-18 : 09:18:39
|
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 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-18 : 09:29:02
|
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 DATACREATE 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 ReadingFROM cte a OUTER APPLY ( SELECT TOP (3) reading FROM cte b WHERE b.rn < a.rn AND b.NC >= 3 ORDER BY rn DESC ) bGROUP BY a.rn, a.dt, a.readingORDER BY a.rn;-----------------------------------DROP TABLE #tmp; |
|
|
sunriser
Starting Member
14 Posts |
Posted - 2012-12-18 : 23:44:32
|
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 DATACREATE 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 ReadingFROM cte a OUTER APPLY ( SELECT TOP (3) reading FROM cte b WHERE b.rn < a.rn AND b.NC >= 3 ORDER BY rn DESC ) bGROUP BY a.rn, a.dt, a.readingORDER 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 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-19 : 09:31:24
|
If your data has a row for Dec 2012 with NULL for reading, it should calculate the average for that as well--- TEST DATACREATE 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); |
|
|
sunriser
Starting Member
14 Posts |
Posted - 2012-12-24 : 04:06:02
|
Tq...sunriser |
|
|
gtopawb
Starting Member
5 Posts |
Posted - 2012-12-27 : 02:12:10
|
unspammed |
|
|
|
|
|