CODEWITHSUNDEEP
sqlsql-servert-sqlselectgroup-by
EricBlair1984
EricBlair1984

Reputation: 379

Select a row in Windowed select based on set time difference in SQL

Using this table and data:

CREATE TABLE [dbo].[MyTable](
[session] [bigint] NULL,
[an] [varchar](10) NULL,
[skformat] [int] NULL,
[skmean] [int] NULL,
[mediatype] [varchar](10) NULL,
[rectime] [datetime] NULL,
[prod] [varchar](10) NULL
)

GO

INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (123, N'123abc', 2, 3, N'pdf', CAST(N'2017-09-27 
11:51:08.210' AS DateTime), N'abc')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (123, N'123abc', 2, 3, N'pdf', CAST(N'2017-09-27 
11:56:08.210' AS DateTime), N'def')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (123, N'456hlm', 4, 5, N'hlm', CAST(N'2017-09-27 
11:51:09.210' AS DateTime), N'ghi')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (123, N'456hlm', 4, 5, N'hlm', CAST(N'2017-09-27 
11:51:10.210' AS DateTime), N'xyz')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (123, N'1a2b3c', 2, 2, N'pdf', CAST(N'2017-09-27 
11:51:08.210' AS DateTime), N'fbi')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (555, N'555xyz', 1, 1, N'pdf', CAST(N'2017-09-27 
11:54:44.050' AS DateTime), N'abc')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (555, N'555xyz', 1, 1, N'pdf', CAST(N'2017-09-27 
11:54:40.050' AS DateTime), N'def')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (555, N'5x5y5z', 4, 6, N'hlm', CAST(N'2017-09-27 
11:54:45.050' AS DateTime), N'ghi')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (555, N'5x5y5z', 4, 6, N'hlm', CAST(N'2017-09-27 
11:54:45.050' AS DateTime), N'xyz')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (555, N'555xxx', 1, 2, N'pdf', CAST(N'2017-09-27 
11:54:44.050' AS DateTime), N'fbi')
INSERT [dbo].[MyTable] ([session], [an], [skformat], [skmean], [mediatype], 
[rectime], [prod]) VALUES (555, N'555xyz', 1, 1, N'pdf', CAST(N'2017-09-27 
11:54:39.050' AS DateTime), NULL)

I created this windowed query:

select session,an,skformat,skmean,mediatype,rectime,
row_number() over 
 (partition by session,an,skformat,skmean,mediatype
 order by rectime asc) as row
 from MyTable
 group by session,an,skformat,skmean,mediatype,rectime
 order by session, an

Good so far...

But what I'd like to do is just select the first row of each grouping (when there is more than one row) IF the time difference (rectime field) is less than 10 seconds when mediatype=pdf and is less than 30 seconds when mediatype=hlm

Stuck here. Advice appreciated.

This this the result I'm looking for:

session an  skformat    skmean  mediatype   rectime row

123 123abc  2   3   pdf 11:51:08 AM 1

123 123abc  2   3   pdf 11:56:08 AM 2

123 1a2b3c  2   2   pdf 11:51:08 AM 1

123 456hlm  4   5   hlm 11:51:09 AM 1

555 555xxx  1   2   pdf 11:54:44 AM 1

555 555xyz  1   1   pdf 11:54:39 AM 1

555 5x5y5z  4   6   hlm 11:54:45 AM 1

Thanks!

Upvotes: 2

Views: 65

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use this query.

;WITH T AS (
    SELECT *,
        row_number() over ( order by rectime desc ) as row
    FROM MyTable
 )
 SELECT session, an,  skformat,    skmean,  mediatype,   rectime FROM T T1
 WHERE 
 NOT EXISTS (SELECT * , DATEDIFF(SECOND, T2.rectime ,T1.rectime ) dff FROM T T2 WHERE 
        T2.row > T1.row 
        AND T2.session = T1.session
        AND T2.an = T1.an
        AND T2.skformat = T1.skformat
        AND T2.skmean = T1.skmean
        AND T2.mediatype = T1.mediatype
        AND ( DATEDIFF(SECOND, T2.rectime ,T1.rectime ) < CASE mediatype WHEN 'pdf ' THEN 10 ELSE 30 END)
        ) 
ORDER BY session, an, rectime

Result

session              an         skformat    skmean      mediatype  rectime
-------------------- ---------- ----------- ----------- ---------- -----------------------
123                  123abc     2           3           pdf        2017-09-27 11:51:08.210
123                  123abc     2           3           pdf        2017-09-27 11:56:08.210
123                  1a2b3c     2           2           pdf        2017-09-27 11:51:08.210
123                  456hlm     4           5           hlm        2017-09-27 11:51:09.210
555                  555xxx     1           2           pdf        2017-09-27 11:54:44.050
555                  555xyz     1           1           pdf        2017-09-27 11:54:39.050
555                  5x5y5z     4           6           hlm        2017-09-27 11:54:45.050

Upvotes: 1

Maur&#237;cio Pontalti Neri
Maur&#237;cio Pontalti Neri

Reputation: 1080

Try it:

WITH cte 
     AS (SELECT session 
                ,an 
                ,skformat 
                ,skmean 
                ,mediatype 
                ,rectime 
                ,Row_number() 
                   OVER ( 
                     partition BY session, an, skformat, skmean, mediatype 
                     ORDER BY rectime ASC) AS row 
                ,Lead(rectime, 1, rectime) 
                   OVER ( 
                     partition BY session, an, skformat, skmean, mediatype 
                     ORDER BY rectime ASC) rectime_lead 
         FROM   mytable 
         GROUP  BY session 
                   ,an 
                   ,skformat 
                   ,skmean 
                   ,mediatype 
                   ,rectime) 
SELECT * 
FROM   cte 
WHERE  row = 1 
       AND ( ( mediatype = 'pdf' 
               AND Datediff(second, rectime, rectime_lead) < 10 ) 
              OR ( mediatype = 'hlm' 
                   AND Datediff(second, rectime, rectime_lead) < 30 ) )

Upvotes: 0

Related Questions