[thelist] Sum'd Union Tables

[Rob Smith]

Ok. I'm trying to get the net result of this query:

SELECT     SUM(total_sale) AS Sale, SUM(total_margin) AS Margin,
segment_id

FROM         receipt

WHERE     (DATEDIFF(month, date_created, GETDATE()) = 1) AND (status <>
'Cancelled') AND commission_ID = 280

GROUP BY segment_id

UNION

SELECT     SUM(total_sale) AS Sale, SUM(total_margin) AS Margin,
segment_id

FROM         return_receipt

WHERE     (DATEDIFF(month, date_created, GETDATE()) = 1) AND (status <>
'Cancelled') AND (commission_id = 280)

GROUP BY segment_id

ORDER BY segment_id

 

The data returned by this query is:

31018               12740.89           1

-754                  -356.19             2

7573                 3009.83             2

-1574                -703                  3

24180.38           10380.73           3

-287.4               -54.2                 4

23668.5             6569.78             4

11498               3262.82             8

2401                 472.89              9

 

That's fine, but what I really really would like to see is this:

31018               12740.89           1

6819                 2653.64             2

22606.38           9677.73             3

23381.1             6515.58             4

11498               3262.82             8

2401                 472.89              9

 

So in basically, the first query returns all orders. The second unioned
query gives the returns. All I need to do is subtract the orders from
the returns.

 

Can you help?


Rob Smith
LexJet
rob.smith at lexjet.com
http://www.lexjet.com
(800)453-9538
(941)330-1210 Int'l
(941)330-1220 Fax
1680 Fruitville Road, 3rd Floor
Sarasota, FL 34236

Proud Sponsors of the Professional Digital Imaging Association (www.pdia.org)