[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query - Minus

Posted on 2012-09-19
8
Medium Priority
?
803 Views
Last Modified: 2012-09-24
SELECT 
	[pro number],
	[net amount due],
	[amount paid],
	convert(datetime,[hold reason 4])
FROM 
	[processing table]
WHERE 
	[process week] BETWEEN '8/1/2012' AND '8/31/2012'
MINUS
SELECT     
	*
FROM         
	[GE8-31a]
WHERE     
	Pro IN (SELECT     
			[Pro Number]
                FROM          
			[Processing Table]
                WHERE      
			[Process Week] BETWEEN '8/1/2012' AND '8/31/2012')

Open in new window

Good Day Experts!

I am in a bit of a quandry here.  I have one query that returns 46,419 records and a second one that returns 49,004 records.  I have the resulting data in seperate worksheets in Excel.  Now I want to make a third worksheet to hold the 2,585 records that are in the second query not in the first one.  So, I thought of the minus operator in SQL.  Only problem is I get an error in SQL Query Analyzer when I try to do the minus.  Erorr indicates--> Incorrect syntax near 'MINUS'.

I have never used minus before.  Perhaps I have something wrong.  Can you help?

Thanks,
jimbo99999
0
Comment
Question by:Jimbo99999
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 200 total points
ID: 38414896
you should be using EXCEPT instead

SELECT
      [pro number],
      [net amount due],
      [amount paid],
      convert(datetime,[hold reason 4])
FROM
      [processing table]
WHERE
      [process week] BETWEEN '8/1/2012' AND '8/31/2012'
EXCEPT
SELECT    
      *
FROM        
      [GE8-31a]
WHERE    
      Pro IN (SELECT    
                  [Pro Number]
                FROM          
                  [Processing Table]
                WHERE      
                  [Process Week] BETWEEN '8/1/2012' AND '8/31/2012')
0
 

Author Comment

by:Jimbo99999
ID: 38414919
Good Day

I have tried and receive -->Incorrect syntax near the keyword 'Except'
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38414943
except is available from SQL Server 2005 onwards, for SQL 2000 you should use either a NOT IN clause or NOT EXIXTS clause.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 71

Accepted Solution

by:
Qlemo earned 1400 total points
ID: 38414953
EXCEPT is supported since MSSQL 2005 - you using Query Analyzer hints that you are working with MSSQL 2000, correct? In that case there is no other way than to use a NOT EXISTS, or transform the whole statement to an optimized one:
SELECT 
  [pro number],
  [net amount due],
  [amount paid],
  convert(datetime,[hold reason 4])
FROM 
  [processing table] pt
WHERE 
  [process week] BETWEEN '8/1/2012' AND '8/31/2012'
and [Pro NUmber] not in (select Pro FROM   [GE8-31a])

Open in new window

0
 

Author Comment

by:Jimbo99999
ID: 38414966
Ok...yes 2000.  I have tried your suggestion but I am perplexed wit hthe results.
I have 2793 rows but when I run the queries seperately I get a difference of 2585 records.
0
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 1400 total points
ID: 38415011
The NOT IN does not check for full records, only for same PRO/PRO NUMBER. If that isn't unique, the result is different. If you want a full check, we need to go the NOT EXISTS way, and compare all important columns:
SELECT 
  [pro number],
  [net amount due],
  [amount paid],
  convert(datetime,[hold reason 4])
FROM 
  [processing table] pt
WHERE 
  [process week] BETWEEN '8/1/2012' AND '8/31/2012'
and not exists (
  select * from [GE8-31a] ge
  where ge.PRO              = PT.[pro number]
    and ge.[Net amount due] = PT.[Net amount due]
    and ge.[amount paid]    = PT.[amount paid]
    and ge.[hold reason 4]  = PT.[hold reason 4]
)

Open in new window

I had to guess the [GE8-31a] column names.
0
 
LVL 5

Assisted Solution

by:sfmny
sfmny earned 200 total points
ID: 38415191
Hello there,

When you use MINUS or UNION operators, the list of columns you select have to be the same. Change the "SELECT *" to match the columns in the first "SELECT ..."

These operators are similar to UNIONS and MINUS in set theory and the elements need to be the same to carry out that operation.
0
 
LVL 18

Assisted Solution

by:deighton
deighton earned 200 total points
ID: 38416912
LEFT Join on all fileds on left able, then select rows that are null on primary key of right table
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question