| Author |
 Topic  |
|
|
sunriser
Starting Member
14 Posts |
 Posted - 12/31/2012 : 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 |
Edited by - sunriser on 12/31/2012 01:40:45
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/31/2012 : 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 |
 |
|
| |
Topic |
|
|
|
First and Third Record
