提取特定日期最后一小时的记录

发布于 2025-01-18 13:38:09 字数 596 浏览 0 评论 0原文

我正在尝试从销售表（ps_tkt_hist）中提取门票数（TKT_DN）的最后一个小时（TKT_DT）记录（bus_dat）。

我有以下代码，但它每小时提取门票数（TKT_NO）的数量。我只想过滤最后一个小时。这是我使用的代码：

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)

我得到的流动结果

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

我想在最后一个小时仅获得记录（4）（21）

原文

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)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我为君王 2025-01-25 13:38:09

如果您只想要特定日期最后一小时内的门票数量：

DECLARE @date date = '20220330';

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

对于最后一小时以外的任何时间（例如晚上 9 点）：

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

如果“最后一小时”不是指晚上 11 点，而是指 < em>最后一小时有促销，您必须执行以下操作：

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;

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;

回复收藏 0 原文

~没有更多了~