Top 1 in desc order not working 1

[coolstrike23]

Hi ,

I need the prev doc number by date for record combination of item and customer. Below is returning the same top1 value for all main query result.

TABLE

CUST ITEM TRANDATE TRANNUM
A CB 21/3/2012 IN20123
A CB 15/3/2012 IN20100
A XY 22/3/2012 INBC2001
A XY 1/2/2012 12345

Result i need is

A CB IN20123 IN20100
A XY INBC2001 12345


SELECT
rtrim(customer) AS CustomerCode, rtrim(max(arcus.namecust)) AS Customer, cast(OESHDT.TRANNUM AS nvarchar) AS DocNum,
rtrim(item) AS Item, rtrim(max(icitem.[desc])) AS ItemDesc,

cast(cast(oeshdt.trandate as nvarchar) as datetime) as Date,

(Select Top 1 dupe.trannum
from oeshdt as Dupe join OESHDT on DUPE.ITEM=oeshDt.ITEM AND DUPE.CUSTOMER=OESHDT.CUSTOMER
And DUPE.TRANDATE < OESHDT.TRANDATE
Group by Dupe.TRANDATE,dupe.customer,dupe.item,dupe.trannum
ORDER BY Dupe.TRANDATE DESC,dupe.customer,dupe.item) AS LASTINV
FROM oeshdt, arcus, icitem
WHERE oeshdt.customer = arcus.idcust AND oeshdt.item = icitem.itemno and
qtysold > 0 and
cast(cast(oeshdt.trandate as nvarchar) as datetime) > getdate()-7
GROUP BY customer, item, OESHDT.trandate, OESHDT.TRANNUM

Thanks In advance

 

[imex]

Try something like that:

(Select Top 1 dupe.trannum
 from oeshdt as Dupe
 where (DUPE.ITEM = oeshDt.ITEM) AND 
       (DUPE.CUSTOMER = OESHDT.CUSTOMER) AND 
       (DUPE.TRANDATE < OESHDT.TRANDATE)
 ORDER BY Dupe.TRANDATE DESC) AS LASTINV

I hope this helps.


Imoveis em Guarulhos

 

[coolstrike23]

That fixes it. Thank you