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)
 First and Third Record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sunriser
Starting Member

14 Posts

Posted - 12/31/2012 :  01:04:24  Show Profile  Reply with Quote
Hi All,
I have a requirement , like after receiving the data
we have to keep the reading's in the descending order partition by machine id and we have to take the 1st and 3rd record and we have to calculate the percentage difference
between the first and 3rd reading and this percentage has to be compared with the threshold value(say some constant 25 %).
If the calculated percentage increases the threshold value, then i have to get the third reading ID.

note: If we get the same reading more then once then we have to give priority to first month in that readings.


CREATE TABLE #tmp(id integer,machine_id varchar(10),dt DATETIME, reading INT);
INSERT INTO #tmp VALUES
(1,'1','20120101',60),(2,'1','20120201',60),(3,'1','20120301',60),
(4,'1','20120401',36),(5,'1','20120501',46),(6,'1','20120601',41),(7,'1','20120701',56),(8,'1','20120801',98),
(9,'1','20120901',54),(10,'1','20121001',59),(11,'1','20121101',66),(12,'1','20121201',66),
(13,'2','20120101',30),(14,'2','20120201',30),(15,'2','20120301',50),
(16,'2','20120401',18),(17,'2','20120501',23),(18,'2','20120601',20),(19,'2','20120701',28),(20,'2','20120801',49),
(21,'2','20120901',27),(22,'2','20121001',30),(23,'2','20121101',33),(24,'2','20121201',37);

Can any 1 help me getting the query
sunriser

Edited by - sunriser on 12/31/2012 01:40:45

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/31/2012 :  10:38:45  Show Profile  Reply with Quote

CREATE TABLE #tmp(id integer,machine_id varchar(10),dt DATETIME, reading INT);
INSERT INTO #tmp VALUES 
(1,'1','20120101',60),(2,'1','20120201',60),(3,'1','20120301',60),
(4,'1','20120401',36),(5,'1','20120501',46),(6,'1','20120601',41),(7,'1','20120701',56),(8,'1','20120801',98),
(9,'1','20120901',54),(10,'1','20121001',59),(11,'1','20121101',66),(12,'1','20121201',66),
(13,'2','20120101',30),(14,'2','20120201',30),(15,'2','20120301',50),
(16,'2','20120401',18),(17,'2','20120501',23),(18,'2','20120601',20),(19,'2','20120701',28),(20,'2','20120801',49),
(21,'2','20120901',27),(22,'2','20121001',30),(23,'2','20121101',33),(24,'2','20121201',37);

--Select * from #tmp

; with CTE as
(
Select *,DENSE_RANK() OVER (PARTITION BY machine_id Order by reading desc) as Seq,
ROW_NUMBER() OVER (PARTITION BY machine_id,reading Order by dt desc) as Seq1
from #tmp
)

Select * from CTE C
inner join
(
Select A.*, (A.reading - B.reading)*100/B.reading PERCENTG
from
(
Select * from CTE
Where Seq in (1) and Seq1 = 1
)A
inner join
(
Select * from CTE
Where Seq in (3) and Seq1 = 1
)B on A.machine_id = B.machine_id
)P on P.machine_id = C.machine_id
Where C.Seq = Case When P.PERCENTG > 25 then 3 Else 1 End and C.Seq1 = 1

drop table #tmp

id	machine_id	dt	reading	Seq	Seq1	id	machine_id	dt	reading	Seq	Seq1	PERCENTG
3	1	2012-03-01 00:00:00.000	60	3	1	8	1	2012-08-01 00:00:00.000	98	1	1	63
24	2	2012-12-01 00:00:00.000	37	3	1	15	2	2012-03-01 00:00:00.000	50	1	1	35
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.08 seconds. Powered By: Snitz Forums 2000