Nested Case question

1

Hello,

I have a problem where if a treatment is given between 07:00 and 09:00 am or between 19:00 and 21:00 pm then a protocol must be done between those two time frames as well. If no treatment is given then no protocol needs to be done.
I have two fields, Treatment_Time and Protocol_Time, (these are both Datetime fields) as well as two fields with data entered for Treatment and Protocol. I want to create a field that is populated by the following:
• between 07:00 and 09:00 am, if the Treatment field is populated and the Protocol field is populated within that time frame as well then the field = 1
• between 07:00 and 09:00 am, if the Treatment field is populated and the Protocol field is not populated within that time frame then the field = 0
• between 19:00 and 21:00 pm, if the Treatment field is populated and the Protocol field is populated within that time frame as well then the field = 1
• between 19:00 and 21:00 pm, if the Treatment field is populated and the Protocol field is not populated within that time frame then the field = 0

There can be multiple treatments within each time frame and multiple Protocols in each. If a treatment is done in a time frame then a protocol must be done. I do not want to pull in records where the Treatment field is null.
Thak you for any help!

2

You can query like shown below. Use the result of the query to update the column if you need to store the value rather than query for it as required

SELECT
	CASE 
		WHEN CAST(Treatment_time AS TIME) BETWEEN '07:00' AND '09:00'
			AND CAST(Protocol_time AS TIME) BETWEEN '07:00' AND '09:00' THEN 1
		WHEN CAST(Treatment_time AS TIME) BETWEEN '07:00' AND '09:00'
			AND Protocol_time NOT BETWEEN  '07:00' AND '09:00' THEN 0
		WHEN CAST(Treatment_time AS TIME) BETWEEN '19:00' AND '21:00'
			AND CAST(Protocol_time AS TIME) BETWEEN '19:00' AND '21:00' THEN 1
		WHEN CAST(Treatment_time AS TIME) BETWEEN '19:00' AND '21:00'
			AND Protocol_time NOT BETWEEN  '19:00' AND '21:00' THEN 0
	END,
	AnyOtherColumnsYouNeed
FROM
	YourTable