locked
select query with a date between 2 dates RRS feed

  • Question

  • Good day,

    i have posted a question before with a select query and i forgot something.

    A certificate has a lifetime of 5 years and every year it has an annual survey this is a window of 90 days before and 90 days after every year  if in that time no annual has done the certificate expires if the annual is done in the window the certificate is okay and then we can go to the second annual, but in my posted query it goes through the first and when the date is okay it says okay in the end while it is in the window for the second annual.

    certificate issued 12-12-2013

    First annual 12-09-2014 till 12-03-2015

    Second annual 12-09-2015 till 12-03-2016

    third annual 12-09-2016 till 12-03-2017

    fourth annual 12-09-2017 till 12-03-2018

    Expiry 12-09-2018 till 12-12-2018the annual done is in my query datedone

    please see below my code

    ,CASE
            
            WHEN    (datediff (DD, Certificates.DueDate1, getdate())) > 90 OR
                    (datediff (DD, Certificates.Deudate2, getdate())) > 90 OR
                    (datediff (DD, Certificates.DueDate3, getdate())) > 90 OR
                    (datediff (DD, Certificates.DueDate4, getdate())) > 90  
            
            THEN 'Expired'

            WHEN    ((datediff (DD, Certificates.DueDate1, getdate())) >-90 AND (datediff (DD, Certificates.DueDate1, getdate())) < 90) OR
                    ((datediff (DD, Certificates.Deudate2, getdate())) >-90 AND (datediff (DD, Certificates.Deudate2, getdate())) < 90) OR
                    ((datediff (DD, Certificates.DueDate3, getdate())) >-90 AND (datediff (DD, Certificates.DueDate3, getdate())) < 90) OR
                    ((datediff (DD, Certificates.DueDate4, getdate())) >-90 AND (datediff (DD, Certificates.DueDate4, getdate())) < 90)

            THEN 'Window Open'

            WHEN    (DateDone1 > (DATEADD(DAY, -90, Certificates.DueDate1))) AND (DateDone1 < (DATEADD(DAY, 90, Certificates.DueDate1))) OR
                    (DateDone2 > (DATEADD(DAY, -90, Certificates.Deudate2))) AND (DateDone2 < (DATEADD(DAY, 90, Certificates.Deudate2))) OR
                    (DateDone3 > (DATEADD(DAY, -90, Certificates.DueDate3))) AND (DateDone3 < (DATEADD(DAY, 90, Certificates.DueDate3))) OR
                    (DateDone4 > (DATEADD(DAY, -90, Certificates.DueDate4))) AND (DateDone4 < (DATEADD(DAY, 90, Certificates.DueDate4)))
            THEN 'Okay'
            

            ELSE 'Okay'

            END as 'Status'

    Thanks in advance for the help

    Friday, September 29, 2017 5:09 PM

All replies

  • i was trying to edit my post but it has been reported as spam

    anyway the below block isnt working if i put the datedone then it gives status expires

    WHEN    (DateDone1 > (DATEADD(DAY, -90, Certificates.DueDate1))) AND (DateDone1 < (DATEADD(DAY, 90, Certificates.DueDate1))) OR
                    (DateDone2 > (DATEADD(DAY, -90, Certificates.Deudate2))) AND (DateDone2 < (DATEADD(DAY, 90, Certificates.Deudate2))) OR
                    (DateDone3 > (DATEADD(DAY, -90, Certificates.DueDate3))) AND (DateDone3 < (DATEADD(DAY, 90, Certificates.DueDate3))) OR
                    (DateDone4 > (DATEADD(DAY, -90, Certificates.DueDate4))) AND (DateDone4 < (DATEADD(DAY, 90, Certificates.DueDate4)))
            THEN 'Okay'
            
    

    Friday, September 29, 2017 5:59 PM
  • In first WHEN to check for expiry , you are using 4 different dates from consecutive years and comparing them with getdate() and has OR.

    You should be using AND as license would be considered as expired if current date falls outside 90 days of 4 possible dates ( per your design) .

    if you use OR it would always passes condition and give EXPIRED


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, September 29, 2017 6:24 PM
  • Hi,

    Could you please share us your table structure and some sample data with Select and Insert statements? So that we can make a test and find out the issue in your query.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 2, 2017 9:37 AM