sql - 时间在周末和周末的情况
问题描述
我正在尝试在我的一个专栏上做一个案例,但时间必须在一周中的两次和周末的不同时间之间。因此,如果 sched_time 在一周内 (MF) 的 8:30:00 和 14:45:00 之间,则为“ABC”,否则为“DEF”。sched_time 列看起来像这样 2020-03-27 09:29:00。这是我到目前为止所拥有的:
SELECT Client_Last_Name,
Client_First_Name,
Sched_Time,
CASE WHEN Sched_time IS BETWEEN '08:30:00' AND '14:45:00'
THEN 'ABC'
ELSE 'DEF'
END
Field_Name,
Recoded_Response,
Dlsequence
FROM DAILY_LOG_CUSTOM_DATA
WHERE SERVICE_NAME = 'Medical'
AND FIELD_CATEGORY = 'Background Information'
AND Field_Name = 'Restraint?'
AND Recoded_Response = 1
And Sched_Time >= TRUNC(SYSDATE + 1, 'IW') - 8 AND
Sched_Time <= TRUNC(SYSDATE + 1, 'IW') - 1
解决方案
假设 SCHED_TIME 是类型,DATE
那么您可以利用 INTERVAL 算术:
SELECT Client_Last_Name,
Client_First_Name,
Sched_Time,
CASE
WHEN Sched_time BETWEEN TRUNC(SCHED_TIME) + INTERVAL '8' HOUR + INTERVAL '30' MINUTE
AND TRUNC(SCHED_TIME) + INTERVAL '14' HOUR + INTERVAL '45' MINUTE AND
TO_CHAR(SCHED_TIME, 'DY') IN ('MON', 'TUE', 'WED', 'THU', 'FRI')
THEN 'ABC'
ELSE 'DEF'
END
Field_Name,
Recoded_Response,
Dlsequence
FROM DAILY_LOG_CUSTOM_DATA
WHERE SERVICE_NAME = 'Medical'
AND FIELD_CATEGORY = 'Background Information'
AND Field_Name = 'Restraint?'
AND Recoded_Response = 1
AND Sched_Time >= TRUNC(SYSDATE + 1, 'IW') - 8 AND
Sched_Time <= TRUNC(SYSDATE + 1, 'IW') - 1
推荐阅读
- sql - How can select all columns except some columns in SQL Server 2017?
- python - Spyder not opening the webcam result window using opencv even code is running
- asp.net-core - ASPNET Core 3.x - Appending data to all API responses
- go - How to add quotes to gval expression
- windows - Error when trying to run Hadoop after a new install
- python - Why can not access the uwsgi server with ERR_EMPTY_RESPONSE response?
- c - Saving two different structures in binary files and read it
- python - Python grab all UDP Datagrams
- c# - Raise an Event Except when something happpen
- python - 如何阻止我的 Tkinter 窗口冻结?