Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Be a leader: Remember the one in front is the only one with a decent forward view
 
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!!!!

04
December

How Do I Find Missing Correlating Data?

I recently had a client, who was creating a SQL Server Reporting Services Employee Sales report, that listed the sales agents who had not made any sales, within a given time period.


Solution:

Within SQL there is a EXCEPT clause, which allows one to compare data between 2 datasets.

The EXCEPT clause will return any distinct value from the first dataset that does not exist in the second dataset.


To find the Employees who have made any orders, in a specific date range:

Declare @Startdate as Datetime
Declare @Enddate as datetime

Set @Startdate  = CONVERT(DATETIME, '2005-01-01 23:59:59', 102)
Set @Enddate  = CONVERT(DATETIME, '2005-01-02 23:59:59', 102)



SELECT [Employee ID] FROM Employee

EXCEPT

SELECT     [Employee ID]
FROM         Orders
WHERE     ([Order Date] BETWEEN @Startdate AND @Enddate)


If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us

MAKE A COMMENT

Name *

Email Address *

Comment *