Get the Start and end date based on a criteria.

Question

Post reply

Get the Start and end date based on a criteria.

ksrikanth77

SSCrazy

Points: 2232
More actions
April 17, 2014 at 1:04 pm

#396623

Hi All,
I have a scenario where i need to get the starting and ending date time based on the crieteria. The criteria is I always have my start date as NS or GS in the data column and my end date as GX so i need NS or GS to be my strart date based on ts Ascending and my end date as GX to be displayed in the same columns .Please help me get the required output.Thank you in advance.
Create Table Test
(Tsq INT IDENTITY (1,1),
Data Varchar (150),
ts datetime,
Tpkt_type int)
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2')
insert into test values ('NS,000020,000021,000022,000023','2013-11-13 09:47:36.180','2')
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:47:37.007','2')
insert into test values ('GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4')
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2')
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:50:26.190','2')
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:50:26.393','2')
insert into test values ('GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4')
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2')
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:51:28.547','2')
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:51:28.767','2')
insert into test values ('GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4')
insert into test values ('NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18')
insert into test values ('GS,000020,000021,000022,000023','2013-12-17 16:51:09.063','2')
insert into test values ('GS,000020,000021,000022,000023','2013-12-17 16:51:09.063','2')
insert into test values ('GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4')
Expected Output
---------Data----------------- ts as starttime--------------tpkt_type------data-----------------------ts as endtime--------tpkttype-
'GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2' 'GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4'
'GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2', 'GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4'
'GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2', 'GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4'
'NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18', 'GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4'

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Pretty easy with windowing functions (if you have SQL Server 2012).

The code is quite long but that's because I have a soft spot for CTEs. The queries itself are pretty basic.

--CREATE TABLE dbo.Test(

-- TsqINT IDENTITY (1,1)

--,DataVARCHAR(150)

--,tsDATETIME

--,Tpkt_typeINT);

--INSERT INTO dbo.Test VALUES ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2');

--INSERT INTO dbo.Test VALUES ('NS,000020,000021,000022,000023','2013-11-13 09:47:36.180','2');

--INSERT INTO dbo.Test VALUES ('GS,000020,000021,000022,000023','2013-11-13 09:47:37.007','2');

--INSERT INTO dbo.Test VALUES ('GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4');

--INSERT INTO dbo.Test VALUES ('GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2');

--INSERT INTO dbo.Test VALUES ('GS,000020,000021,000022,000023','2013-11-13 09:50:26.190','2');

--INSERT INTO dbo.Test VALUES ('GS,000020,000021,000022,000023','2013-11-13 09:50:26.393','2');

--INSERT INTO dbo.Test VALUES ('GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4');

--INSERT INTO dbo.Test VALUES ('GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2');

--INSERT INTO dbo.Test VALUES ('GS,000020,000021,000022,000023','2013-11-13 09:51:28.547','2');

--INSERT INTO dbo.Test VALUES ('GS,000020,000021,000022,000023','2013-11-13 09:51:28.767','2');

--INSERT INTO dbo.Test VALUES ('GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4');

--INSERT INTO dbo.Test VALUES ('NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18');

--INSERT INTO dbo.Test VALUES ('GS,000020,000021,000022,000023','2013-12-17 16:51:09.063','2');

--INSERT INTO dbo.Test VALUES ('GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4');

WITH CTE_Classification AS

(

SELECT

Data

,ts

,Tpkt_type

,Classification = CASEWHEN (Marker = 'GS' OR Marker = 'NS') AND PreviousValue IS NULL -- first row of the entire set

THEN 'First'

WHEN (Marker = 'GS' OR Marker = 'NS') AND PreviousValue = 'GX' -- first row of new set

THEN 'First'

WHEN (Marker = 'GS' OR Marker = 'NS') AND (PreviousValue = 'GS' OR PreviousValue = 'NS') -- somewhere in the middle

THEN 'Middle'

WHEN Marker = 'GX' -- last row of a set

THEN 'Last'

END

FROM

(

SELECT

Data

,ts

,Tpkt_type

,Marker= LEFT(Data,2)

,PreviousValue= LAG(LEFT(Data,2)) OVER (ORDER BY ts)

FROM dbo.Test

) tmp

)

, CTE_FirstLast AS

(

SELECT *

FROM CTE_Classification

WHERE Classification <> 'Middle' -- these rows are not needed

)

SELECT StartData, StartTime, StartTime, EndData, EndTime, EndType

FROM

(

SELECT

StartData= CASE WHEN Classification = 'First'

THEN Data

ELSE NULL

END

,StartTime= CASE WHEN Classification = 'First'

THEN ts

ELSE NULL

END

,StartType= CASE WHEN Classification = 'First'

THEN Tpkt_type

ELSE NULL

END

,EndData= CASE WHEN Classification = 'First'

THEN LEAD(Data) OVER (ORDER BY ts)

ELSE NULL

END

,EndTime= CASE WHEN Classification = 'First'

THEN LEAD(ts) OVER (ORDER BY ts)

ELSE NULL

END

,EndType= CASE WHEN Classification = 'First'

THEN LEAD(Tpkt_type) OVER (ORDER BY ts)

ELSE NULL

END

,Classification

,ts

FROM CTE_FirstLast ) tmp

WHERE Classification = 'First'

ORDER BY ts;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Oh yeah, no-one has ever died of giving columns readable names...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

gbritton1 SSCertifiable Points: 6522 More actions · Answer 3

Here's a solution that doesn't depend on SQL Server 2012:

declare @test-2 table

(Tsq INT IDENTITY (1,1),

Data Varchar (150),

ts datetime,

Tpkt_type int)

insert into @test-2 values ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2') --1

insert into @test-2 values ('NS,000020,000021,000022,000023','2013-11-13 09:47:36.180','2')

insert into @test-2 values ('GS,000020,000021,000022,000023','2013-11-13 09:47:37.007','2')

insert into @test-2 values ('GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4') --1

insert into @test-2 values ('GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2') --2

insert into @test-2 values ('GS,000020,000021,000022,000023','2013-11-13 09:50:26.190','2')

insert into @test-2 values ('GS,000020,000021,000022,000023','2013-11-13 09:50:26.393','2')

insert into @test-2 values ('GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4') --2

insert into @test-2 values ('GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2') --3

insert into @test-2 values ('GS,000020,000021,000022,000023','2013-11-13 09:51:28.547','2')

insert into @test-2 values ('GS,000020,000021,000022,000023','2013-11-13 09:51:28.767','2')

insert into @test-2 values ('GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4') --3

insert into @test-2 values ('NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18') --4

insert into @test-2 values ('GS,000020,000021,000022,000023','2013-12-17 16:51:09.063','2')

insert into @test-2 values ('GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4') --4

;with

GX as (

select *, row_number() over(order by ts) rn

from @test-2

where Data like 'GX%'

),

buckets as (

Select gx1.ts gxbegin, gx2.ts gxend, gx2.data gxdata, gx2.Tpkt_type gxtype

from GX gx1

right join gx gx2

on gx2.rn = gx1.rn+1

),

bucketed as (

select t.*, b.* from @test-2 t

join buckets b

on t.ts < b.gxend and (t.ts > b.gxbegin or b.gxbegin is null)

),

filtered as (

select t.*, b.gxdata, b.gxend, b.gxtype from buckets b

cross apply (

select top(1) * from @test-2 t

where t.ts < b.gxend

and (t.ts > b.gxbegin or b.gxbegin is null)

order by t.ts asc

) t

)

select * from filtered

thava SSCrazy Points: 2275 More actions · Answer 4

how about this one keep an eye on the primary key

DECLARE @test-2 TABLE

(

ID INT IDENTITY(1, 1) PRIMARY KEY, DATA VARCHAR(150), ts DATETIME, Tpkt_type INT

)

insert into @test-2

values

('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2'),

('NS,000020,000021,000022,000023','2013-11-13 09:47:36.180','2'),

('GS,000020,000021,000022,000023','2013-11-13 09:47:37.007','2'),

('GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4'),

('GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2'),

('GS,000020,000021,000022,000023','2013-11-13 09:50:26.190','2'),

('GS,000020,000021,000022,000023','2013-11-13 09:50:26.393','2'),

('GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4'),

('GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2'),

('GS,000020,000021,000022,000023','2013-11-13 09:51:28.547','2'),

('GS,000020,000021,000022,000023','2013-11-13 09:51:28.767','2'),

('GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4'),

('NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18'),

('GS,000020,000021,000022,000023','2013-12-17 16:51:09.063','2'),

('GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4')

;

WITH Datas AS(

SELECT t.ID, t.[Data], t.ts, t.Tpkt_type, CASE

WHEN t.Tpkt_type=4 THEN 1

ELSE 0

END Grp

FROM @test-2 t

),Groups AS (

SELECT ROW_NUMBER() OVER(ORDER BY d.id) r,

ROW_NUMBER() OVER(PARTITION BY d.grp ORDER BY d.id) -Grp rn,

d.*

FROM datas d

),results AS(

SELECT ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY d2.id) r, d2.[Data] sData, d2.ts sDate,

d.[Data] EData, d.ts EDate

FROM groups d INNER JOIN groups d2

ON d.rn = (d2.r-d2.rn) AND

d.grp = 1 AND

d2.grp<>1

)

SELECT *

FROM results WHERE r=1

;

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

ksrikanth77 SSCrazy Points: 2232 More actions · Answer 5

I thank all of you for providing me solutions for different versions, however I thought of playing with the data once it is filtered with the earlier logic. However I would like to take more help on the same with some extended options.

My Output is similar to the earlier one however I need to generate a unique Identifier for the Rows between NS or GS ending GX and also get the alarmstatus as Yes or no based on alarm column ( If left(DATA,2 ) = GA and alarm value is 1 in between the range of one NS/GS TO GX combination then Yes else No)

Could you please help me to generate the NEWID for PID and also get the Alarmstatus. Thanks in Advance.

declare @test-2 table

(

ID INT IDENTITY(1, 1) PRIMARY KEY, DATA VARCHAR(150)NULL, ts DATETIME, Tpkt_type INT,PID uniqueidentifier, alarmstatus varchar (3) , alarm int

)

insert into @test-2

values

('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2',NULL,NULL,0),

('GA,000020,000021,000022,000023','2013-11-13 09:47:35.963','2',NULL,NULL,1),

('NS,000020,000021,000022,000023','2013-11-13 09:47:36.180','2',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-13 09:47:37.007','2',NULL,NULL,0),

('GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2',NULL,NULL,0),

('GA,000020,000021,000022,000023','2013-11-13 09:50:25.987','2',NULL,NULL,1),

('GA,000020,000021,000022,000023','2013-11-13 09:50:25.987','2',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-13 09:50:26.190','2',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-13 09:50:26.393','2',NULL,NULL,0),

('GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-13 09:51:28.547','2',NULL,NULL,0),

('GA,000020,000021,000022,000023','2013-11-13 09:51:28.547','2',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-13 09:51:28.767','2',NULL,NULL,0),

('GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2',NULL,NULL,0),

('GA,000020,000021,000022,000023','2013-11-13 09:47:35.963','2',NULL,NULL,0),

('NS,000020,000021,000022,000023','2013-11-13 09:57:36.180','2',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-13 09:57:37.007','2',NULL,NULL,0),

('GX,1,0000000000000000000000000','2013-11-13 09:57:37.007','4',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-13 09:59:25.987','2',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-13 09:59:26.190','2',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-13 09:59:26.393','2',NULL,NULL,0),

('GX,1,0000000000000000000000000','2013-11-13 09:59:40.920','4',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-15 09:51:28.330','2',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-15 09:51:28.547','2',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-11-15 09:51:28.767','2',NULL,NULL,0),

('GX,1,0000000000000000000000000','2013-11-15 09:51:43.257','4',NULL,NULL,0),

('NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-12-17 16:51:09.063','2',NULL,NULL,0),

('GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4',NULL,NULL,0),

('NS,000020,000021,000022,000023','2013-12-18 16:51:09.063','18',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-12-18 16:51:15.257','2',NULL,NULL,0),

('GA,000020,000021,000022,000023','2013-12-18 16:51:15.257','2',NULL,NULL,0),

('GA,000020,000021,000022,000023','2013-12-18 16:51:15.257','2',NULL,NULL,1),

('GX,1,0000000000000000000000000','2013-12-18 16:51:15.257','4',NULL,NULL,0),

('NS,000020,000021,000022,000023','2013-12-18 16:51:15.257','18',NULL,NULL,0),

('GS,000020,000021,000022,000023','2013-12-18 16:51:15.257','2',NULL,NULL,0),

('GA,000020,000021,000022,000023','2013-12-18 16:51:15.257','2',NULL,NULL,0),

('GX,1,0000000000000000000000000','2013-12-18 16:51:15.257','4',NULL,NULL,0)

SELECT * FROM @test-2

Expected output

--------Data----------------- ts as starttime--------------tpkt_type------data-----------------------ts as endtime--------tpkttype----PID------------------------------alarmstatus--alarm

'GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2' 'GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4','2C616A75-2CAB-42E7-8649-BD282DF867F6','yes',1

'GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2', 'GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4','C9C1E83B-72D2-4E69-B973-BD56A60CE34D','yes',1

'GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2', 'GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4','B90B57A5-B2E7-4CE4-B6DB-BEA11093765E''No',0

'GS,000020,000021,000022,000023','2013-11-13 09:57:35.963','2' 'GX,1,0000000000000000000000000','2013-11-13 09:57:37.007','4','071DD582-B49D-4031-98B0-C18F37283C5D''NO',0

'GS,000020,000021,000022,000023','2013-11-13 09:59:25.987','2' 'GX,1,0000000000000000000000000','2013-11-13 09:59:40.920','4','02122182-B49D-4031-98B0-C18F372C5D12','No',0

'GS,000020,000021,000022,000023','2013-11-15 09:51:28.330','2' 'GX,1,0000000000000000000000000','2013-11-15 09:51:43.257','4','AD22182-B49D-4031-98B0-DS18F372C5451','no',0

'NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18', 'GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4','DD450881-76C7-45F8-B121-C06E52D0777E','no',0

'NS,000020,000021,000022,000023','2013-12-18 16:51:09.063','18', 'GX,1,0000000000000000000000000','2013-12-18 16:51:15.257','4','AS450881-76C7-45F8-B121-SD6E52D0777E','Yes',1

'NS,000020,000021,000022,000023','2013-12-18 16:51:15.257','18', 'GX,1,0000000000000000000000000','2013-12-18 16:51:15.257','4','RTEW0881-76C7-45F8-B121-ERE6E52D0KOSL','No',0

ksrikanth77 SSCrazy Points: 2232 More actions · Answer 6

Sorry there is a typo in the Insert statement

the Tpkt_type for GA is 12 and not 4,18 or 2

thava SSCrazy Points: 2275 More actions · Answer 7

The solution is already there making a small adjustment will produce a result you want

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

ksrikanth77 SSCrazy Points: 2232 More actions · Answer 8

Hi ,

Could you please let me know the adjustment in the query to get my desired results, I will give a try.

Thanks

SR

thava SSCrazy Points: 2275 More actions · Answer 9

create a case when expression on the base cte like this

CASE

WHEN t.[DATA] LIKE 'GA%' THEN 1

ELSE 0

END AS Alarm

after that in the result cte just add the following

SUM(d2.alarm) OVER(PARTITION BY d.id) AS No_Of_Alarm

This will give the total no of alarms on a group using this you can put yes or no and whatever you want

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]