query for serial number for items sold

[Former Member]

hi,

I want a query which would show serial numbers for Product A and Product B which were sold. I know that following tables will be used

SELECT T0.[CardCode], T0.Docnum,T0.[DocDate], T0.[CardName],T1.Quantity,T1.[ItemCode],T6.DistNumber

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

inner join DLN1 T4 on T1.DocEntry=T4.BaseEntry

inner join ODLN T7 on T4.DocEntry=T7.DocEntry

inner join OSRN T6 on T4.itemcode=t6.itemcode

WHERE T1.[ItemCode] in ('ProductA','ProductB') and T0.[CardName] like'% naaveol%' and T0.CANCELED<>'Y'

group by T0.[CardCode],T0.[CardName], T0.[DocDate],T1.[ItemCode] ,T0.Docnum,T6.DistNumber,T1.Quantity

Howerver I am doing some thing wrong and it is showing alot more records than it should. basically what I was trying to do was to get

item code

serial num

quantity

for the items mentioned sold to the particular business partner.

Your help will be greatly appreciated

[Former Member · Accepted solution]

Hi again.......

Please try this.......

SELECT T0.CardCode, T0.Docnum,T0.DocDate, T0.CardName,T1.Quantity,T1.ItemCode,T4.DistNumber 
from ODLN T0 inner join DLN1 T1 on T0.DocEntry=T1.DocEntry
left outer join SRI1 I1 on T1.ItemCode=I1.ItemCode   and (T1.DocEntry=I1.BaseEntry and T1.ObjType=I1.BaseType)
left outer join OSRN T4 on T4.ItemCode=I1.ItemCode and I1.SysSerial=T4.SysNumber

Regards,

Rahul

[Former Member · Accepted solution]

Thank you so much for answering my question :). This solved my issue..

[Former Member · Accepted solution]

I have sent you email on your gmail account, please check.