Selecting last 24 hours plus the first 8 hours of current day

Question

I have a query that get's the last 24 hours or records and counters them and then groups the records by an ID.

What I would like is for it to select the past 24 hours plus the first 8 hours of the current day

SELECT controllerID,
       DATEPART (HOUR, dateadd(hour, datediff(hour, 0, dtReading), 0)) as TimeStampHour,
       Count(*) As Count
FROM [ReaderData]
WHERE dtReading >= dateadd(day,datediff(day,1,GETDATE()),0)
  AND dtReading < dateadd(day,datediff(day,0,GETDATE()),0)
  AND (EventType = '(0x03)Door state low')
  AND CardID = 'fffffff0'
GROUP BY controllerID, dateadd(hour, datediff(hour, 0, dtReading), 0)
ORDER BY controllerID, dateadd(hour, datediff(hour, 0, dtReading), 0);

Right now i can change the day that is being selected by changing the -1 and 0 in the where conditions to change the date. at present this query produces a result like this

    controllerID    TimeStampHour   Count
    13                  0           129
    13                  1           114
    13                  2           104
    13                  3           96
    13                  4           111
    13                  5           114
    13                  6           97
    13                  7           116
    13                  8           62
    13                  9           82
    13                  11          62
    13                  12          112
    13                  13          78
    13                  14          20
    13                  15          11
    13                  16          116
    13                  17          122
    13                  18          106
    13                  19          126
    13                  20          125
    13                  21          105
    13                  22          122
    13                  23          16
    28                  10          12
    28                  16          8
    30                  0           162
    30                  1           161
    30                  2           161
    30                  3           62
    30                  4           61
    30                  5           62

As you can see there is a count for each hour for the controller ID

What i would like is for the hours to extend 8 hours in to the next day for example after the 23rd hour for controllerID 13 the TimeStampHour would be 0 but that would be the next day after the days that have already been selected

for your reference http://stackoverflow.com/questions/1888544/how-to-select-records-from-last-24-hours-using-sql — Hisham, Jan 21 '16 at 14:02
I don't want the last 24 hours, i want a day + the first 8 hours of the following day The query i have already gets the records for the selected day just not the + first 8 hours of the following day — josh kirkpatrick, Jan 21 '16 at 14:04
Why isn't it as simple as adding 8 hours with DATEADD() to the upper datetime filter? It seems from your current script that you already know how to use DATEADD(). — Tab Alleman, Jan 21 '16 at 14:20

score 1 · Accepted Answer · answered Jan 21 '16 at 14:10

1

It seems you need to replace

dtReading >= dateadd(day,datediff(day,1,GETDATE()),0)
AND dtReading < dateadd(day,datediff(day,0,GETDATE()),0)

with

dtReading between dateadd(day,datediff(day,1,GETDATE()),0) AND dateadd(hour,8,dateadd(day,datediff(day,0,GETDATE()),0))

answered Jan 21 '16 at 14:10

Dzmitry Paliakou

1,587
19
27

Thanks that looks correct for what i want, just so i'm positive if i want to go back through the days i would change dateadd(day,datediff(day,1,GETDATE()),0) to dateadd(day,datediff(day,2,GETDATE()),0) And dateadd(hour,8,dateadd(day,datediff(day,0,GETDATE()),0)) to dateadd(hour,8,dateadd(day,datediff(day,1,GETDATE()),0)) And that would then take me back 1 more day correct? – josh kirkpatrick Jan 21 '16 at 14:17

score 0 · Answer 2 · answered Jan 21 '16 at 14:18

Try like this, It is almost similar to @Dmitry Polyakov

SELECT controllerID,
       Datepart (HOUR, Dateadd(hour, Datediff(hour, 0, dtReading), 0)) AS TimeStampHour,
       Count(*)                                                        AS Count
FROM   [ReaderData]
WHERE  dtReading >= Dateadd(day, Datediff(day, 1, Getdate()), 0)
       AND dtReading < Dateadd(hour, 8, Dateadd(day, Datediff(day, 0, Getdate()), 0))
       AND ( EventType = '(0x03)Door state low' )
       AND CardID = 'fffffff0'
GROUP  BY controllerID,
          Dateadd(hour, Datediff(hour, 0, dtReading), 0)
ORDER  BY controllerID,
          Dateadd(hour, Datediff(hour, 0, dtReading), 0);

Selecting last 24 hours plus the first 8 hours of current day

2 Answers2