FIND GAP IN SQL
-
hi all, ( there is ascript at end 🙂 so you dont have to read all the story :))
first i will explain the logic behind the proccess 🙂
in my company there a lot of machines that send parameters
from time to time.
i have table that hold machine name parameter name,paramter value and time
that machine send. ( when parameter is raise)
the request was to :
extract sample of paramters for interval that supplly.
for exmple :
intalval=5 (minutes)
1) take first sampling for every machine
2) find if diff between first sampling to second sampling is less 15 ( @interval_miuntes)
go to next record on same machine .
3) if diff between first sampling to second sampling is above 15 ( @interval_miuntes)
second record become first record and start search next record with diff above or equal to 15 (( @interval_miuntes))
DROP TABLE #MACHINE_LOG
CREATE TABLE #MACHINE_LOG (MACHINE_NUMBER NVARCHAR (MAX),VALUE INT ,EVENT_DATE DATETIME)
declare @interval_miuntes int
SET @interval_miuntes=15
INSERT #MACHINE_LOG (MACHINE_NUMBER,VALUE,EVENT_DATE)
SELECT 'A',1,'2015-04-21 11:17:10.000'
union all
SELECT 'A',8,'2015-04-21 11:17:16.000'
union all
SELECT 'A',15,'2015-04-21 11:17:35.000'
union all
SELECT 'A',20,'2015-04-21 11:17:38.000'
union all
SELECT 'A',29,'2015-04-21 12:17:51.000'
union all
SELECT 'A',29,'2015-04-21 12:35:51.000'
UNION ALL
SELECT 'B',55,'2015-04-21 16:10:10.000'
union all
SELECT 'B',63,'2015-04-21 16:22:16.000'
union all
SELECT 'B',72,'2015-04-21 16:32:35.000'
union all
SELECT 'B',84,'2015-04-21 16:40:35.000'
union all
SELECT 'B',29,'2015-04-21 15:46:35.000'
UNION ALL
SELECT 'B',29,'2015-04-21 15:47:35.000'
--SELECT * FROM #MACHINE_LOG
IF @interval_miuntes =15
'A',1,'2015-04-21 11:17:10.000'
--('A',8,'2015-04-21 11:17:16.000') -- AVOID THIS RECORD IN RESAULT SET !! DIFF BETWEEN '2015-04-21 11:17:10.000' TO '2015-04-21 11:17:16.000' LESS 15
'A',15,'2015-04-21 11:17:35.000' - - OK DIIF BETWEEN '2015-04-21 11:17:35.000' TO '2015-04-21 11:17:10.000' ABOVE 15
--'A',20,2015-04-21 11:17:38.000 -- AVOID THIS RECORD IN RESAULT SET !! DIFF BETWEEN 2015-04-21 11:17:38.000 TO '2015-04-21 11:17:35.000' LESS 15
'A',29,'2015-04-21 12:17:51.000' -- OK DIIF BETWEEN '2015-04-21 12:17:51.000' TO '2015-04-21 11:17:35.000' ABOVE 15
'A',29,'2015-04-21 12:35:51.000' -- OK DIIF BETWEEN '2015-04-21 12:35:51.000' TO '2015-04-21 12:17:51.000' ABOVE 15
'B',55,'2015-04-21 16:10:10.000'
--'B', 63,'2015-04-21 16:22:16.000' -- AVOID THIS RECORD IN RESAULT SET !! DIFF BETWEEN '2015-04-21 16:22:16.000' TO '55,2015-04-21 16:10:10.000' LESS 15
'B',72,'2015-04-21 16:32:35.000' -- OK DIFF BETWEEN '2015-04-21 16:32:35.000' TO '2015-04-21 16:10:10.000' ABOVE 15
--'B', 84,'2015-04-21 16:40:35.000' -- AVOID THIS RECORD IN RESAULT SET !! DIFF BETWEEN '2015-04-21 16:40:35.000' TO '2015-04-21 16:32:35.000' LESS 15
'B', 29, '2015-04-21 15:46:35.000' -- OK DIFF BETWEEN '2015-04-21 15:46:35.000' TO '2015-04-21 16:32:35.000' ABOVE 15
--'B', 29, '2015-04-21 15:47:35.000' -- AVOID THIS RECORD IN RESAULT SET !! DIFF BETWEEN '2015-04-21 15:47:35.000' TO '2015-04-21 15:46:35.000' LESS 15
thanks alot i hope i was caler 🙂
sharon
Edited: Today @ 11:56 AM by sharon-472085
-
See http://www.sqlservercentral.com/Forums/Topic1676473-3412-1.aspx
Quirky update or recursive CTE is needed.
-
Here's a solution using recursion, don't expect it to be quick though.
WITH Ordered AS (
SELECT m.MACHINE_NUMBER,m.VALUE,m.EVENT_DATE,
ROW_NUMBER() OVER(PARTITION BY m.MACHINE_NUMBER ORDER BY m.EVENT_DATE) AS rn,
ca.VALUE AS NextVALUE,
ca.EVENT_DATE AS NextEVENT_DATE
FROM #MACHINE_LOG m
OUTER APPLY(SELECT TOP 1 m2.VALUE,m2.EVENT_DATE
FROM #MACHINE_LOG m2
WHERE m2.MACHINE_NUMBER = m.MACHINE_NUMBER AND m2.EVENT_DATE > m.EVENT_DATE
AND DATEDIFF(second,m.EVENT_DATE,m2.EVENT_DATE) > @interval_miuntes
ORDER BY m2.EVENT_DATE) ca
),
Recur AS (
SELECT MACHINE_NUMBER,VALUE,EVENT_DATE,NextVALUE,NextEVENT_DATE
FROM Ordered
WHERE rn=1
UNION ALL
SELECT o.MACHINE_NUMBER,o.VALUE,o.EVENT_DATE,o.NextVALUE,o.NextEVENT_DATE
FROM Ordered o
INNER JOIN Recur r ON r.MACHINE_NUMBER = o.MACHINE_NUMBER
AND r.NextVALUE = o.VALUE
AND r.NextEVENT_DATE = o.EVENT_DATE
)
SELECT MACHINE_NUMBER,VALUE,EVENT_DATE
FROM Recur
ORDER BY MACHINE_NUMBER,EVENT_DATE;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark, looks like OP needn't restrict rows by r.NextVALUE = o.VALUE only by MACHINE_NUMBER.
P.S. I mean (MACHINE_NUMBER, EVENT_DATE) is unique i guess. If not, VALUE must be present as it is in your query.
-
Recursion is not needed here if this is really being done in SQL 2012.
SELECT MACHINE_NUMBER, VALUE, EVENT_DATE
FROM
(
SELECT *
,s=DATEDIFF(second
,EVENT_DATE
,LEAD(EVENT_DATE, 1) OVER (PARTITION BY MACHINE_NUMBER ORDER BY EVENT_DATE))
FROM #MACHINE_LOG
) a
WHERE s >= 15;
Note that I had to use second in DATEDIFF because the EVENT_DATEs in your data are separated by seconds and not minutes.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (4/21/2015)
Recursion is not needed here if this is really being done in SQL 2012.If i got OP's task right, recursion is inevitable. As far as i understand having data as
INSERT #MACHINE_LOG (MACHINE_NUMBER,VALUE,EVENT_DATE)SELECT 'A',1,'2015-04-21 11:17:10.000'
union all
SELECT 'A',8,'2015-04-21 11:17:16.000'
union all
SELECT 'A',15,'2015-04-21 11:17:22.000'
union all
SELECT 'A',20,'2015-04-21 11:17:38.000'
union all
SELECT 'A',29,'2015-04-21 12:17:51.000'
union all
SELECT 'A',29,'2015-04-21 12:35:51.000'
the query must begin with a starting row being set to
( 'A',1,'2015-04-21 11:17:10.000')
then make a step, that is find first matching row
( 'A',20,'2015-04-21 11:17:38.000'),
set it as starting row and proceed with recursion.
It means the next step totally depends on the result of the previous step.
-
serg-52 (4/22/2015)
dwain.c (4/21/2015)
Recursion is not needed here if this is really being done in SQL 2012.If i got OP's task right, recursion is inevitable. As far as i understand having data as
INSERT #MACHINE_LOG (MACHINE_NUMBER,VALUE,EVENT_DATE)SELECT 'A',1,'2015-04-21 11:17:10.000'
union all
SELECT 'A',8,'2015-04-21 11:17:16.000'
union all
SELECT 'A',15,'2015-04-21 11:17:22.000'
union all
SELECT 'A',20,'2015-04-21 11:17:38.000'
union all
SELECT 'A',29,'2015-04-21 12:17:51.000'
union all
SELECT 'A',29,'2015-04-21 12:35:51.000'
the query must begin with a starting row being set to
( 'A',1,'2015-04-21 11:17:10.000')
then make a step, that is find first matching row
( 'A',20,'2015-04-21 11:17:38.000'),
set it as starting row and proceed with recursion.
It means the next step totally depends on the result of the previous step.
It is certainly possible that I misunderstood the requirement. Where is the OP when we need him?
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
THANK YOU VARY MUCH
ITS WORKED FOR ME
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply