英文:
SQL Server displaying certain rows with a case inside of a where
问题
以下是您要翻译的代码部分：
我有一个 SQL Server 查询，需要根据当前时间显示信息。我试图根据班次（白天和夜班）选择行。
我建了一个通用表，并对其进行别名为 DATA，然后我从 DATA 中选择，因为别名列的问题... 从那里，我有一个包含 CASE 的 WHERE 子句。该 Case 需要查看当前时间并获取当前小时，如果小时在白天班次中，则只显示白天班次的行。如果小时在夜班中，则只显示夜班次的行。
白天班次用 1 表示
夜班次用 0 表示
以下是查询，我遇到的主要问题在于第二个 SELECT...
WITH DATA as(
select vwDateShift.*
,DateHour.DateTime
,DateHour.DateTimeEnd
,DateHour.Hour
,DateHour.HH
,DateHour.[HH:MM]
,DATEDIFF(Hour,DateTime,GETDATE()) as HoursAgo
,CASE
WHEN hour <= 6 THEN 0
WHEN hour >= 7 And hour <=18 THEN 1
ELSE 0
END AS WhichShift
,Case
WHEN hour <= 6 Then ShiftNightPrev
WHEN hour >= 19 Then ShiftNight
ELSE ShiftDay
end AS ShiftHour
from PublicUtilities..vwDateShift
Left Join PublicUtilities..DateHour on DateHour.Date = vwDateShift.Date
)
SELECT *
FROM DATA
WHERE WhichShift = CASE
WHEN DATEPART(HOUR, GETDATE()) = 1 THEN WhichShift
WHEN DATEPART(HOUR, GETDATE()) = 2 THEN WhichShift
WHEN DATEPART(HOUR, GETDATE()) = 3 THEN WhichShift
...
END
CASE 中有 24 个 WHEN 情况，但为了节省空间，我将它们省略了，因为它们非常相似。
日期时间 HH 班次
----------------------- -- -----------
2000-01-06 04:00:00.000 04 0
2000-01-06 05:00:00.000 05 0
2000-01-06 06:00:00.000 06 0
2000-01-06 07:00:00.000 07 1
2000-01-06 08:00:00.000 08 1
2000-01-06 09:00:00.000 09 1
表格排除了许多行，因为我认为它们与问题无关，如果您需要查看它们，请提出要求。
截止目前，无论当前是哪个班次，每一行仍然都显示。我该如何修复此问题以显示当前班次？
英文:
I have a SQL Server query that needs to display information based on the time of day. I am trying to select rows based on shift (Day and Night shift).
I have a general table built aliased as DATA, and then I select from DATA because of issues with aliasing columns... From there I have a WHERE Clause with a CASE inside of it. That Case needs to look at the time of day and get the current hour, if that hour is in the day shift then I want to display only the day shift rows. If that hour is in the night shift then I want to display only the night shift rows.
Day shift is represented by 1
Night shift is represented by 0
Here is the query, the main issues I am having is with the second SELECT...
WITH DATA as(
select vwDateShift.*
,DateHour.DateTime
,DateHour.DateTimeEnd
,DateHour.Hour
,DateHour.HH
,DateHour.[HH:MM]
,DATEDIFF(Hour,DateTime,GETDATE()) as HoursAgo
,CASE
WHEN hour <= 6 THEN 0
WHEN hour >= 7 And hour <=18 THEN 1
ELSE 0
END AS WhichShift
,Case
WHEN hour <= 6 Then ShiftNightPrev
WHEN hour >= 19 Then ShiftNight
ELSE ShiftDay
end AS ShiftHour
from PublicUtilities..vwDateShift
Left Join PublicUtilities..DateHour on DateHour.Date = vwDateShift.Date
)
SELECT *
FROM DATA
WHERE WhichShift = CASE
WHEN DATEPART(HOUR, GETDATE()) = 1 THEN WhichShift
WHEN DATEPART(HOUR, GETDATE()) = 2 THEN WhichShift
WHEN DATEPART(HOUR, GETDATE()) = 3 THEN WhichShift
...
END
There are 24 WHEN cases inside of the CASE but to save room I left them out because they are very similar.
Date Time HH Which Shift
----------------------- -- -----------
2000-01-06 04:00:00.000 04 0
2000-01-06 05:00:00.000 05 0
2000-01-06 06:00:00.000 06 0
2000-01-06 07:00:00.000 07 1
2000-01-06 08:00:00.000 08 1
2000-01-06 09:00:00.000 09 1
The table excludes a lot of rows because I don't think they pertain to the question, just ask if you need to see them.
As of right now every row is still displayed no matter the what shift it currently is. How can I fix this to display on the current shift?
答案1
得分: 2
获取当前小时，如果该小时在白天班则只显示白天班的行。如果该小时在夜班则只显示夜班的行。
根据我理解的问题，你只需将用于计算列WhichShift的相同逻辑应用于当前日期即可。可以稍微简化case表达式如下：
with data as (...)
select *
from data
where whichShift = case when datepart(hour, getdate()) between 7 and 18 then 1 else 0 end
当当前小时在7点到18点之间时，case表达式返回1，这将筛选whichShift列中的白天班（夜班同理）。
英文:
> get the current hour, if that hour is in the day shift then I want to display only the day shift rows. If that hour is in the night shift then I want to display only the night shift rows.
As I understand the question, you just need to apply the same logic you used to compute column WhichShift to the current date. That would be (with a little simplification on your case expression):
with data as (...)
select *
from data
where whichShift = case when datepart(hour, getdate()) between 7 and 18 then 1 else 0 end
When the current hour of the day is between 7 and 18, the case expression returns 1, which filters on day shifts in column whichShift (and conversely for night shifts).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论