Query help.

[northw]

Hi all,

I have these two queries below, I want to substract the qty from the first query from the second, and display the result, what would be the approach I should take?

SELECT pos.trade_num,
         pos.time_period_cd,
         mkt.quote_def_cd,
         SUM (qty)
    FROM pos_20130729 pos
         INNER JOIN mkt mkt ON pos.mtm_quote_def_num = mkt.quote_def_num
         INNER JOIN org org ON org.strategy_num = pos.strategy_num
         INNER JOIN snapshot snapshot ON pos.snapshot_cd = snapshot.snapshot_cd
   WHERE     mkt.status_ind = 1
         AND snapshot.status_ind = 1
         AND snapshot.snapshot_cd = 'Official'
         AND pos.bifurcation_ind IN (0, 2)
         AND pos.time_period_type_ind = 4
         AND pos.risk_qty != 0
         AND pos.cost_type_cd = 'Primary Settlement'
         AND pos.whatif_trade_ind != 1
         AND org.book_cd = 'Land USProp Gs/Ds/LS'
         AND pos.pl_type_ind = 0 --to get unrealized  
         AND pos.risk_start_dt >= TO_DATE ('20130701000000', 'YYYYMMDDHH24MISS') --??
GROUP BY pos.trade_num, 
         pos.time_period_cd, 
         mkt.quote_def_cd

 SELECT pos.trade_num,
         pos.time_period_cd,
         mkt.quote_def_cd,
         SUM (qty)
    FROM pos_20130725 pos
         INNER JOIN mkt mkt ON pos.mtm_quote_def_num = mkt.quote_def_num
         INNER JOIN org org ON org.strategy_num = pos.strategy_num
         INNER JOIN snapshot snapshot ON pos.snapshot_cd = snapshot.snapshot_cd
   WHERE     mkt.status_ind = 1
         AND snapshot.status_ind = 1
         AND snapshot.snapshot_cd = 'Official'
         AND pos.bifurcation_ind IN (0, 2)
         AND pos.time_period_type_ind = 4
         AND pos.risk_qty != 0
         AND pos.cost_type_cd = 'Primary Settlement'
         AND pos.whatif_trade_ind != 1
         AND org.book_cd = 'Land USProp Gs/Ds/LS'
         AND pos.pl_type_ind = 0 --to get unrealized  
         AND pos.risk_start_dt >= TO_DATE ('20130701000000', 'YYYYMMDDHH24MISS') --??
GROUP BY pos.trade_num, 
         pos.time_period_cd, 
         mkt.quote_def_cd

Thanks in advance!!

 

[carp]

You need to give your sum(qty) column an alias. Then, if we call your two queries q1 and q2, then the following will get you close:

SELECT v1.trade_num,v1.time_period_cd,v1.quote_def_cd, v2.sum_qty - v1.sum_qty AS sum_dif
FROM (q1) v1
INNER JOIN (q2) v2
ON v1.trade_num = v2.trade_num
AND v1.time_period_cd = v2.time_period_cd
AND v1.quote_def_cd = v2.quote_def_cd;

Of course, this will only give you results where the first three columns are identical in both queries.
If you want just a single number, then lose the first three columns in both queries and just subtract the two sum_qty values.

 

[northw]

Thanks Carp!