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

Question

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

jbalbo

SSCertifiable

Points: 7583
More actions
December 4, 2023 at 7:29 pm

#4339702

Hi
I need to insert a new line of my output when a condition is met
My out looks like
ID,Date
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
1,'10/1/23'
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
1,'10/1/23'
2 '10/1/23'
3 '10/3/23'
3 '10/2/23'
4 '10/1/23'
Thank you....

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

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>
UNION
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.

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

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 ...lol 🙂

Thank You