Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Estimatation Query

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.
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
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.
Go to Top of Page

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
Go to Top of Page

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 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 - 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 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
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 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 - 2012-12-24 : 04:06:02
Tq...

sunriser
Go to Top of Page

gtopawb
Starting Member

5 Posts

Posted - 2012-12-27 : 02:12:10
unspammed
Go to Top of Page
   

- Advertisement -