Case statement to insert a new line in query output??

  • Hi

    I need to insert a new line of my output when a condition is met

    My out looks like


    When The date is between say '10/2/23' and '10/30/23' i want to insert a new line in the output with the date -1 day

    for example

    my output looks like


    2 '10/1/23'

    3 '10/3/23'

    4 '10/1/23'

    If the example above I would want to insert a new line for ID=3 of '10/2/23' so the output looks like


    2 '10/1/23'

    3 '10/3/23'

    3 '10/2/23'

    4 '10/1/23'

    Thank you....





  • This looks like a case for a UNION. You do your main SELECT then UNION a SELECT of the ID and the date minus 1 day. There is probably another way to do it (there is always more than 1 way to do something in SQL... I imagine a CTE could do it), but I like to keep things easy and this sounds like you are basically wanting to do 2 selects - first of the raw data and second a filtered result set with the date prior but same ID.

    At least that's how I'd approach it. So along the lines of:

    SELECT ID, Date
    FROM <table>
    SELECT ID, DATEADD(day,1,Date)
    FROM <table>
    WHERE Date >= CAST('10/2/2023' AS DATE)
    AND DATE <= CAST('10/30/2023' AS DATE)

    Depending on performance considerations or if this is part of a stored procedure, I may move those dates to variables rather than hard-coded in the query. But for a one-off run, I'd use something similar to the above...

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Hi Brian,

    I think is the exact logic, I'm looking for.

    I do have some more if,and or but's, but let me play around with it.

    I do reserve the right to ask more.. just in case 🙂


    Thank You

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

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