cancel
Showing results for 
Search instead for 
Did you mean: 

List of items not sold in last 2 years

Former Member
0 Kudos

Hi Experts,

Is it possible to find a list of items that have not been sold in the last 2 years?

Possible SAP Report or SQL Query?

Thank you.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Paul ,

try this

SELECT ItemCode, ItemName FROM OITM

WHERE ItemCode NOT IN (SELECT ItemCode FROM OINM where DocDate >=  DATEADD(year,-2,GETDATE()))

Regards,

BHARATHIRAJA

Former Member
0 Kudos

Hi,

Thank you for the replies. Is it possible to add the date 31.11.2012 therefore any item not sold after that date appears in the list?

Thanks again

Former Member
0 Kudos

Hi,

Then try this

SELECT ItemCode, ItemName FROM OITM

WHERE ItemCode NOT IN (SELECT ItemCode FROM OINM where DocDate >= '2012-11-31')

Former Member
0 Kudos

Hi,

My mistake. 31.11.2012 obviously doesn't exist. I have amended it for 31.10.2012

Thank you very much for your help.


malcolm_lamour
Explorer
0 Kudos

Hi Bharathiraja

Will not also pull out other documents not related to "sold" i.e credit notes etc....

"sold" as in Invoices comes from OINV - document level and INV1 - ItemLevel afaik.

regards

Malcolm

Answers (3)

Answers (3)

malcolm_lamour
Explorer
0 Kudos

Hi

Adjust as you see fit.

Select Itemcode, max(Docdate) as LastInvoiceDate from INV1

group by itemcode

having max(Docdate)<=DATEADD(year,-2,GETDATE())

Former Member
0 Kudos

Thank you for all your help

Former Member
0 Kudos

hi,

try below.

SELECT itemcode

FROM [youritemtable]

WHERE itemcode NOT IN (

SELECT DISTINCT itemcode FROM [yoursalestable] where year([yourdatesoldfield]) >= (year(getdate()) -2)

)

note: this is not tested, please modify just in case there's wrong with the query.

former_member205766
Active Contributor
0 Kudos

Hi

Check the below link

With Regards

Balaji Sampath