How to determine products that have not been sold since a given date

[former_member620944]

We are trying to list all products put in the system before a certain date but which have not been sold since a second date.

I tried to link our products table to our invoice detail table and then search for nulls from invoice detail (that is, the product exists but hasn't been sold). That's not working and I'm a little lost as to what to try next. Has anyone got a suggestion?

Thanks.

[abhilash_kumar]

Hi Janet,

It's very easy to do this using a SQL Query as the report's datasource.

The SQL would look something like this:

Select
P.Product
From Product P
Where P.CreationDate <= '2019-01-01'
AND NOT EXISTS
(
Select 1 
From Invoice I
Where I.ProductID = P.ProductID
AND I.InvoiceDate >= '2019-01-01'
)

-Abhilash

[DellSC]

It's also possible to do this without using a query (Command).

1. Use the tables that contain the Product and Invoice Details information.

2. Add a link FROM Product TO Invoice details. Select the link, right click on it and select "Link Options". Make the join a "Left Outer" join.

3. In the Select Expert, you'll need to edit the formula to be something like this:

{Product.CreationDate} <= '2019-01-01' and
{Invoice.InvoiceDate} >= '2019-01-01' and
IsNull({Invoice.ProductID})

If you do decide to use a Command like Abhilash suggests, there's information about best practices for that here: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

-Dell

[former_member620944]

The problem is that there are literally thousands of these ... I was going through the database and reviewing them in our inventory system. Most were fine and what I was looking for. But there were some that had multiple invoices outside the date parameter which shouldn't have been returned.I

I'll try running the query in Access -- it's easier to see results there than in CR.

Thanks for your help

[former_member620944]

That's what I tried and for whatever reason, it didn't work. 😞 Abhilash's solution worked ... partially.

Abhilash: for some reason, I'm getting some parts -- some of them have earlier invoice dates as well as later ones, but some only have later ones and they should be filtered out by the command.

Any suggestions? Is there any way to look for a max date in the second half of the command?

[abhilash_kumar]

The query inside 'NOT EXISTS' should ideally only return Products that have an Invoice since a certain date.

If you run this query, do you see the product that you believe should be filtered out?

Select 
P.Product,
I.Invoice#,
I.InvoiceDate
From Invoice I
JOIN Product P ON I.ProductID = P.ProductID
AND I.InvoiceDate >= '2019-01-01'

-Abhilash