Nested Case statement question

  • 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!

  • 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

  • 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