Crystal Reports & SQL Reporting Services Training Consultants

Account Log in:

Username:

Password:

*Creativity is inventing, experimenting, growing, taking risks, breaking rules, making mistakes, and having fun* Mary Lou Cook

Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers

Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog

August

How Do I Find All the Products that Have Not Generated Sales?

A client recently asked me "How do I create an SQL statement that lists all the products that have not generated any sales, that can be used with either Crystal Reports or SQL Server Reporting Services?"

Using SQL Server 2000:

SELECT     Product .[Product ID]

FROM         Product

WHERE

NOT EXISTS
(
SELECT     [Orders Detail].[Product ID]

FROM         [Orders Detail]

WHERE Product.[Product ID]=[Orders Detail].[Product ID])

Using SQL Server 2005, the above SQL statment can be simplified using the EXCEPT command:

SELECT Product .[Product ID], Product .[Product Name]

FROM Product

EXCEPT

SELECT [Orders Detail].[Product ID]

FROM [Orders Detail]

If you have any questions, or need any assistance, please do not hesitate to Contact Us