April 21, 2015 at 4:49 am
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
April 21, 2015 at 5:53 am
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply