October 16, 2009 at 10:11 am
I'm a bit of a newbie and have been tasked with identifying some information which I know will require some complex querying...probably beyond my current skillset, so I'm wondering if I might get some assistance.
I'm filling a temp table with a set of records that are ordered by Route and EventOrder. From that temp table, I'm pulling a specific subset of records/Events from certain Routes (if they meet the Where clause specs). Even though I can identify the records/Events that I want from the temp table, I'll actually need to get the one record before and the one record after my subset of records. I think I need to use Rowcount (and maybe Rowcount +1, Rowcount -1?) in some fashion to get this, but not quite sure how I'd go about it.
(My code is a bit ugly and quite rudimentary so I'm not sure if it's worth including or not.....?)
Anyone out there have any ideas for me?
Thanks!
October 16, 2009 at 10:34 am
Definitely worth including. No need to be embarrassed that your code isn't perfect; we all start somewhere. We may need sample data as well (see the article in my signature for how to supply this).
October 16, 2009 at 10:56 am
Ok, here goes... let me know if i need to clarify. (and thank you for your help!)
------To find actual mileage based on odometers:
----FILL TEMP TABLE WITH ALL RECORDS FROM ALL ROTUES THAT HAVE P OR D AT THE S LANE ST SITE
SELECT ROUTE
, ACTIVITY
, activityvalue
, BOOKINGID
, EVORDER
, CLIENTNAME
, [ADDRESS]
, DISTANCE
, TRAVELTIME
, DEADHEAD_MILES
, DEADHEAD_TIME
, ODOMETER
INTO #temp
FROM VIEW_MANIFEST
WHERE LDate = 20090914
AND SchedStatusValue IN (3, 21)
AND CCSecGroup = 'ACCESS'
AND ROUTE IN
(SELECT ROUTE
FROM view_manifest
WHERE LDate = 20090914
AND Route NOT IN('9999')
AND bookingid IN
(SELECT bookingid
FROM View_Manifest
WHERE Address = '803 S LANE ST'
AND LDATE = 20090914))
ORDER by ROUTE,EVORDER
-----View the results of the temp table, to validate the odometers that are pulled from it below
select *
from #temp
-----FINDS THE MIN AND MAX ODOMETER and DH OF THE TRIPS DROPPING AT S LANE ST---------
SELECT [ROUTE]
, MIN(ODOMETER)MINPickOdom
, MAX(DEADHEAD_MILES)DHMiles
, MAX(DEADHEAD_TIME) DHTime
FROM #temp
WHERE activity = 'PICK'
AND bookingid IN
(SELECT bookingid
FROM dbo.View_Manifest
WHERE LDATE = 20090914
AND activity = 'DROP'
AND address = '803 S LANE ST')
GROUP BY ROUTE
SELECT [ROUTE]
, MAX(ODOMETER)MAXPickOdom
FROM #temp
WHERE activity = 'DROP'
AND bookingid in
(SELECT bookingid
FROM dbo.View_Manifest
WHERE LDATE = 20090914
AND activity = 'DROP'
AND address = '803 S LANE ST')
GROUP BY ROUTE
drop table #temp
October 16, 2009 at 10:58 am
Oh! and I read your post on the sample data....but I'm not quite sure how to go about doing that for this exercise. I wouldn't know where to begin. (eek! I'm sorry)
October 16, 2009 at 12:15 pm
It's ok, we'll start with this. I'm not quite sure what values you need to pull, but I'm throwing this out there to see if it would make it easier. If I knew how your data was organized, we could possibly re-organize this query to use joins rather than nested IN clauses. If you're not familiar with JOIN's yet, you're definitely going to want to read up on them. Basically all I'm doing here is pre-defining the temp table rather than using SELECT INTO so that I can specify an identity value. These will be numeric integers in the order of your data. This may allow you to get what you need out of this table. Sorry to be so vague, but I'm not sure what you can't get.
Also, I defined the table as all varchar(20)'s because I don't know what your datatypes are. You'll likely want to change these to match your actual data types. This only replaces the first part.
October 16, 2009 at 12:51 pm
I'm open to hearing whatever you got.
As far as the temp table goes, I technically could've used joins but it was much simpler for me to use the view which took care of that for me and included a few calculated columns that I needed to boot. I would have to join about 10 tables if I did it manually.
I don't know if it helps any, but to make it simpler (i think), the data in the temp table are just all the events that were performed on a particular route. Technically all i need is the odom, the DH miles and DH time. The events are put into proper order by EvOrder. Ultimately, I'm trying to isolate some specific trips within that route which went to a specific address, find the odometer of the first pick, the odometer of the last drop in that group, and get a value (i haven't gotten to that part yet). The hard part is that in addition to getting the start/end odom of that group of trips, i need to get the odom of the trip before the start and the odom of the trip after the end so i can do some further calculations on deadhead travel to/from that group.
*whew.
I don't know if I clarified or complicated matters further for you. For simplicity, data types can be int.
Does that help any?
October 16, 2009 at 1:21 pm
Wow, that's terrible. I guess I'm a bit distracted today, I forgot to actually post the code at the end! :blush:.
Here is what I was alluding to in my first post.
------To find actual mileage based on odometers:
----FILL TEMP TABLE WITH ALL RECORDS FROM ALL ROTUES THAT HAVE P OR D AT THE S LANE ST SITE
CREATE TABLE #Temp(
TempIDint IDENTITY(1,1),
ROUTEvarchar(20)
, Activityvarchar(20)
, activityvaluevarchar(20)
, BOOKINGIDvarchar(20)
, EVORDERvarchar(20)
, CLIENTNAMEvarchar(20)
, [ADDRESS]varchar(20)
, DISTANCEvarchar(20)
, TRAVELTIMEvarchar(20)
, DEADHEAD_MILESvarchar(20)
, DEADHEAD_TIMEvarchar(20)
, ODOMETERvarchar(20)
)
INSERT INTO #Temp( ROUTE, ACTIVITY, activityvalue, BOOKINGID, EVORDER, CLIENTNAME, [ADDRESS], DISTANCE, TRAVELTIME,
DEADHEAD_MILES, DEADHEAD_TIME, ODOMETER)
SELECT ROUTE
, ACTIVITY
, activityvalue
, BOOKINGID
, EVORDER
, CLIENTNAME
, [ADDRESS]
, DISTANCE
, TRAVELTIME
, DEADHEAD_MILES
, DEADHEAD_TIME
, ODOMETER
FROM VIEW_MANIFEST V1
WHERE LDate = 20090914
AND SchedStatusValue IN (3, 21)
AND CCSecGroup = 'ACCESS'
AND ROUTE IN
(SELECT ROUTE
FROM view_manifest
WHERE LDate = 20090914
AND Route NOT IN('9999')
AND bookingid IN
(SELECT bookingid
FROM View_Manifest
WHERE Address = '803 S LANE ST'
AND LDATE = 20090914))
ORDER by ROUTE,EVORDER
-----View the results of the temp table, to validate the odometers that are pulled from it below
select *
from #temp
October 16, 2009 at 1:44 pm
tacy.highland (10/16/2009)
I'm open to hearing whatever you got.As far as the temp table goes, I technically could've used joins but it was much simpler for me to use the view which took care of that for me and included a few calculated columns that I needed to boot. I would have to join about 10 tables if I did it manually.
The view is fine, I was referring to something like
FROM VIEW_MANIFEST V1
INNER JOIN VIEW_MANIFEST V2 ON V1.Route ...
Alternatively, you could join the view back to a couple of the original tables since you don't necessarily need all the additional tables in the view for the IN's, etc.
I don't know if it helps any, but to make it simpler (i think), the data in the temp table are just all the events that were performed on a particular route. Technically all i need is the odom, the DH miles and DH time. The events are put into proper order by EvOrder. Ultimately, I'm trying to isolate some specific trips within that route which went to a specific address, find the odometer of the first pick, the odometer of the last drop in that group, and get a value (i haven't gotten to that part yet). The hard part is that in addition to getting the start/end odom of that group of trips, i need to get the odom of the trip before the start and the odom of the trip after the end so i can do some further calculations on deadhead travel to/from that group.
*whew.
I don't know if I clarified or complicated matters further for you. For simplicity, data types can be int.
Does that help any?
I get the gist of what you're trying to do, but as is usually the case in these situations, the devil is in the details. Without sample data, it will be somewhat difficult to give you an exact answer. I don't know the relationship of the data which makes it difficult to know what I can group by etc. For example, Is there 1 bookingID per route? You should be able to consolidate the bottom 2 queries into this though, which gives you an example of what I mean about joins.
SELECT T.[ROUTE]
, MIN(T1.ODOMETER)MINPickOdom
, MAX(T1.DEADHEAD_MILES)DHMiles
, MAX(T1.DEADHEAD_TIME) DHTime
, MAX(T2.ODOMETER)MAXPickOdom
FROM #temp T
LEFT JOIN #temp T1 ON T.TempID = T1.TempID AND T1.Activity = 'PICK'
LEFT JOIN #temp T2 ON T.TempID = T2.TempID AND T2.Activity = 'DROP'
WHERE T.LDATE = 20090914
AND T.[address] = '803 S LANE ST'
GROUP BY ROUTE
October 16, 2009 at 3:25 pm
You are awesome. This is good stuff!
Ok, let's see if I can break this down...
For each route, there are multiple Activities (pick or drop). For every pick there is a corresponding drop.
A BookingID occurs twice on a route: once for each pick and once for each drop. (it's an ID for the entire leg- pickup and dropoff).
Evorder is a unique ID for all the events and used to put events in the correct order on routes.
Clientname occurs multiple times on a route, at least twice if they just have one trip on the route, four times if they also have a return ride on the same route.
DeadheadMiles/Time are int and may be populated for each event. They are calculated in the view.
Odometer, like Evorder, is chronological through each route, also an int.
I cut down the number of columns I'm pulling into the temp table to just this:
ROUTE
, LDATE
, ACTIVITY
, BOOKINGID
, EVORDER
, CLIENTNAME
, [ADDRESS]
, DEADHEAD_MILES
, DEADHEAD_TIME
, ODOMETER
I tried running your scripts and found that the min/max odometers weren't populating in many cases, so I'm going to have to check that out further....
October 16, 2009 at 3:47 pm
I think I figured out why the Odometers are not populating quite right...
My code separates out by Activity in two different cases for two different odoms: (note in the first query its pulling the first odom for Pick events with a drop address of that site, and the second query is pulling the last odom for all drops with the drop address of that site. ...Did that make sense?)
SELECT [ROUTE]
, MIN(ODOMETER)MINPickOdom
, MAX(DEADHEAD_MILES)DHMiles
, MAX(DEADHEAD_TIME) DHTime
FROM #temp
WHERE activity = 'PICK'
and bookingid in
(select bookingid
from dbo.View_Manifest
WHERE LDATE = 20090914
and activity = 'DROP'
and address = '803 S LANE ST')
GROUP BY ROUTE
SELECT [ROUTE]
, MAX(ODOMETER)MAXPickOdom
FROM #temp
WHERE activity = 'DROP'
and bookingid in
(select bookingid
from dbo.View_Manifest
WHERE LDATE = 20090914
and activity = 'DROP'
and address = '803 S LANE ST')
GROUP BY ROUTE
Your code consolidates the Activities, so it can't differentiate between the address on the pick and the address on the drop, so it can't pull the right odom:
SELECT T.[ROUTE]
, MIN(T1.ODOMETER)MINPickOdom
, MAX(T2.ODOMETER)MAXPickOdom
, MAX(T1.DEADHEAD_MILES)DHMiles
, MAX(T1.DEADHEAD_TIME) DHTime
FROM #temp T
LEFT JOIN #temp T1 ON T.TempID = T1.TempID AND T1.Activity = 'PICK'
LEFT JOIN #temp T2 ON T.TempID = T2.TempID AND T2.Activity = 'DROP'
WHERE T.LDATE = 20090914
AND T.[address] = '803 S LANE ST'
GROUP BY T.[ROUTE]
I do like your more consolidated approach better though as it would make it easier to perform the subtraction of the min odom from the max odom to get total miles....
October 16, 2009 at 8:40 pm
Man, I'm dropping the ball all over this thread! I made this post about 5 hours ago and clicked preview instead of post. Oops.
tacy.highland (10/16/2009)
I think I figured out why the Odometers are not populating quite right...My code separates out by Activity in two different cases for two different odoms: (note in the first query its pulling the first odom for Pick events with a drop address of that site, and the second query is pulling the last odom for all drops with the drop address of that site. ...Did that make sense?)
Nope. I'm left joining based on activity. Only rows for picks will be in T1, only drops will be in T2. The addresses are the same in both queries, as are the LDates. The difference here that I see is you're looking for the max pick that had a drop and I'm just looking for the max pick period. Assuming that all picks had drops, they should return the same results. (Unless I'm misunderstanding the data again).
Your code consolidates the Activities, so it can't differentiate between the address on the pick and the address on the drop, so it can't pull the right odom:
The addresses are the same in both queries.
This would really work immensely better if we had sample data. Is it possible to grab a subset of the rows that populate the temp table(I don't need the original data from the 10 tables the view consolidates, the data in the temp table is probably good enough) you're creating and provide those in the way that article in my sig mentions? If there aren't many rows in the temp table you could supply all of them, but if it has a lot, the subset should include at least enough rows to cover all of the fields you want to grab as well as a few others to be a realistic sample.
I may not be able to respond for a while, but there are a lot of people here that can help if you provide sample data. Most of them won't just guess though :-).
October 16, 2009 at 11:07 pm
I'm hoping I followed directions correctly...
I've posted some sample data, just for 2 routes, from the temp table: (Note, I excluded clientname, for obvious reasons)
--Create the temp table:
CREATE TABLE #Temp(
TempID int IDENTITY(1,1)
, LDATEINT
, [ROUTE] varchar(20)
, ACTIVITY varchar(15)
, BOOKINGID INT
, EVORDER INT
, [ADDRESS] varchar(100)
, DEADHEAD_MILES INT
, DEADHEAD_TIME INT
, ODOMETER INT
)
SET IDENTITY_INSERT #temp ON
INSERT INTO #Temp( LDATE
, [ROUTE]
, ACTIVITY
, BOOKINGID
, EVORDER
, [ADDRESS]
, DEADHEAD_MILES
, DEADHEAD_TIME
, ODOMETER)
SELECT '20090915','336','Pick','10902327','30819604','32711 17TH AVE SW','11','32','52162' UNION ALL
SELECT '20090915','336','Pick','10927709','34523312','32505 110TH AVE SE','0','0','52180' UNION ALL
SELECT '20090915','336','Drop','10927709','38094725','12424 42ND AVE S','0','0','52198' UNION ALL
SELECT '20090915','336','Drop','10902327','60491140','12424 42ND AVE S','0','0','52198' UNION ALL
SELECT '20090915','336','Pick','10902257','64838983','12424 42ND AVE S','0','0','52198' UNION ALL
SELECT '20090915','336','Pick','10902329','77172274','12424 42ND AVE S','0','0','52198' UNION ALL
SELECT '20090915','336','Drop','10902329','89550365','803 S LANE ST','0','0','52209' UNION ALL
SELECT '20090915','336','Drop','10902257','91106859','803 S LANE ST','0','0','52209' UNION ALL
SELECT '20090915','336','Pick','10920821','92426828','816 S 216TH ST','13','37','52226' UNION ALL
SELECT '20090915','336','Drop TFR','10920821','93416863','2700 BELLEVUE WAY SE','18','42','52245' UNION ALL
SELECT '20090915','336','Pick','10901780','97624264','2700 BELLEVUE WAY SE','0','0','52252' UNION ALL
SELECT '20090915','336','Pick','10901411','98218227','2700 BELLEVUE WAY SE','0','0','52252' UNION ALL
SELECT '20090915','336','Drop','10901411','98663652','1600 E OLIVE ST','0','0','52260' UNION ALL
SELECT '20090915','336','Drop','10901780','98997732','500 17TH AVE','0','6','52263' UNION ALL
SELECT '20090915','336','Pick','10927731','99436248','7829 S 180TH ST','14','35','52278' UNION ALL
SELECT '20090915','336','Drop','10927731','99577178','12059 12TH AVE NE','22','49','52304' UNION ALL
SELECT '20090915','336','Pick','10901412','99821601','1600 E OLIVE ST','7','23','52316' UNION ALL
SELECT '20090915','336','Drop','10901412','99995272','2700 BELLEVUE WAY SE','8','27','52326' UNION ALL
SELECT '20090915','336','Pick TFR','10901646','99995354','2700 BELLEVUE WAY SE','0','0','0' UNION ALL
SELECT '20090915','336','Drop','10901646','99995416','11621 NE 134TH ST','9','26','52335' UNION ALL
SELECT '20090915','339','Pick','10914842','25068492','747 VASHON AVE NE','10','25','42565' UNION ALL
SELECT '20090915','339','Pick','10909630','29999947','2700 BELLEVUE WAY SE','0','0','42574' UNION ALL
SELECT '20090915','339','Pick','10902321','34000025','2700 BELLEVUE WAY SE','0','0','42574' UNION ALL
SELECT '20090915','339','Pick','10902244','38000003','2700 BELLEVUE WAY SE','0','0','42574' UNION ALL
SELECT '20090915','339','Pick','10902360','41999977','2700 BELLEVUE WAY SE','0','0','42574' UNION ALL
SELECT '20090915','339','Pick','10902240','45999982','2700 BELLEVUE WAY SE','0','0','42574' UNION ALL
SELECT '20090915','339','Drop','10914842','46968735','803 S LANE ST','0','0','42581' UNION ALL
SELECT '20090915','339','Drop','10909630','49874935','803 S LANE ST','0','0','42581' UNION ALL
SELECT '20090915','339','Drop','10902240','49968674','803 S LANE ST','0','0','42581' UNION ALL
SELECT '20090915','339','Drop','10902360','49992085','803 S LANE ST','0','0','42581' UNION ALL
SELECT '20090915','339','Drop','10902244','49997969','803 S LANE ST','0','0','42581' UNION ALL
SELECT '20090915','339','Drop','10902321','49999449','803 S LANE ST','0','0','42581' UNION ALL
SELECT '20090915','339','Pick','10926483','50062139','3033 CALIFORNIA AVE SW','4','16','42588' UNION ALL
SELECT '20090915','339','Drop','10926483','50109168','1101 MADISON ST','5','16','42595' UNION ALL
SELECT '20090915','339','Pick','10927095','50250257','2211 NE 12TH ST','13','31','42610' UNION ALL
SELECT '20090915','339','Pick','10927058','51002609','4400 NE SUNSET BLVD','0','0','42612' UNION ALL
SELECT '20090915','339','Drop TFR','10927058','51532198','2700 BELLEVUE WAY SE','0','0','42621' UNION ALL
SELECT '20090915','339','Drop','10927095','53274152','1031 SW 128TH ST','14','30','42636' UNION ALL
SELECT '20090915','339','Pick','10909625','60000011','803 S LANE ST','8','24','42650' UNION ALL
SELECT '20090915','339','Pick','10902322','61999935','803 S LANE ST','0','0','42650' UNION ALL
SELECT '20090915','339','Pick','10902245','64000007','803 S LANE ST','0','0','42650' UNION ALL
SELECT '20090915','339','Pick','10902361','65999947','803 S LANE ST','0','0','42650' UNION ALL
SELECT '20090915','339','Pick','10902241','67999940','803 S LANE ST','0','0','42650' UNION ALL
SELECT '20090915','339','Drop','10909625','69999946','2700 BELLEVUE WAY SE','0','0','42657' UNION ALL
SELECT '20090915','339','Drop','10902241','71999944','2700 BELLEVUE WAY SE','0','0','42657' UNION ALL
SELECT '20090915','339','Drop','10902361','74000004','2700 BELLEVUE WAY SE','0','0','42657' UNION ALL
SELECT '20090915','339','Drop','10902245','76000011','2700 BELLEVUE WAY SE','0','0','42657' UNION ALL
SELECT '20090915','339','Drop','10902322','77999952','2700 BELLEVUE WAY SE','0','0','42657' UNION ALL
SELECT '20090915','339','Pick','10902385','79999942','2700 BELLEVUE WAY SE','0','0','42657' UNION ALL
SELECT '20090915','339','Pick','10902387','81999959','2700 BELLEVUE WAY SE','0','0','42657' UNION ALL
SELECT '20090915','339','Drop','10902385','83999988','1081 LYNNWOOD AVE NE','0','0','0' UNION ALL
SELECT '20090915','339','Drop','10902387','85999926','12653 SE 162ND ST','4','21','42675' UNION ALL
SELECT '20090915','339','Pick TFR','10923339','86499955','12424 42ND AVE S','7','24','42685' UNION ALL
SELECT '20090915','339','Pick TFR','10927756','86874969','12424 42ND AVE S','0','0','42685' UNION ALL
SELECT '20090915','339','Pick','10927951','87999938','12424 42ND AVE S','0','0','42685' UNION ALL
SELECT '20090915','339','Pick','10920607','89999985','12424 42ND AVE S','0','0','42685' UNION ALL
SELECT '20090915','339','Drop','10920607','90496060','2201 SE MAPLE VALLEY HWY','0','0','42691' UNION ALL
SELECT '20090915','339','Drop','10927951','90868147','12027 SE 184TH ST','0','0','42697' UNION ALL
SELECT '20090915','339','Drop','10927756','91914613','10609 SE 248TH ST','0','0','0' UNION ALL
SELECT '20090915','339','Drop','10923339','91984345','12631 SE 259TH PL','2','11','42711'
SET IDENTITY_INSERT #temp OFF
Again, hopefully I got it right. (Never posted this before).
Let me know what you think.
Thanks!
October 19, 2009 at 10:21 am
...Anyone else out there have any ideas for me?
October 19, 2009 at 10:29 am
You did it perfectly, thank you. So, this gets you to where you were before, maybe a bit farther, and uses joins. I'd ditch the create table(just use select into) that I suggested for the initial temp table with the identity column, as I don't think you'll need it. Sequencing is something you might need after that though. This one lets you do things like join a route to its previous route though. Example of that is shown below.
Note: I believe I handled the difference between our queries before by excluding BookingID's that did not include a "drop" activity to the specified address.
CREATE TABLE #Routes(
RouteSeqint IDENTITY(1,1),
[ROUTE]varchar(10),
MaxDHMilesint,
MaxDHTimeint,
MinPickOdomint,
MaxDropOdomint)
INSERT INTO #Routes(Route, MaxDHMiles, MaxDHTime, MinPickOdom, MaxDropOdom)
SELECT T.Route,
MAX(T.Deadhead_Miles) MaxDHMiles, MAX(T.Deadhead_Time) MaxDHTime,
MIN(P.Odometer) MinPickOdom, MAX(D.Odometer) MaxDropOdom
FROM #Temp T
INNER JOIN (SELECT BookingID FROM #Temp WHERE Activity = 'DROP' AND ADDRESS = '803 S LANE ST') TD
ON T.BookingID = TD.BookingID
LEFT JOIN #Temp P ON T.TempID = P.TempID AND P.Activity = 'Pick'
LEFT JOIN #Temp D ON T.TempID = D.TempID AND D.Activity = 'Drop'
WHERE T.LDATE = '20090915'
GROUP BY T.ROUTE
--Example of Sequence Join
SELECT CT.MinPickOdom - PT.MinPickOdom MinDiff
FROM #Routes CT
LEFT JOIN #Routes PT ON Ct.RouteSeq = PT.RouteSeq + 1
October 20, 2009 at 5:39 pm
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply