1

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
JD Davis
  • 3,269
  • 4
  • 21
  • 55
  • 1
    @TabAlleman . . . I don't think that duplicate would help the OP answer this question. – Gordon Linoff Mar 03 '16 at 14:54
  • 1
    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

4 Answers4

5

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
Squirrel
  • 21,215
  • 4
  • 34
  • 31
3

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
2

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.

Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
  • 4
    Is there a reason for the downvotes? – Gordon Linoff Mar 03 '16 at 14:43
  • Probably the other poster didn't like you stealing their thunder. Notice how every answer except one has 1 down-vote! lol – user692942 Mar 03 '16 at 14:48
  • What other people? everyone here got downvoted –  Mar 03 '16 at 14:49
  • @Yossi Has [Squirrel](http://stackoverflow.com/users/185647/squirrel)? – user692942 Mar 03 '16 at 14:49
  • Looks like they have now! lol – user692942 Mar 03 '16 at 14:49
  • 2
    Uncredited duplicate of answer available in the duplicate question? – Tab Alleman Mar 03 '16 at 14:50
  • 2
    It mostly probably because .. rest of the answer kind a look like dupe of the answer provided by @squirrel ... including this one too – Rahul Mar 03 '16 at 14:51
  • Actually mine isn't, and is the only one that selects the top 5! @Rahul and I already got 2 downvotes –  Mar 03 '16 at 14:52
  • 1
    @Yossi, it may not be exact duplicate but the core logic is same as what suggested by Squirrel and so it still qualifies as dupe. – Rahul Mar 03 '16 at 14:54
  • not me. I didn't down vote this post – Squirrel Mar 03 '16 at 14:54
  • Even if the core logic is the same(not to talk about the fact that I was in the middle of writing mine before I saw his) , his answer doesn't provide a perfect solution to the question! And mine does, so it shouldn't qualify as dupe. @Rahul –  Mar 03 '16 at 14:55
  • You can see from the timestamp, that Gordon and Squirrel answered at roughly the same time. But when a question is this easy to answer, there's almost always a duplicate that should be flagged in lieu of answering at all. – Tab Alleman Mar 03 '16 at 14:55
  • 1
    yes. Both of us post about the same time. Not fair to up one post and down the other just becuase it is similar. I will up-vote this ! – Squirrel Mar 03 '16 at 14:58
  • CTE's are like wizardry to me. But both solutions are fantastic, thanks for the help. – JD Davis Mar 03 '16 at 14:59
  • @Jdsfighter Again not a CTE this is a subquery. The give away is the lack of the `WITH` statement. – user692942 Mar 03 '16 at 15:11
  • I guess I've always associated the 'partition by' stuff as CTE. – JD Davis Mar 03 '16 at 15:53
1

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;
cosmos
  • 2,151
  • 1
  • 16
  • 27