subtract mutlple rows from top row value

Question

subtract mutlple rows from top row value

tdanley

SSC-Addicted

Points: 473
More actions
September 6, 2013 at 11:13 am

#277826

Here is my sample data: order number, location, stop type, stop datetime, column i made pup =1, drp =2.
1303927Whouse1 PUP43949032013-08-11 07:26:33.0001
1303927Store1DRP43949042013-08-11 08:31:46.0002
1303927Store2DRP43949072013-08-11 09:28:57.0002
1303927Store3DRP43950402013-08-11 10:38:53.0002
What I need to do is calculate the time between the PUP time, and each DRP time. I'm thinking a cursor but looking for suggestions on how to best accomplish this? Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Let's start with some ddl and actual sample data. The problem with just posting some characters is we have no idea what the datatypes are or where the column breaks are. If you instead post code to create a table and then fill that table with inserts there is no room for error. The other advantage is that you will have a lot more people willing to help.

I have no idea if this is right because I had to make some guesses.

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

OrderNum int,

Location varchar(10),

PUPDRP char(3),

SomeValueOfSomething int,

StopTime datetime,

ColumnIMadeUP tinyint

)

insert #Something

select 1303927, 'Whouse1', 'PUP', 4394903, '2013-08-11 07:26:33.000', 1 union all

select 1303927, 'Store1', 'DRP', 4394904, '2013-08-11 08:31:46.000', 2 union all

select 1303927, 'Store2', 'DRP', 4394907, '2013-08-11 09:28:57.000', 2 union all

select 1303927, 'Store3', 'DRP', 4395040, '2013-08-11 10:38:53.000', 2

select * from #Something

If that is not right then please modify this and post it the format that it should be.

If this is correct can you please explain what you mean "What I need to do is calculate the time between the PUP time, and each DRP time."

I'm thinking a cursor but looking for suggestions on how to best accomplish this? Thanks.

You absolutely 100% do NOT need a cursor for this. Cursors have their place but straight data manipulation is not it. They are just too slow.

_______________________________________________________________

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/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Based on what I think you may be looking for I came up with:

WITH ddl_next_time_please(order_number, location, stop_type, stop_datetime, col) AS

(SELECT 1303927,'Whouse1 PUP',4394903,'2013-08-11 07:26:33.000',1UNION ALL

SELECT 1303927,'Store1 DRP',4394904,'2013-08-11 08:31:46.000', 2UNION ALL

SELECT 1303927,'Store2 DRP',4394907,'2013-08-11 09:28:57.000', 2UNION ALL

SELECT 1303927,'Store3 DRP',4395040,'2013-08-11 10:38:53.000', 2)

SELECT d1.*, DATEDIFF(n,d2.stop_datetime,d1.stop_datetime) AS [pup_to_drp(minutes)]

FROM ddl_next_time_please d1

CROSS APPLY

(SELECT TOP 1 stop_datetime FROM ddl_next_time_please WHERE col=1) d2

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

tdanley SSC-Addicted Points: 473 More actions · Answer 3

my apologies for the lack of code in my original post. Newbie mistake I haven't made many posts and duly noted. However what you have is exactly right.

The first line has a PUPDRP type of "PUP". The stop time is the time the truck stopped the loading facility (PUP = Pick Up). The 3 remaining lines are designated DRP meaning "drop" so the times in the stop time there is the time that the truck stopped the drop location. I need to calculate how long it was between the time the truck loaded, and each stop.

Time between loading and stop 1 = 1 hrs

time between loading and stop 2 = 2 hrs

time between loading an stop 3 = 2 hrs

I know that every order has one pickup and potentially multiple drop offs.

Hope that helps clarify things.

Sean Lange (9/6/2013)
Let's start with some ddl and actual sample data. The problem with just posting some characters is we have no idea what the datatypes are or where the column breaks are. If you instead post code to create a table and then fill that table with inserts there is no room for error. The other advantage is that you will have a lot more people willing to help.
I have no idea if this is right because I had to make some guesses.
if OBJECT_ID('tempdb..#Something') is not null
drop table #Something
create table #Something
(
OrderNum int,
Location varchar(10),
PUPDRP char(3),
SomeValueOfSomething int,
StopTime datetime,
ColumnIMadeUP tinyint
)
insert #Something
select 1303927, 'Whouse1', 'PUP', 4394903, '2013-08-11 07:26:33.000', 1 union all
select 1303927, 'Store1', 'DRP', 4394904, '2013-08-11 08:31:46.000', 2 union all
select 1303927, 'Store2', 'DRP', 4394907, '2013-08-11 09:28:57.000', 2 union all
select 1303927, 'Store3', 'DRP', 4395040, '2013-08-11 10:38:53.000', 2
select * from #Something
If that is not right then please modify this and post it the format that it should be.
If this is correct can you please explain what you mean "What I need to do is calculate the time between the PUP time, and each DRP time."
I'm thinking a cursor but looking for suggestions on how to best accomplish this? Thanks.
You absolutely 100% do NOT need a cursor for this. Cursors have their place but straight data manipulation is not it. They are just too slow.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

tdanley (9/6/2013)
my apologies for the lack of code in my original post. Newbie mistake I haven't made many posts and duly noted. However what you have is exactly right.
The first line has a PUPDRP type of "PUP". The stop time is the time the truck stopped the loading facility (PUP = Pick Up). The 3 remaining lines are designated DRP meaning "drop" so the times in the stop time there is the time that the truck stopped the drop location. I need to calculate how long it was between the time the truck loaded, and each stop.
Time between loading and stop 1 = 1 hrs
time between loading and stop 2 = 2 hrs
time between loading an stop 3 = 2 hrs
I know that every order has one pickup and potentially multiple drop offs.
Hope that helps clarify things.

No problem. Everybody was new around here at one point in time. It is hard to know what to post for these types of questions.

That does help clarify. I had a feeling you wanted that but I have been bitten so many times by coding a solution to the wrong problem. 😛

Unless I am mistaken, the excellent code that Alan posted should do exactly that. Well except that no matter how I look at it it seems that stop #3 should be 3 hours. 7:26am - 10:38am

If his code does not produce the desired output post back and we will figure out what we can do to help.

_______________________________________________________________

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/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

I took what Sean put together and added a little more sample data (a second order number) and changed it up a little. If I am correct you need the difference between the PUP and DRP that relate to each order number. Let us know if this does is what you need.

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

OrderNum int,

Location varchar(10),

PUPDRP char(3),

SomeValueOfSomething int,

StopTime datetime,

col tinyint

)

insert #Something

select 1303927, 'Whouse1', 'PUP', 4394903, '2013-08-11 07:26:33.000', 1 union all

select 1303927, 'Store1', 'DRP', 4394904, '2013-08-11 08:31:46.000', 2 union all

select 1303927, 'Store2', 'DRP', 4394907, '2013-08-11 09:28:57.000', 2 union all

select 1303927, 'Store3', 'DRP', 4395040, '2013-08-11 10:38:53.000', 2 union all

select 1303928, 'Whouse1', 'PUP', 4394903, '2013-08-12 07:26:33.000', 1 union all

select 1303928, 'Store1', 'DRP', 4394904, '2013-08-12 09:31:46.000', 2 union all

select 1303928, 'Store2', 'DRP', 4394907, '2013-08-12 11:28:57.000', 2 union all

select 1303928, 'Store3', 'DRP', 4395040, '2013-08-12 12:38:53.000', 2

select d1.*, DATEDIFF(HOUR,d2.StopTime,d1.StopTime) AS TimeBetween

from #Something d1

JOIN (SELECT OrderNum, StopTime FROM #Something WHERE col=1) d2

ON d1.OrderNum=d2.OrderNum

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001