March 30, 2007 at 8:45 pm
I want the LAST loaded and unloaded weights for each truck/trailer on ONE row:
Here is my table:
SELECT SCALE_TABLE.truck_id,
SCALE_TABLE.trailer_id,
SCALE_TABLE.scale_dt,
SCALE_TABLE.scale_wt,
SCALE_TABLE.l_u_ind,
SCALE_TABLE.order_no
FROM SCALE_TABLE
Here is my data:
truck_id...trailer_id...scale_dt...scale_wt...l_u_ind...order_no
TRUCK A....TL B....1/1/2007...10000........U..........123
TRUCK A....TL B....1/2/2007...12000........U..........123
TRUCK A....TL B....1/3/2007...20000........L..........123
TRUCK A....TL B....1/4/2007...25000........L..........123
TRUCK C....TL D....1/1/2007...10000........U..........222
TRUCK D....TL E....2/1/2007...13000........U..........AAA
TRUCK D....TL E....2/3/2007...20000........L..........AAA
TRUCK D....TL E....2/4/2007...25000........L..........AAA
This is what I want:
truck_id...trailer_id...unload_dt...load_dt...scale_wt_unl...scale_wt_load...order_no
TRUCK A...TL B....1/2/2007....1/4/2006...12000..........25000...........123
TRUCK C...TL D....1/1/2007...................10000.............................222
TRUCK D...TL E ....2/1/2007....2/4/2007...13000..........25000...........AAA
March 31, 2007 at 12:57 am
select truck_id, trailer_id,
unload_dt = max(case when l_u_ind = 'U' then scale_dt end),
load_dt = max(case when l_u_ind = 'L' then scale_dt end),
scale_wt_unl = max(case when l_u_ind = 'U' then scale_wt end),
scale_wt_load = max(case when l_u_ind = 'L' then scale_wt end),
order_no
from scale_table
group by truck_id, trailer_id, order_no
March 31, 2007 at 8:12 am
Listen - thank you so much. Some of these things comes easier to some of us. I really appreciate it and it saved me a bunch of headache.
March 31, 2007 at 9:27 am
Too bad!
The query does not guarantee that all values come from same record...
N 56°04'39.16"
E 12°55'05.25"
March 31, 2007 at 9:40 am
-- Prepare sample data
DECLARE
@data TABLE (truck_id VARCHAR(7), trailer_id VARCHAR(4), scale_dt DATETIME, scale_wt INT, l_u_ind VARCHAR(1), order_no VARCHAR(3))
INSERT
SELECT 'TRUCK A', 'TL B', '1/1/2007', 10000, 'U', '123' UNION ALL
SELECT 'TRUCK A', 'TL B', '1/2/2007', 12000, 'U', '123' UNION ALL
SELECT 'TRUCK A', 'TL B', '1/3/2007', 20000, 'L', '123' UNION ALL
SELECT 'TRUCK A', 'TL B', '1/4/2007', 25000, 'L', '123' UNION ALL
SELECT 'TRUCK C', 'TL D', '1/1/2007', 10000, 'U', '222' UNION ALL
SELECT 'TRUCK D', 'TL E', '2/1/2007', 13000, 'U', 'AAA' UNION ALL
SELECT 'TRUCK D', 'TL E', '2/3/2007', 20000, 'L', 'AAA' UNION ALL
SELECT 'TRUCK D', 'TL E', '2/4/2007', 25000, 'L', 'AAA'
-- Show the expected output
SELECT truck_id,
trailer_id,
MAX(unload_dt) AS unload_dt,
MAX(load_dt) AS load_dt,
MAX(scale_wt_unl) AS scale_wt_unl,
MAX(scale_wt_load) AS scale_wt_load,
order_no
FROM (
SELECT truck_id,
trailer_id,
scale_dt AS unload_dt,
NULL AS load_dt,
scale_wt AS scale_wt_unl,
NULL AS scale_wt_load,
order_no,
ROW_NUMBER() OVER (PARTITION BY truck_id, trailer_id ORDER BY scale_dt DESC) AS RecID
FROM @data
WHERE l_u_ind = 'u'
UNION ALL
SELECT truck_id,
trailer_id,
NULL AS unload_dt,
scale_dt AS load_dt,
NULL AS scale_wt_unl,
scale_wt AS scale_wt_load,
order_no,
ROW_NUMBER() OVER (PARTITION BY truck_id, trailer_id ORDER BY scale_dt DESC) AS RecID
FROM @data
WHERE l_u_ind = 'l'
) AS x
WHERE RecID = 1
GROUP BY truck_id,
trailer_id,
order_no
ORDER BY truck_id,
trailer_id,
order_no
N 56°04'39.16"
E 12°55'05.25"
March 31, 2007 at 9:43 am
You could at least have some decency to comment if our previous suggestions are to your satisfaction...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=354311
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply