Tricky join problem

[raydenl]

Two separate queries produce these results:

There are 48 periods per day.

select a.mydate, a.myperiod, a.mycode, a.buysell, SUM(a.myvalue) from dbo.mytable a
where a.mydate = '2006-03-01' and a.buysell = 'Buy'
group by a.mydate, a.myperiod, a.mynode, a.buysell

2006-03-01 1 AAA Buy 200.00
...
2006-03-01 48 AAA Buy 123.00
2006-01-01 1 BBB Buy 345.00
...
2006-01-01 48 BBB Buy 453.00
2006-01-01 1 CCC Buy 445.00
...
2006-01-01 48 CCC Buy 256.00

select b.mydate, b.myperiod, b.mycode, b.buysell, SUM(b.myvalue) from dbo.mytable b
where b.mydate = '2006-03-01' and b.buysell = 'Sell'
group by b.mydate, b.myperiod, b.mynode, b.buysell

2006-03-01 1 AAA Sell 100.00
...
2006-03-01 48 AAA Sell 345.00
2006-01-01 1 CCC Sell 567.00
...
2006-01-01 48 CCC Sell 123.00
2006-01-01 1 DDD Sell 400.00
...
2006-01-01 48 DDD Sell 789.00

What I would like to do is subtract all the sell rows from their equivalent buy row.

If the buy row has no equivalent sell row then just return the buy value, and if the sell row has no equivalent buy row then just return the sell value.

Desired result:

2006-03-01 1 AAA 100.00 (200-100)
...
2006-03-01 48 AAA -222.00 (123-345)
2006-01-01 1 BBB 345.00
...
2006-01-01 48 BBB 453.00
2006-01-01 1 CCC -122.00 (445-567)
...
2006-01-01 48 CCC 133.00 (256-123)
2006-01-01 1 DDD 400.00
...
2006-01-01 48 DDD 789.00

Can anyone help me please?

 

[bborissov]

SELECT a.mydate, a.myperiod, a.mycode, 
       CASE WHEN a.myvalue IS NULL THEN ISNULL(b.myvalue,0)
       CASE WHEN b.myvalue IS NULL THEN ISNULL(a.myvalue,0)
       ELSE a.myvalue - b.myvalue END AS Diff
FROM
(select mydate, myperiod, mycode, SUM(myvalue) from dbo.mytable
where mydate = '2006-03-01' and buysell = 'Buy'
group by mydate, myperiod, mynode, buysell) A
FULL JOIN 
(select mydate, myperiod, mycode, buysell, SUM(b.myvalue) from dbo.mytable
where mydate = '2006-03-01' and buysell = 'Sell'
group by mydate, myperiod, mynode, buysell
) B ON A.mydate = b.mydate AND A.myperiod = B.myperiod AND A.mynode = A.mynode

not tested


Borislav Borissov