Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Extract the record for last hour for specific date SQL Server

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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)

>Solution :

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));
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading