Post reply

Calculate number of days based on Computer System Date and a Due Date row!

  • March 18, 2014 at 1:22 am

    #283489

    Hi everyone,

    I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.

    Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.

    I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.

    Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.

    For example the output should be like: (Please see the image)

    Is this possible? Can someone help me how to write this Case Statement please? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQL...Thanks heaps...cheers...

  • March 18, 2014 at 2:31 am

    #1697893

    Select DueDate

    , case when DateDiff(day, Current_timestamp, DueDate) >= 45

    then 'YES' else NULL end as [45 Days Expectant]

    , case when DateDiff(day, Current_timestamp, DueDate) <= -30

    and DateDiff(day, Current_timestamp, DueDate) > -60

    then 'YES' else NULL end as [30 Days Overdue]

    , case when DateDiff(day, Current_timestamp, DueDate) <= -60

    and DateDiff(day, Current_timestamp, DueDate) > -90

    then 'YES' else NULL end as [60 Days Overdue]

    , case when DateDiff(day, Current_timestamp, DueDate) <= -90

    then 'YES' else NULL end as [90 Days Overdue]

    from tbl

    Russel Loski, MCSE Business Intelligence, Data Platform

  • March 18, 2014 at 8:10 pm

    #1698232

    Thanks man...worked like a charm....cheers... : )

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply