StartDate and EndDate in same column

Question

StartDate and EndDate in same column

Redmond01

SSC-Addicted

Points: 492
More actions
October 26, 2017 at 5:54 pm

#326024

I have a date column below RunDate , I want to create two column StartDate and EndDate like this StartDate = '2004-08-01' EndDate = '2006-08-01' second row StartDate = '2006-08-02' EndDate = '2007-02-15
how do I do this SQL missing row could be null or empty string

RunDate
8/1/2004
11/1/2004
8/1/2006
missing row
8/2/2006
2/15/2007
missing row
2/16/2007
2/17/2007
2/22/2007

Viewing 15 posts - 1 through 15 (of 26 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I see no rhyme or reason to how you are putting together your start and end dates based on the dates posted. Care to explain this a little better?

Redmond01 SSC-Addicted Points: 492 More actions · Answer 2

Well basically my Rundate column is in ascending order and I have to setstart and enddate range every time row is null or empty. so after null column will be start date and before null column is end date

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Redmond01 - Thursday, October 26, 2017 6:15 PM
Well basically my Rundate column is in ascending order and I have to setstart and enddate range every time row is null or empty. so after null column will be start date and before null column is end date

Your original post didn't match that description. According to what you wrote above, the second start date should be 2006-08-02 instead of 2004-08-02, correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Redmond01 - Thursday, October 26, 2017 6:15 PM
Well basically my Rundate column is in ascending order and I have to setstart and enddate range every time row is null or empty. so after null column will be start date and before null column is end date

What determines the order of the rows? (and don't say RunDate, because if it was, both of those missing rows would sort first)

Are you really using SQL Server 2017?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Redmond01 SSC-Addicted Points: 492 More actions · Answer 5

Correct Jeff Moden start date should be 2006-08-02 instead of 2004-08-02, hat was a type .

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Redmond01 - Friday, October 27, 2017 12:31 AM
Correct Jeff Moden start date should be 2006-08-02 instead of 2004-08-02, hat was a type .

Now you need to answer Gail's question.

sgmunson SSC Guru Points: 110639 More actions · Answer 7

I'm pretty I now see what the original poster is after. Apparently whenever ISNULL(RunDate, '') = '', he/she wants a pair of dates representing the range of dates encountered since the last such condition. However, as we have no expected output, and RunDate, assuming it's actually a date kind of data type, isn't going to be able to handle multiple date values in the same row, so we're forced to assume that a new pair of columns is needed. We also have to presume what values those columns should have for any row where the stated condition is NOT true. What say you, Redmond01?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

Since there's no way to give a proper answer without further information, I'll just leave this article here that explains this kind of problem.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Redmond01 - Friday, October 27, 2017 12:31 AM
Correct Jeff Moden start date should be 2006-08-02 instead of 2004-08-02, hat was a type .

Ok. Then, like Gail said, we need a different column that preserves the order of the rows as you have them posted because NULLs will bubble to the top in a sort and you cannot rely on any kind of sort based only on the order of insert.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99126 More actions · Answer 10

Redmond01 - Sunday, October 29, 2017 3:04 PM
@Lynn Pettis , why don't you keep you obnoxious ,sarcastic and dumb as rock remarks to yourself.

I see no where that Lynn has been any of these, and I feel confident in saying you have just lost any further advice you would have received on this topic, or possibly forum, with comments such as that.

There's no need to make derogatory remarks towards any one on these forums, especially when almost the entire community are volunteering their help and expertise completely free of charge and of their own time.

If nothing else, I would suggest an apology (to Lynn), if you'd like others to help you with getting the results you need from your data.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Redmond01 - Sunday, October 29, 2017 3:04 PM
@Lynn Pettis , why don't you keep you obnoxious ,sarcastic and dumb as rock remarks to yourself.

Excuse me?? I have made no obnoxious, sarcastic, dumb as rock remarks.
Good luck with this and any other issues you may have.

Redmond01 SSC-Addicted Points: 492 More actions · Answer 12

Redmond01 - Sunday, October 29, 2017 3:04 PM@Lynn Pettis , why don't you keep you obnoxious ,sarcastic and dumb as rock remarks to yourself.

Excuse me?? I have made no obnoxious, sarcastic, dumb as rock remarks.
Good luck with this and any other issues you may have.

Cool
Lynn Pettis

@Lynn Pettis are you threatening me , do you own this forum?

sgmunson SSC Guru Points: 110639 More actions · Answer 13

Redmond01 - Monday, October 30, 2017 1:58 AM
             Redmond01 - Sunday, October 29, 2017 3:04 PM@Lynn Pettis , why don't you keep you obnoxious ,sarcastic and dumb as rock remarks to yourself.
Excuse me?? I have made no obnoxious, sarcastic, dumb as rock remarks.
Good luck with this and any other issues you may have.

Lynn Pettis
@Lynn Pettis   are you threatening me , do you own this forum?

Are you some kind of bully? No one has threatened you... and all they've said is that it's unlikely anyone here will want to help someone who posts what amounts to some rather inflammatory statements that are neither accurate nor reasonable. That does not constitute a threat. They're just advising you that making such remarks is a good way to avoid getting any help at all. Most everyone posting on this forum is a volunteer, including me. The forum is owned by RedGate software, and frankly, I'm of the opinion your ability to post here may be in jeopardy with this kind of post clearly being a repeated problem, as it appears from the post history that your first such post was removed... I'll be reporting this post of yours, as it's clear you just don't understand how a forum such as this works.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Redmond01 - Monday, October 30, 2017 1:58 AM
             Redmond01 - Sunday, October 29, 2017 3:04 PM@Lynn Pettis , why don't you keep you obnoxious ,sarcastic and dumb as rock remarks to yourself.
Excuse me?? I have made no obnoxious, sarcastic, dumb as rock remarks.
Good luck with this and any other issues you may have.

Lynn Pettis
@Lynn Pettis   are you threatening me , do you own this forum?

Again, excuse me?? I am not threatening you in any way. I will tell you that I will not be helping you now or in the future after this latest outburst. That is my choice as a volunteer on this site.