CODEWITHSUNDEEP
sqlsql-server-2008
user6391599
user6391599

Reputation: 41

How do I select the min(Time) based on 3 consecutive rows which are grouped by Date

This is the data in mytable:

select Name, LastName, Id, LocationId, Date, Time, RN from mytable


Name    LastName    Id  LocationId  Date        Time        RN
Office  Jones       284 NY          20171004    1540        1
Office  Jones       284 NY          20171004    1545        2
Office  Jones       284 NY          20171004    1550        3

Office  Jones       284 NY          20171004    1650        4

Office  Jones       284 NY          20171113    1030        1
Office  Jones       284 NY          20171113    1035        2
Office  Jones       284 NY          20171113    1040        3

Office  Jones       284 NY          20171113    1130        4
Office  Jones       284 NY          20171113    1135        5
Office  Jones       284 NY          20171113    1140        6

Office  Jones       284 NY          20171113    1230        7
Office  Jones       284 NY          20171113    1435        8
Office  Jones       284 NY          20171113    1740        9

Office  Jones       284 NY          20171113    1840        10

Office  Jones       284 NY          20171115    1030        1
Office  Jones       284 NY          20171115    1035        2
Office  Jones       284 NY          20171115    1040        3

I would like to display my results as Name, LastName, Id, LocationId, Date, min(Time) based on 3 consecutive rows which are grouped by Date

Name    LastName    Id  LocationId  Date        Time
Office  Jones       284 NY          20171004    1540
Office  Jones       284 NY          20171113    1030
Office  Jones       284 NY          20171113    1130
Office  Jones       284 NY          20171113    1230
Office  Jones       284 NY          20171115    1030

This is what I have so far but I'm not able to figure out how to get the other 2 rows into my results

SELECT  Name,   LastName,   Id, LocationId, Date,  min(Time) as Time FROM mytable
WHERE Date IN (SELECT Date
                       FROM mytable
                      WHERE RN = 3)
GROUP BY Name,  LastName,   Id, LocationId, Date



Name    LastName    Id  LocationId  Date        Time
Office  Jones       284 NY          20171004    1540
Office  Jones       284 NY          20171113    1030
Office  Jones       284 NY          20171115    1030

Upvotes: 0

Views: 35

Answers (2)

ben
ben

Reputation: 329

here is a simplified version the removes the constant values columns, just focusing on the columns needed for the calculation (which I'd dumped into a temp table). the derived table in the inner join determines the max row number, per date that is a multiple of three, and the clustering by three of the result is on the group by of the outer query:

select #tmp.dates,
       min(#tmp.times)
from #tmp
inner join (select dates,
                   max(rnum) rnum_x
            from #tmp
            where rnum % 3 = 0 
            group by dates ) x
    on #tmp.dates = x.dates
   and #tmp.rnum <= x.rnum_x
group by #tmp.dates,
       case when rnum%3 = 0
                then rnum/3 - 1
       else rnum/3
       end

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270503

How about using arithmetic on rn?

select name, lastname, id, locationid, date, min(time)
from t
group by name, lastname, id, locationid,
         (rn - 1) / 3;

Or, with just a where:

select name, lastname, id, locationid, date, time
from t
where rn % 3 = 1;

Upvotes: 2

Related Questions