March 14, 2014 at 12:34 pm
Hello,
Let's say the first row returned has StartDate = 1/1/2014 and EndDate is 1/2/2014. The next row I want the StartDate to equal the previous row EndDate so it would be 1/2/2014 as StartDate. This compounds every row basically the third row StartDate would be the second row EndDate. All in one select statement if it can be done. Using SQL2008r2. Appreciate the help.
March 14, 2014 at 12:39 pm
JP10 (3/14/2014)
Hello,Let's say the first row returned has StartDate = 1/1/2014 and EndDate is 1/2/2014. The next row I want the StartDate to equal the previous row EndDate so it would be 1/2/2014 as StartDate. This compounds every row basically the third row StartDate would be the second row EndDate. All in one select statement if it can be done. Using SQL2008r2. Appreciate the help.
Sounds like a recursive cte may help here.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2014 at 1:00 pm
Ok thanks for the post.
here is the code to get sample data. So, my results I want the first row to be as is then second row (the ID 2 row) to show me ID 1 row's EndDate. The third row will then show me the EndDate of ID 2 row. Basically want the previous rows EndDate as the StartDate except for the first row. Appreciate the help.
--- drop table if exists
IF OBJECT_ID('tempdb..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1),
StartDate DATETIME,
EndDate DATETIME
)
-- insert data
INSERT INTO #mytable
Select '1/1/2014','1/2/2014'
INSERT INTO #mytable
Select '','1/5/2014'
INSERT INTO #mytable
Select '','1/7/2014'
--get sample data
Select * from #mytable
March 14, 2014 at 1:30 pm
Thanks for the ddl and sample data. This helps make this a LOT easier.
The first thing I did was to use a cte to allow me to number the rows. This is necessary because you might have gaps in your identity. Then I just did a self join.
with MyCTE as
(
select mt.ID, mt.StartDate, mt.EndDate, ROW_NUMBER() over (order by ID) as RowNum
from #mytable mt
)
select c1.ID, case when c2.ID is null then c1.StartDate else c2.EndDate end as StartDate,
c1.EndDate
from MyCTE c1
left join MyCTE c2 on c1.RowNum = c2.RowNum + 1;
Make sure you understand what this is doing before you load this into your system.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2014 at 1:46 pm
Np thank you! I will review it. Appreciate it.
March 14, 2014 at 2:32 pm
Ok here is my other issue. Below is the sample data. What if the StartDates and EndDates were not stored in the table and were derived originally from getdate() function and based on the AddDays column we add that number of days to the StartDate (which starts off at current day and time) to get the first row EndDate. Then from that point on we start using the previous EndDate as the next StartDate?
Do we need to update the table with a separate script to calculate the correct StartDate and EndDate if so any ideas how to update the StartDate and EndDate correctly?
Appreciate the help.
--- drop table if exists
IF OBJECT_ID('tempdb..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1),
StartDate DATETIME,
EndDate DATETIME,
AddDays int
)
-- insert data
INSERT INTO #mytable
Select null,null,1
INSERT INTO #mytable
Select null,null, 0
INSERT INTO #mytable
Select null,null,2
--get sample data
Select AddDays,getdate() as StartDate, getdate() + AddDays as EndDate from #mytable
March 14, 2014 at 2:40 pm
JP10 (3/14/2014)
Ok here is my other issue. Below is the sample data. What if the StartDates and EndDates were not stored in the table and were derived originally from getdate() function and based on the AddDays column we add that number of days to the StartDate (which starts off at current day and time) to get the first row EndDate. Then from that point on we start using the previous EndDate as the next StartDate?Do we need to update the table with a separate script to calculate the correct StartDate and EndDate if so any ideas how to update the StartDate and EndDate correctly?
Appreciate the help.
--- drop table if exists
IF OBJECT_ID('tempdb..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1),
StartDate DATETIME,
EndDate DATETIME,
AddDays int
)
-- insert data
INSERT INTO #mytable
Select null,null,1
INSERT INTO #mytable
Select null,null, 0
INSERT INTO #mytable
Select null,null,2
--get sample data
Select AddDays,getdate() as StartDate, getdate() + AddDays as EndDate from #mytable
Are there any other business rules you haven't mentioned yet? This changes the query somewhat but not hugely. Give it a crack and see if you can figure it out.
FWIW, I would recommend not using getdate() + SomeInt for adding days. You should use DATEADD(DAY, getdate(), AddDays).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2014 at 2:50 pm
I have been for past few hours. =) I just added + 1 for speed purposes. Appreciate the help.
March 14, 2014 at 2:54 pm
JP10 (3/14/2014)
I have been for past few hours. =) I just added + 1 for speed purposes. Appreciate the help.
So you want the startdate for the first row to be getdate() and the remaining rows will add whatever it has for AddDays? I will see if I can modify the query I posted earlier.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2014 at 3:02 pm
Yes, so when you run the select from my latest code, the second row StartDate should be the first row EndDate, then the EndDate for second row would add the AddDays to it's StartDate (which in this case is 0), then the third row StartDate will be the second row EndDate and the EndDate for row 3 will be it's StartDate plus the AddDays. So on and so on. Really appreciate the help.
March 17, 2014 at 2:15 pm
Hello Sean,
I got what I needed I just dumped the data set into a table variable then ran a cursor to update the dates manually getting the previous rows enddate. The key here was using a -1 in the where clause and defining another column named RowNum by using ROW_NUMBER() over statement. Appreciate the help.
March 17, 2014 at 2:22 pm
Hello Sean,
Thanks for the help really appreciate it. Looks like I solved my problem by using a cursor and updating the StartDates manually and then selecting that table with the updates. Key here was using the - 1 in the where clause of RowNum column I created by using Row_num() over statement. If there is a way to do this with one select statement or cte please do share.
March 17, 2014 at 2:52 pm
JP10 (3/17/2014)
Hello Sean,Thanks for the help really appreciate it. Looks like I solved my problem by using a cursor and updating the StartDates manually and then selecting that table with the updates. Key here was using the - 1 in the where clause of RowNum column I created by using Row_num() over statement. If there is a way to do this with one select statement or cte please do share.
UGH!!! Using a cursor for this is not a good solution. Cursors are horrible for performance. The original post I send is most of the solution. Then you added the next level of complexity. All you really need is a running total for how many days to add to getdate() for each row. You could do this most likely do this using the quirky update but there are some caveats that are very important. Take a look at this article which explains the quirky update in detail. http://www.sqlservercentral.com/articles/68467/[/url]. You should be able to combine this logic with the logic I used in my first post to solve this with no cursors.
I am busy right now with my job and will be out the second half of the week. If nobody has come along I will look this thread up next week.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2014 at 5:28 am
I believe that this article might be relevant to your question:
Creating a Date Range from Multiple Rows Based on a Single Date[/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 18, 2014 at 7:18 am
dwain.c (3/18/2014)
I believe that this article might be relevant to your question:Creating a Date Range from Multiple Rows Based on a Single Date[/url]
As soon as I saw the title of this article in my email today I thought of this thread. Well done yet again Dwain!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply