Ulices Diaz
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.
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!
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My comment is accurate, and there were no other comments.
ASKER