March 3, 2016 at 9:51 am
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.
Thank you for any help!
March 3, 2016 at 10:07 am
Robert,
could you post the create table scripts and insert statements for some sample (representative, not necessarily real) data? Then I think your question would be easier to understand and get you a working solution.
Thanks!
Pieter
March 3, 2016 at 10:28 am
robert693 (3/3/2016)
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.
Thank you for any help!
Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply