Extract the record for last hour for specific date-CodePudding

I am trying to extract the last hour (TKT_DT) record for number of tickets (TKT_DN) from sales table (PS_TKT_HIST) for specific date (BUS_DAT).

I have the following code but it extracts the number of tickets (TKT_NO) for each hour. I want to filter the last hour only. Here is the code I used:

Select count(TKT_NO) AS SAL_TKTS, 
  DATEPART(HOUR, (TKT_DT))AS SAL_HR
FROM PS_TKT_HIST
WHERE BUS_DAT = '2022-03-30'
GROUP By DATEPART (HOUR, TKT_DT)

I get the flowing results

SAL_TKTS    SAL_HR
5             10
1             11
3             12
5             13
10            14
13            15
23            16
18            17
12            18
6             19
6             20
4             21

I want to get only the record (4) for the last hour (21)

CodePudding user response：

If you just want the number of tickets in the last hour on a given day:

DECLARE @date date = '20220330';

SELECT COUNT(*)
  FROM dbo.PS_TKT_HIST
  WHERE BUS_DAT = @date
  AND TKT_DAT >= DATEADD(HOUR, 23, CONVERT(datetime2, @date));

For any hour other than the last hour (let's say, the 9PM hour):

WHERE BUS_DAT = @date
  AND TKT_DAT >= DATEADD(HOUR, 21, CONVERT(datetime2, @date))
  AND TKT_DAT <  DATEADD(HOUR, 22, CONVERT(datetime2, @date));

If by "last hour" you don't mean 11 PM but rather the last hour there was a sale, you would have to do something like this:

DECLARE @date date = '20220330';

SELECT TOP (1) COUNT(*)
  FROM dbo.PS_TKT_HIST
  WHERE BUS_DAT = @date
  GROUP BY DATEPART(HOUR, TKT_DAT)
  ORDER BY DATEPART(HOUR, TKT_DAT) DESC;