MS SQL 2008 R2 - How to Compare or get the Difference from Two Queries

First I would like to thank you guys for all of your help with my question(s) and others.

I have MS SQL 2008 R2 and I was getting a sum of two columns like so:

SELECT SUM(RATE+RATE_ADDITIONAL) FROM LOADS WHERE INVOICED=1 
and NOT OV_FACTORING=2 and DATE_INVOICED >= '1/1/2007' 
and DATE_INVOICED <= '5/1/2012'

but when I try it like this:

SELECT SUM(RATE+RATE_ADDITIONAL) FROM LOADS WHERE INVOICED=1 
AND OV_FACTORING=3 OR OV_FACTORING=4 OR OV_FACTORING=5 
and DATE_INVOICED >= '1/1/2007' and DATE_INVOICED <= '5/1/2012'

and it comes out to with a different total when it should be the same since in the OV_FACTORING the only values are: 2,3,4, and 5.

I have a column named LOAD_NO and I would like to get that LOAD_NO that is different or non existant in the other query, so something like to compare the two queries and to output the load_no that is different or shows up in one of the queries and not in the other one.

I tried some examples on the site but cannot get them to work.

Again, Thanks for all of your help...

Answers


To get the same result, you'll need to isolate the OV_FACTORING filters together like so:

SELECT SUM(RATE+RATE_ADDITIONAL) 
FROM LOADS WHERE INVOICED=1 AND 
(OV_FACTORING=3 OR OV_FACTORING=4 OR OV_FACTORING=5) AND 
DATE_INVOICED >= '1/1/2007' AND DATE_INVOICED <= '5/1/2012'

If you want to limit in the OV_FACTORING the you can do this:

SELECT OV_FACTORING, SUM(RATE+RATE_ADDITIONAL) 
  FROM dbo.LOADS 
  WHERE INVOICED = 1
  AND OV_FACTORING IN (3,4,5)
  AND DATE_INVOICED >= '20070101' 
  AND DATE_INVOICED <= '20120501'
  GROUP BY OV_FACTORING;

Or if you really want the OR statements. Then you can do this:

SELECT OV_FACTORING, SUM(RATE+RATE_ADDITIONAL) 
  FROM dbo.LOADS 
  WHERE INVOICED = 1
  AND (OV_FACTORING=3 OR OV_FACTORING=4 OR OV_FACTORING=5)
  AND DATE_INVOICED >= '20070101' 
  AND DATE_INVOICED <= '20120501'
  GROUP BY OV_FACTORING;

SELECT SUM(RATE+RATE_ADDITIONAL) 
FROM LOADS 
WHERE INVOICED=1 
AND (OV_FACTORING=3 OR OV_FACTORING=4 OR OV_FACTORING=5) 
AND DATE_INVOICED >= '1/1/2007' 
AND DATE_INVOICED <= '5/1/2012

In order to see that the numbers in the table are accurate, check the results of:

SELECT OV_FACTORING, SUM(RATE+RATE_ADDITIONAL) 
  FROM dbo.LOADS 
  WHERE INVOICED = 1
  AND DATE_INVOICED >= '20070101' 
  AND DATE_INVOICED <= '20120501'
  GROUP BY OV_FACTORING;

Some general comments that don't relate to the problem:

  • carriage returns and indenting make queries 100X more readable
  • never use regional formats for dates - is 5/1/2012 May 1 or Jan 5? (blog post)
  • specify the schema prefix for your queries (blog post)

Need Your Help

java parameter replacement in a String

java regex

I'ms looking for a way to replace my variables in a string by their value. Here is my string lookalike: