First and Third Record - SQL Server Forums

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)

First and Third Record

Author

Topic

sunriser
Starting Member

14 Posts

Posted - 2012-12-31 : 01:04:24

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-31 : 10:38:45

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources