Selecting 1 row per day closest to 4am?

Question

We're currently working on a query for a report that returns a series of data. The customer has specified that they want to receive 5 rows total, with the data from the previous 5 days (as defined by a start date and an end date variable). For each day, they want the data from the row that's closest to 4am.

I managed to get it to work for a single day, but I certainly don't want to union 5 separate select statements simply to fetch these values. Is there any way to accomplish this via CTEs?

select top 1 
    'W' as [RecordType]
,   [WellIdentifier]                    as [ProductionPtID]
,   t.Name                              as [Device Name]
,   t.RecordDate --convert(varchar, t.RecordDate, 112) as [RecordDate]
,   TubingPressure                      as [Tubing Pressure]
,   CasingPressure                      as [Casing Pressure]
from #tTempData t
Where cast (t.recorddate as time) = '04:00:00.000'
or datediff (hh,'04:00:00.000',cast (t.recorddate as time)) < -1.2
order by Name, RecordDate desc

@TabAlleman . . . I don't think that duplicate would help the OP answer this question. — Gordon Linoff, Mar 03 '16 at 14:54
Why not? It shows the technique of getting the top 1 row per group. OP wants top 1 row per Day (there's your group/partition) in order by Time, with a filter of after 4am. I know it would help me if I were him. — Tab Alleman, Mar 03 '16 at 15:00

score 5 · Accepted Answer · answered Mar 03 '16 at 14:41

assuming that the #tTempData only contains the previous 5 days records

SELECT *
FROM
(
    SELECT *, rn = row_number() over 
                   ( 
                      partition by convert(date, recorddate)
                      order by ABS ( datediff(minute, convert(time, recorddate) , '04:00' )
                   )
    FROM   #tTempData

)
WHERE rn = 1

score 3 · Answer 2 · answered Mar 03 '16 at 14:46

You can use row_number() like this to get the top 5 last days most closest to 04:00

SELECT TOP 5 * FROM (
    select t.* , 
           ROW_NUMBER() OVER(PARTITION BY t.recorddate
                        ORDER BY abs(datediff (minute,'04:00:00.000',cast (t.recorddate as time))) rnk
    from #tTempData t)
WHERE rnk = 1
ORDER BY recorddate DESC

score 2 · Answer 3 · answered Mar 03 '16 at 14:42

You can use row_number() for this purpose:

select t.*
from (select t.*,
             row_number() over (partition by cast(t.recorddate as date)
                                order by abs(datediff(ms, '04:00:00.000',
                                                      cast(t.recorddate as time)
                                                     ))
                               ) seqnum
      from #tTempData t
     ) t
where seqnum = 1;

You can add an appropriate where clause in the subquery to get the dates that you are interested in.

cosmos · Answer 4 · 2016-03-03T14:52:02.240

Try something like this:

select 
    'W' as [RecordType]
,   [WellIdentifier]                    as [ProductionPtID]
,   t.Name                              as [Device Name]
,   t.RecordDate --convert(varchar, t.RecordDate, 112) as [RecordDate]
,   TubingPressure                      as [Tubing Pressure]
,   CasingPressure                      as [Casing Pressure]
from #tTempData t
Where exists
(select 1 from #tTempData t1 where
    ABS(datediff (hh,'04:00:00.000',cast (t.recorddate as time))) < 
    ABS(datediff (hh,'04:00:00.000',cast (t1.recorddate as time)))
    and GETDATE(t.RecordDate) = GETDATE(t1.RecordDate)
)dt
and t.RecordDate between YOURDATERANGE
order by Name, RecordDate desc;

Selecting 1 row per day closest to 4am?

4 Answers4