deliveries report query

[Former Member]

Hi:

I need to do a query to get a report of the deliveries, having the parameters : date and series.

The report will need delivery no., address, invoice related (if exists),, sale order number and rows(itemcode, dscription,quantity), client name (cardname), client code, and series name. and dont need closed deliveries

Can anyone help?

thnx

Edited by: gerardo lopez on Feb 6, 2009 1:18 AM

Edited by: gerardo lopez on Feb 6, 2009 1:19 AM

[Former Member · Accepted solution]

Try this one to start:

SELECT T1.docdate, T1.docnum as 'Delivery#', T1.Address2, T0.itemcode, T0.Dscription,
T0.quantity,  T1.CardName as 'Customer Name', T0.BaseEntry as 'Sales Order#', T0.TrgetEntry as 'Invoice#'
From dbo.DLN1 T0
INNER JOIN dbo.ODLN T1 on T1.DocEntry = T0.docentry
WHERE T1.docdate Between '[%0]' AND '[%1]' AND T1.DocStatus = 'O'
ORDER BY T1.docdate

Thanks,

Gordon

[Former Member]

Hi Gerardo,

Pls use the following query ,

Select dln1.itemcede,dln1.itemdescription,.......... ,ordr.docnum from dln1

inner join odln on dln1.docentry=odln.docentry

left outer join rdr1 on rdr1.targetentry=ordr.docnum

where odln.docdate >= [%0] and odln.docdate <= [%1]

hope this will hwlp u .

Rgds,

Premraj

Edited by: PremRaj kaushik on Feb 6, 2009 12:35 PM

[Former Member]

Hi

I am not sure what you are asking but looks like this

You want to list out all open deliveries with Delivery Number , Client Name , Client code, Client Address , Series Name, Sales Order Number , AR Invoice Number ,itemcode ,description and quantity with a parameter to select series name and date

Hope this is what you are looking for .In that case , you can use ODLN and DLN1.

BTW - what exactly seriesname is referring to

Thank you

Bishal