Query help. Previous using previous row

Question

Post reply

Query help. Previous using previous row

johnnycougar

SSC Enthusiast

Points: 103
More actions
February 25, 2015 at 9:44 am

#295565

/* Can't edit title. Meant to say "Set current row using values in previous row' */
Hello,
I've tried all sorts of code i.e. cross apply, running totals, etc. Cannot get this to work. I am trying to add a previous row value but only doing it for each group.
.
Source records
DECLARE @tbl table (Item int, Sequence int, StartTime datetime, Duration int)
INSERT INTO @tbl (Item,Sequence,StartTime, Duration) VALUES (1,1,'2/25/2015 12:00 am',10),(1,2,null,20),(1,3, null,22),(2,1,'2/25/2015 1:00 am',15),(2,2,null,30),(2,3, null,45),(2,4, null,5)
select * from @tbl
ItemSequenceStartTimeDuration
1102/25/15 0:0010
12null 20
13null 22
2102/25/15 1:0015
22null 30
23null 45
2 4 null 5
.
I would like to set the start time of the next row to be equal to the previous row time + duration. I know the start time of each group of 'Items' when the 'Sequence' number = 1. The last 'duration' value in the group would be ignored. My expected output would be:
.
ItemSequenceStartTimeDuration
1102/25/15 0:0010
1202/25/15 0:1020
1302/25/15 0:3022
2102/25/15 1:0015
2202/25/15 1:1530
2302/25/15 1:4545
2402/25/15 2:305
.
Thank you all for your help.
-John

Viewing 8 posts - 1 through 7 (of 7 total)

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Are you using SQL Server 2012? If so you can do this

select Item,

Sequence,

StartTime,

Duration,

dateadd(minute,

sum(Duration) over(partition by Item order by Sequence) - Duration,

first_value(StartTime) over(partition by Item order by Sequence)) AS NewStartTime

from @tbl

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

johnnycougar SSC Enthusiast Points: 103 More actions · Answer 2

johnnycougar

SSC Enthusiast

Points: 103

February 25, 2015 at 10:37 am

#1780000

Hello. Thank you for the reply.

.

We are using 2008

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

I suspect you'll need to use some sort of running total on the Duration column. Search for "quirky update" for a very fast method, there's plenty of good folks out there that can point you in the right direction.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 4

johnnycougar (2/25/2015)
/* Can't edit title. Meant to say "Set current row using values in previous row' */
Hello,
I've tried all sorts of code i.e. cross apply, running totals, etc. Cannot get this to work. I am trying to add a previous row value but only doing it for each group.
.
Source records
DECLARE @tbl table (Item int, Sequence int, StartTime datetime, Duration int)
INSERT INTO @tbl (Item,Sequence,StartTime, Duration) VALUES (1,1,'2/25/2015 12:00 am',10),(1,2,null,20),(1,3, null,22),(2,1,'2/25/2015 1:00 am',15),(2,2,null,30),(2,3, null,45),(2,4, null,5)
select * from @tbl
ItemSequenceStartTimeDuration
1102/25/15 0:0010
12null 20
13null 22
2102/25/15 1:0015
22null 30
23null 45
2 4 null 5
.
I would like to set the start time of the next row to be equal to the previous row time + duration. I know the start time of each group of 'Items' when the 'Sequence' number = 1. The last 'duration' value in the group would be ignored. My expected output would be:
.
ItemSequenceStartTimeDuration
1102/25/15 0:0010
1202/25/15 0:1020
1302/25/15 0:3022
2102/25/15 1:0015
2202/25/15 1:1530
2302/25/15 1:4545
2402/25/15 2:305
.
Thank you all for your help.
-John

Is the goal to eventually update a permanent table? If so, please post the CREATE TABLE statement of the real permanent table along with all constraints and indexes so that don't run into any nasty surprises when trying to solve this problem. Thanks.

--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)

johnnycougar SSC Enthusiast Points: 103 More actions · Answer 5

The goal is to eventually update a permanent table. If I could get some assistance to figure out how to update this temporary table, I can handle the rest downstream.

.

The requirement is to update the current rows datetime field by adding the previous rows 'duration'. The first row contains the start time. The datetime column in subsequent rows (where sequence > 1 would be calculated. This is where I believe it's a running total.

.

Thank you for your assistance and patience.

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 6

johnnycougar (2/25/2015)
If I could get some assistance to figure out how to update this temporary table, I can handle the rest downstream.

Not entirely true for what you need in 2008. It's not a difficult thing to do but we do need be very careful with how your temp table gets populated or how your permanent table gets updated. I need the information I asked for to help you do it right without blowing up the data in the process.

--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)

Dwain Camps SSC Guru Points: 86968 More actions · Answer 7

To me this looks like a combination of a "data smear" and a running totals problem.

Perhaps you'll find some ideas here:

Filling In Missing Values Using the T-SQL Window Frame[/url]

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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