Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

subtract mutlple rows from top row value Expand / Collapse
Author
Message
Posted Friday, September 6, 2013 11:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 6, 2013 12:19 PM
Points: 8, Visits: 7
Here is my sample data: order number, location, stop type, stop datetime, column i made pup =1, drp =2.

1303927 Whouse1 PUP 4394903 2013-08-11 07:26:33.000 1
1303927 Store1 DRP 4394904 2013-08-11 08:31:46.000 2
1303927 Store2 DRP 4394907 2013-08-11 09:28:57.000 2
1303927 Store3 DRP 4395040 2013-08-11 10:38:53.000 2

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.
Post #1492347
Posted Friday, September 6, 2013 12:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 13,028, Visits: 11,838
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1492359
Posted Friday, September 6, 2013 12:25 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 551, Visits: 2,562
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',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 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



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1492364
Posted Friday, September 6, 2013 12:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 6, 2013 12:19 PM
Points: 8, Visits: 7
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.
Post #1492366
Posted Friday, September 6, 2013 12:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 13,028, Visits: 11,838
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1492375
Posted Friday, September 6, 2013 12:56 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 551, Visits: 2,562
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



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1492379
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse