Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
;with cte as (select *, row_number() over (partition by ItemNo order by [Date] Desc) as Rn from Sales)
select * from cte where Rn = 1
WITH cte AS
( SELECT *
, ROW_NUMBER() OVER
( PARTITION BY itemcode
ORDER BY docdate DESC ) AS rn
FROM oinm )
SELECT oitm.*
, cte.docdate AS latest
FROM oitm
LEFT OUTER
JOIN cte
ON cte.itemcode = oitm.itemcode
[red]AND[/red] cte.rn = 1
ORDER
BY oitm.itemcode
select OTM.*, MD.MaxDate as Latest
from OTM LEFT JOIN (select ItemCode, max(DocDate) as MaxDate
from OINM group by ItemCode) MD on OTM.ItemCode = MD.ItemCode
select t0.itemcode
-- , oinm.transtype ???? how do I get this extra column
, t1.maxdate as latest
from oitm t0
left join
(select itemcode, max(docdate) as maxdate
from oinm
group by itemcode) t1 on t0.itemcode = t1.itemcode
select OTM.*, MD.MaxDate as Latest, substring(Compound,11,len(Compound)) as TranType
from OTM LEFT JOIN (select ItemCode, max(DocDate) as MaxDate,
max(convert(char(10),DocDate,112) + TranType) as Compound
from OINM group by ItemCode) MD on OTM.ItemCode = MD.ItemCode