Finding products not sold in last 3 months returns products sold within last 3 months-CodePudding

I'm trying to identify only stock_items with their accno, and description that weren't sold in last 3 months by filtering out results in TransDate, but it also returns data of products sold within last 3 months as well.

Does anyone can help to to find a way to fix this issue.

select DA.ACCNO, DA.NAME,DI.STOCKCODE, SI.DESCRIPTION
from DR_ACCS DA
inner join DR_TRANS DT
on DA.ACCNO = DT.ACCNO
inner join DR_INVLINES DI
on DT.SEQNO = DI.HDR_SEQNO
inner join STOCK_ITEMS SI
on DI.STOCKCODE = SI.STOCKCODE
where SI.ISACTIVE = 'Y' and DT.TRANSTYPE = 1 and DI.TRANSDATE > DATEADD(MONTH,-3,GETDATE()) 
order by DA.NAME

CodePudding user response：

Here is my best guess corresponding to the second comment. The CTE will find all STOCKCODE values for transactions that occurred after <today - 3 months>. Do you have transactions dated after today? Think about that and how your goal and the query should reflect their presence. Don't assume, go check. Ask someone that knows if you're not certain.

with products_to_ignore (
   select DI.STOCKCODE 
   from dbo.DR_INVLINES as DI 
   where DI.TRANSDATE > cast(DATEADD(MONTH,-3,GETDATE()) as date)
   -- notice the GREATER THAN operator
)
select ...
from ...
where not exists (select * from products_to_ignore as prodig
   where prodig.STOCKCODE = ??.STOCKCODE)
   and <your other filters - but not the date one> 
order by ...
;

Notice the additional logic around the DATEADD calculation. I leave it to you to look at it and understand what it does. Ask a question if needed. I don't know your schema so I made another guess - and this is why you should include DDL and sample data with a question like this. Notice the schema-qualified table names - that is a best practice.

If the CTE includes all STOCKCODE values that have been sold in the last 3 months, then you simply need to filter the transactions you desire by excluding those products included in the CTE resultset.

That is done using the NOT EXISTS filter in the WHERE clause. Notice that I left out the "guts" of the query. Your current query is likely sufficient for this purpose and you can simply copy/paste it into the main SELECT statement.

CodePudding user response：

Another way of looking at it:

All stock codes where the max trans date is longer ago (less than, numerically) than 3 months ago

SELECT stock_code 
FROM dr_invlines
GROUP BY stock_code
HAVING MAX(trans_date) < DATEADD(MONTH,-3,GETDATE())

You can put this as a subquery or CTE and join your other tables to it..

This does show only products that have sold at some point, not in the last 3 months. It's not clear to me what you want to do about products that have never sold, but there are easy solutions to that, such as "products left join sales where sales is null" or even just unioning the products table onto this with some old date

SELECT stock_code 
FROM (
  SELECT stock_code, trans_date FROM dr_invlines
  UNION ALL
  SELECT stock_code, '1970-01-01' FROM products
) x
GROUP BY stock_code
HAVING MAX(trans_date) < DATEADD(MONTH,-3,GETDATE())