Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
By learning one will teach; by teaching one will learn
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

03
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

MAKE A COMMENT

Name *

Email Address *

Comment *