question

artisltover avatar image
artisltover asked

One load_id has three records. I need the second to the last one.

0

select

        sr.customer_site_name,         sr.customer_reference as 'Cust Ref #',         sr.imdl_container as 'Customer #',         sr.shipment_id as 'Load #',         sr.shipper_city as 'Shipper City',         sr.shipper_state as 'Shipper St',         sr.consignee_site_state as 'Consig St',         sr.consignee_site_city as 'Consig Cty',         sl.destramp_arrive_date as 'Dest Ramp - Arrive',         sr.delv_live_start as 'Driver Arrived',         sr.delv_sched_to as 'DELV Appt',         sr.delv_spot_unload as 'Delv/Dropped',         sr.delv_spot_empty_ready as 'Notified Empty',         sr.equipment_terminated as 'EQ Term DT',         datediff(sr.pick_spot_shipper_notified, sr.pick_spot_load) - 2 as 'NOA to DELV',         '',         sr.dest_dray_site_name as 'Dest Dray'

from cs.shipment_reporting sr inner join cs.shipment_legs sl

                    on sr.shipment_id = sl.shipment_id

where sr.imdl_container = 'xxx'

and sr.create_date < date_add(now(), interval - 30 day)

So what needs to be added to the where clause is sl.shipment_legs = but i cannot figure out the code to go through field shipment_legs and if total is 3 return the 2nd. It can have more than three. Stupid i can't but can anyone advise?

sqlserver20008
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.