Link to home
Start Free TrialLog in
Avatar of Ulices Diaz
Ulices DiazFlag for United States of America

asked on

Problem adding a Top 1 join

I have a complex script which is pulling from various tables for an internal page.

The new requirement is to join the OrderAudit Table with only the latest time and date (last activity) for a particular order.

BEGIN     TRAN
    Select
                    Ltrim(Rtrim(H.ord_no)) AS TrimmedOrderNo
                  , a1.aud_dt
                  , Ltrim(h.cus_no)                 AS CustomerNo
                  , C.cmp_name                      as CustomerName
                  , Convert(varchar(50),DD.Account) as CustomerGuid
                  , h.ship_to_name
                  , h.ship_to_addr_1 AS Address1
                  , h.ship_to_addr_2 AS Address2
                  , h.ship_to_addr_3 AS Address3
                  , h.ship_to_addr_4 AS Address4
                  , h.ship_to_city   AS City
                  , h.ship_to_state  AS State
                  , CASE
                                    When h.status='C'
                                                    then 'Credit Hold'
                                    WHEN h.status = '1'
                                                    THEN 'Booked'
                                    WHEN h.status = '4'
                                                    THEN 'Picking Ticket Printed'
                                    WHEN h.status = '5'
                                                    THEN 'Picked'
                                    WHEN h.status = '6'
                                                    THEN 'Pack Slip Printed'
                                    WHEN h.status = '7'
                                                    THEN 'Shipped'
                                    WHEN h.status = '8'
                                                    THEN 'Billed'
                                                    ELSE 'Invoice Printed'
                    END          AS OrdStatus
                  , s.code_desc  AS ShipMethodDesc
                  , h.mfg_loc    AS Location
                  , i.pur_or_mfg AS MFG
                  , lo.loc_desc  AS LocationDesc
                  , l.req_ship_dt
                  , CM.Notes
                  , CASE
                                    WHEN ISNULL(CM.ID,0) = 0
                                                    THEN 0
                                                    ELSE 1
                    END                                                         as HasComment
                  , l.line_no                                                   AS LineItem
                  , l.item_no                                                   AS ItemNumber
                  , l.item_desc_1                                               AS ItemDesc
                  , l.qty_ordered                                               AS QtyOrdered
                  , l.qty_to_ship                                               AS QtyToShip
                  , Dateadd(dd, 1 - Datepart(dw, l.req_ship_dt), l.req_ship_dt) AS ReqShipWeek
                  , l.loc                                                       AS LineLocation
                  , lo2.loc_desc                                                AS LineLocationDescription
                  , h.status
                  , h.oe_po_no
                  , h.job_no
                  , L.ID
                  , I.prod_cat
    from
                    oeordhdr_sql h (NOLOCK)
                    Inner Join
                                    OrderLines l
                                    on
                                                    h.ord_type   = l.ord_type
                                                    AND h.ord_no = l.ord_no
                    Inner Join
                                    Inventory I
                                    on
                                                    l.item_no = I.item_no
                    Left Outer Join
                                    CustomerTable c
                                    on
                                                    h.cus_no = c.debcode
                    Left Outer Join
                                    [synergy].dbo.divisiondebtors AS DD
                                    on
                                                    LTrim(RTRIM(H.cus_no)) = LTRIM(RTRIM(DD.debcode)) Collate Database_Default
                    Left Outer Join
                                    ShippingCodes S
                                    on
                                                    h.ship_via_cd = s.sy_code
                    Left Outer Join
                                    Warehouses  Lo
                                    on
                                                    h.mfg_loc = lo.loc
                    Left Outer Join
                                    WAREHOUSES lo2
                                    on
                                                    l.loc = lo2.loc
                    Left Outer Join
                                    OrderComments Cm
                                    on
                                                    LTRIM(RTRIM(H.ord_no)) = ltrim(rtrim(cm.ord_no))
                                                    and cm.ordertype       ='OE'
                                                    and cm.line_no         =0
                    Left Outer Join
                                    (
                                             select
                                                      top 1 aud.ord_no
                                                    , aud.aud_dt
                                                    , aud.aud_tm
                                             from
                                                      OrderAudit aud
                                             where
                                                      h.ord_no= aud.ord_no
                                             order by
                                                      aud.aud_tm desc
                                    )
                                    q1
    WHERE
                    (
                                    h.ord_type       in ('O')
                                    And H.status NOT IN ('L'
                                                       ,'C')
                    )
                    and
                    (
                                    h.Status not in ('L')
                                    AND s.cd_type = 'V'
                    )
END TRAN

Open in new window


I've tried several variation on scripting this, but I've hit a wall and cannot seem to get it working.

Any thoughts or advice would be greatly appreciated.

Thank you!
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ulices Diaz

ASKER

Did not resolve the issue
My comment is accurate, and there were no other comments.