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' ENDDid I miss taking something out or isn't this going to work the way I thought?