CODEWITHSUNDEEP
sqlsql-server-2008
user3376315
user3376315

Reputation: 41

How do I return the min(Time) of 2 consecutive rows where the time is 15 min apart, grouped by the date

I have data in a table:

select SiteName, LName, Loc1, Appt, Date, Time, Dur from appt 

SiteName    LName   Loc1    Appt    Date        Time    Dur RN
Main Office Jones   MO      Annual  20171018    1330    15  1
Main Office Jones   MO      Annual  20171018    1345    15  2


Main Office Jones   MO      Annual  20171018    1530    15  3
Main Office Jones   MO      Annual  20171018    1545    15  4


Main Office Jones   MO      Annual  20171024    1045    15  1-----I don't need this row


Main Office Jones   MO      Annual  20171024    1530    15  2
Main Office Jones   MO      Annual  20171024    1545    15  3


Main Office Jones   MO      Annual  20171025    1330    15  1
Main Office Jones   MO      Annual  20171025    1345    15  2


Main Office Jones   MO      Annual  20171025    1430    15  3
Main Office Jones   MO      Annual  20171025    1445    15  4


Main Office Jones   MO      Annual  20171025    1530    15  5
Main Office Jones   MO      Annual  20171025    1545    15  6


Main Office Jones   MO      Annual  20171027    1045    15  1-----I don't need this row

Main Office Jones   MO      Annual  20171027    1530    15  2
Main Office Jones   MO      Annual  20171027    1545    15  3


Main Office Jones   MO      Annual  20171031    1030    15  1
Main Office Jones   MO      Annual  20171031    1045    15  2


Main Office Jones   MO      Annual  20171031    1330    15  3
Main Office Jones   MO      Annual  20171031    1345    15  4


Main Office Jones   MO      Annual  20171031    1430    15  5
Main Office Jones   MO      Annual  20171031    1445    15  6


Main Office Jones   MO      Annual  20171031    1530    15  7
Main Office Jones   MO      Annual  20171031    1545    15  8


Main Office Jones   MO      Annual  20171101    1145    15  1

Main Office Jones   MO      Annual  20171101    1330    15  2
Main Office Jones   MO      Annual  20171101    1345    15  3

Main Office Jones   MO      Annual  20171101    1430    15  4
Main Office Jones   MO      Annual  20171101    1445    15  5

I'm trying to return the min(Time) of 2 consecutive rows where the time is 15 min apart, grouped by the date

For example, my results should look like this:

Results:

SiteName    LName   Loc1    Appt    Date        Time    Dur 
Main Office Jones   MO      Annual  20171018    1330    15  
Main Office Jones   MO      Annual  20171018    1530    15  
Main Office Jones   MO      Annual  20171024    1530    15  
Main Office Jones   MO      Annual  20171025    1330    15  
Main Office Jones   MO      Annual  20171025    1430    15  
Main Office Jones   MO      Annual  20171025    1530    15  
Main Office Jones   MO      Annual  20171027    1530    15  
Main Office Jones   MO      Annual  20171031    1030    15  
Main Office Jones   MO      Annual  20171031    1330    15  
Main Office Jones   MO      Annual  20171031    1430    15  
Main Office Jones   MO      Annual  20171031    1530    15  
Main Office Jones   MO      Annual  20171101    1330    15  
Main Office Jones   MO      Annual  20171101    1430    15

This is the query that I have so far but I need additional logic to show min(Time) of 2 consecutive rows where the time is 15 min apart, grouped by the date 

    select t.SiteName,  t.LName,    t.Loc1, t.Appt, t.Date,  min(t.Time) as Time, Dur
    from appt t
        inner join (select Date,
                       max(rn) rn_x
                from appt
                where rn % 2 = 0 
                group by Date ) x
        on t.Date = x.Date
       and t.rn <= x.rn_x
    group by t.SiteName,    t.LName,    t.Loc1, t.Appt, t.Date,  t.Dur,
           case when rn%2 = 0
                    then rn/2 - 1
           else rn/2
           end

Everything is working ok, except when I encounter data like this in my table

 select SiteName, LName, Loc1, Appt, Date, Time, Dur from appt 

 SiteName        LName      Loc1    Appt        Date        Time    Dur 
 Main Office    Jones       MO  Office Visit    20171108    1300    15  
 Main Office    Jones       MO  Office Visit    20171108    1315    15  
 Main Office    Jones       MO  Office Visit    20171108    1330    15  
 Main Office    Jones       MO  Office Visit    20171108    1345    15

The results are returning

 SiteName        LName  Loc1    Appt        Date        Time    Dur
 Main Office    Jones   MO  Office Visit    20171108    1300    15
 Main Office    Jones   MO  Office Visit    20171108    1315    15------But I don't need this row
 Main Office    Jones   MO  Office Visit    20171108    1330    15

Upvotes: 2

Views: 49

Answers (1)

Alberto Martinez
Alberto Martinez

Reputation: 2670

I'm not sure why are you using rn to get the results since it doesn't seem to be related to the date and time columns. If you want to obtain rows with times 15 minutes apart the best option is combining the date and time columns into a single datetime column so you work with the full dates.

Also, I don't know if date/time are of type date/time or are just char columns, so I'm going to put the two versions:

date/time columns of type date/time respectively

with Appointments_DT as (
  select *, cast([date] as datetime)+cast([time] as datetime) as AppDatetime
  from Appointments
)
select SiteName, LName, Loc1, Appt, [Date], [Time], Dur
from Appointments_DT as ADT1
where exists (select * from Appointments_DT AS ADT2
  where ADT2.SiteName=ADT1.SiteName
    and ADT2.LName=ADT1.LName
    and ADT2.Loc1=ADT1.Loc1
    and ADT2.Appt=ADT1.Appt
    and ADT2.AppDatetime=dateadd(minute,15,ADT1.AppDatetime))

SQL fiddle

date/time columns of type char(8)/char(4) respectively

with Appointments_DT as (
  select *, cast([date]+' '+left([time],2)+':'+right([time],2) as datetime) as AppDatetime
  from Appointments
)
select SiteName, LName, Loc1, Appt, [Date], [Time], Dur
from Appointments_DT as ADT1
where exists (select * from Appointments_DT AS ADT2
  where ADT2.SiteName=ADT1.SiteName
    and ADT2.LName=ADT1.LName
    and ADT2.Loc1=ADT1.Loc1
    and ADT2.Appt=ADT1.Appt
    and ADT2.AppDatetime=dateadd(minute,15,ADT1.AppDatetime))

SQL fiddle

In both versions we use EXISTS to get the rows that have another row with the same location and type but exactly 15 minutes later.

Upvotes: 1

Related Questions