Slow moving item list

[Former Member]

Hi All,

i want a list of all item which are not delivered from last 15 days.

or which are not sold from last 15 days.

Thanks & Regards

Shiv

[Former Member]

Hi Shiv,

If you have many items, the list wold be very long. If not, here is the query for the first requirement:

SELECT Distinct T0.ItemCode, T0.ItemName

FROM dbo.OITM T0

WHERE T0.ItemCode Not IN (SELECT Distinct T1.ItemCode FROM DLN1 T1 INNER JOIN ODLN T2 ON T2.DocEntry = T1.DocENtry WHERE DateDiff(DD,T2.DocDate,GetDate()) <16) AND T0.SellItem

= 'Y'

Thanks,

Gordon

[Former Member]

Hi Gordan

Thanks for querry.

some filed are also needed with this report

last date of item delivered,

last Quantity delivered,

and to whom that was delivered means customer name .

please do the change as required in the querry.

Thanks

SHIV

[Former Member]

You may need more than one query to get them all. Here is something more:

SELECT Max(T1.DocNum) 'Delivery#', Max(T1.DocDate) 'Last Delivery Date', T0.ItemCode, MAX(T0.Dscription) 'Item Description', T0.Quantity

FROM dbo.DLN1 T0

INNER JOIN dbo.ODLN T1 ON T0.DocEntry = T1.DocENtry

WHERE DateDiff(DD,T1.DocDate,GetDate()) >15 and T1.DocDate > '[%0\]'

Group By T0.ItemCode, T0.Quantity

[Former Member]

Hi Gordan,

This querry shows only those item which are in Delivery note.

but i want it from item list, not from Delivery note.

Thanks

Shiv

[Former Member]

How about your request for last date of item delivered, last Quantity delivered if no delivery?

[Former Member]

Hi Gordon,

your 1st one querry is correct but in this querry i need some extra field as i mentioned.

how these can come in this querry.

thanks

SHIV SEN

[Former Member]

It would be not difficult to add any fields if there are proper links to those tables.

[Former Member]

Thanks Gordon