AS >>BEGIN >> -- SET NOCOUNT ON added to prevent extra result sets from >> -- interfering with SELECT statements. >> SET NOCOUNT ON; >> >> >> --DECLARE @ClientCode char(3) >> --DECLARE @ClientName varchar(50) >> --DECLARE @date varchar(10) >> --SET @date = '20110330' >> >>SELECT bol_number, u.ref_num, pro_number, client_zip, client_city, customer_name, customer_state, tb.carrier_name, bol_status, p.delivered_date, customer_zip, ship_date, p.weight, io, p.date_last_updated, convert(date,curr_est_deliver_date,101) as [est_delivery], convert(date,appt_date,101) as [appt_date], >> >>CASE WHEN ISNULL(p.delivered_date, tb.delivered_date) IS NOT NULL OR force_delivered = 1 OR (SELECT TOP 1 ISNULL(status_code, appt_status_code) FROM tbl_214_status a LEFT JOIN tbl_214_isa b ON b.tbl_214_id = a.tbl_214_id WHERE bol_id = tb.bol_id ORDER BY isa_09 DESC, isa_10 DESC, status_date DESC, status_time DESC, a.record_num DESC) = 'D1' THEN (SELECT sd_display FROM edi_status_descriptions WHERE edi_status_code = 'D1') >>ELSE (SELECT TOP 1 ISNULL(sd_display, asd_display) FROM tbl_214_status a LEFT JOIN edi_status_descriptions b ON b.edi_status_code = a.status_code LEFT JOIN edi_appt_status_descriptions c ON c.edi_appt_status_code = a.appt_status_code LEFT JOIN tbl_214_isa d ON d.tbl_214_id = a.tbl_214_id WHERE bol_id = tb.bol_id ORDER BY isa_09 DESC, isa_10 DESC, status_date DESC, status_time DESC, a.record_num DESC) END as [Latest Status Description], >> >>CASE WHEN ISNULL(p.delivered_date, tb.delivered_date) IS NOT NULL OR force_delivered = 1 OR (SELECT TOP 1 ISNULL(status_code, appt_status_code) FROM tbl_214_status a LEFT JOIN tbl_214_isa b ON b.tbl_214_id = a.tbl_214_id WHERE bol_id = tb.bol_id ORDER BY isa_09 DESC, isa_10 DESC, status_date DESC, status_time DESC, a.record_num DESC) = 'D1' THEN (SELECT srd_display FROM edi_status_reason_descriptions WHERE edi_status_reason_code = 'NS') >>ELSE (SELECT TOP 1 ISNULL(srd_display, ard_display) FROM tbl_214_status a LEFT JOIN edi_status_reason_descriptions b ON b.edi_status_reason_code = a.status_reason_code LEFT JOIN edi_appt_reason_descriptions c on c.edi_appt_reason_code = a.appt_reason_code LEFT JOIN tbl_214_isa d ON d.tbl_214_id = a.tbl_214_id WHERE bol_id = tb.bol_id ORDER BY isa_09 DESC, isa_10 DESC, status_date DESC, status_time DESC, a.record_num DESC) END as [Latest Reason Description] >> >>FROM tbl_bol tb >> LEFT JOIN dbo.tbl_ref_nums u on tb.bol_id = u.bol_id >> INNER JOIN dbo.tbl_214_datatable p on tb.bol_id = p.bol_id >> INNER JOIN tbl_carrier c ON c.scac_code = p.scac >> INNER JOIN tbl_carrier_access a ON a.carrier_id = c.carrier_id AND a.dba_id = tb.dba_id >> >> >>--AND ISNULL(p.delivered_date, tb.delivered_date) IS NULL >>AND force_delivered = 0 >>AND (SELECT TOP 1 >> ISNULL(status_code, appt_status_code) >> FROM tbl_214_status a >> LEFT JOIN tbl_214_isa b ON b.tbl_214_id = a.tbl_214_id >> ORDER BY isa_09 DESC, isa_10 DESC, status_date DESC, status_time DESC, a.record_num DESC) <> 'D1' >>END >> >> >>>>