Crosstab query. 1

[northw]

Hi all,

I have these two queries below, I want to substract the qty from the first query from the second, and display in a cross tab, 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!!

 

[PeterDimitrov]

You can join both queries as subqueries like it is shown in the code window. Use the new query to build the crosstab. You might need to use FULL JOIN instead of LEFT JOIN.

select q1.trade_num,
       q1.time_period_cd,
       q1.quote_def_cd,
       q1.SQty1,q2.SQty2, q1.SQty1-q2.SQty2 as Diff
from (
SELECT pos.trade_num,
         pos.time_period_cd,
         mkt.quote_def_cd,
         SUM (qty) as SQty
    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 ) q1 
LEFT JOIN  (
 SELECT pos.trade_num,
         pos.time_period_cd,
         mkt.quote_def_cd,
         SUM (qty) as SQty
    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 ) q2 ON 
q1.trade_num = q2.trade_num AND 
q1.time_period_cd=q2.time_period_cd AND 
q1.quote_def_cd=q2.quote_def_cd

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[northw]

Thanks Peter!