SHARE
TWEET

Untitled

a guest Nov 15th, 2019 119 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2.  
  3.        t2.BUSINESS_TYPE,
  4.  
  5.        t2.CLIENT_TYPE,
  6.  
  7.        CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END AS Upsale,
  8.  
  9.        t1.history_date,
  10.  
  11.        COUNT(t1.application_account) count_accounts,
  12.  
  13.        SUM(t1.portfolio) sum_portfolio,
  14.  
  15.        SUM(t1.cess45_new_discont) sum_cess,
  16.  
  17.        SUM(t1.cession_protfolio) sum_cess_portfolio,
  18.  
  19.        SUM(t1.delta_revenue) AS sum_delta_revenue,
  20.  
  21.        SUM(t1.delta_provisions) AS sum_delta_provisions,
  22.  
  23.        SUM(t1.delta_portfolio) AS sum_delta_portfolio,
  24.  
  25.        SUM(t1.delta_ecl) AS sum_delta_ecl,
  26.  
  27.        CASE WHEN ROW_NUMBER() OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END ORDER BY t1.history_date) <= 12 THEN 0 ELSE
  28.  
  29.                                                              SUM(SUM(t1.delta_revenue)) OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END
  30.  
  31.                                                              ORDER BY t1.history_date
  32.  
  33.                                                              ROWS 11 PRECEDING) END AS delta_revenue_12,
  34.  
  35.        CASE WHEN ROW_NUMBER() OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END ORDER BY t1.history_date) <= 12 THEN 0 ELSE
  36.  
  37.                                                              SUM(SUM(t1.delta_provisions)) OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END
  38.  
  39.                                                              ORDER BY t1.history_date
  40.  
  41.                                                              ROWS 11 PRECEDING) END AS delta_provisions_12,
  42.  
  43.        CASE WHEN ROW_NUMBER() OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END ORDER BY t1.history_date) <= 12 THEN 0 ELSE
  44.  
  45.                                                              SUM(SUM(t1.delta_portfolio)) OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END
  46.  
  47.                                                              ORDER BY t1.history_date
  48.  
  49.                                                              ROWS 11 PRECEDING) END AS delta_portfolio_12,
  50.  
  51.        CASE WHEN ROW_NUMBER() OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END ORDER BY t1.history_date) <= 12 THEN 0 ELSE
  52.  
  53.                                                              SUM(SUM(t1.delta_ecl)) OVER(PARTITION BY BUSINESS_TYPE, CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END
  54.  
  55.                                                              ORDER BY t1.history_date
  56.  
  57.                                                              ROWS 11 PRECEDING) END AS delta_ecl_12
  58.  
  59.  
  60.  
  61. FROM (SELECT *, cession_protfolio = CASE WHEN cess45_new_discont > 0 THEN portfolio ELSE 0 END,
  62.  
  63.                            delta_portfolio = CASE WHEN portfolio - LAG(portfolio) OVER(PARTITION BY application_account ORDER BY application_account, history_date) + LAG(CASE WHEN cess45_new_discont > 0 THEN portfolio ELSE 0 END) OVER(PARTITION BY application_account ORDER BY application_account, history_date) IS NULL THEN 0
  64.  
  65.                            ELSE portfolio - LAG(portfolio) OVER(PARTITION BY application_account ORDER BY application_account, history_date) + LAG(CASE WHEN cess45_new_discont > 0 THEN portfolio ELSE 0 END) OVER(PARTITION BY application_account ORDER BY application_account, history_date) END,
  66.  
  67.                            delta_ecl = CASE WHEN ecl - LAG(ecl) OVER(PARTITION BY application_account ORDER BY application_account, history_date) + LAG(cess45_new_discont) OVER(PARTITION BY application_account ORDER BY application_account, history_date) IS NULL THEN 0
  68.  
  69.                            ELSE ecl - LAG(ecl) OVER(PARTITION BY application_account ORDER BY application_account, history_date) + LAG(cess45_new_discont) OVER(PARTITION BY application_account ORDER BY application_account, history_date) END
  70.  
  71. FROM [DWH_FIRE_WINDOW].[dbo].[tmp_revenues]) AS t1
  72.  
  73.  
  74.  
  75. LEFT JOIN DSS.spr.spr_all_data AS t2
  76.  
  77.        ON t1.application_account = t2.APPLICATION_ACCOUNT
  78.  
  79. WHERE DAY(t1.history_date) BETWEEN 27 AND 32
  80.  
  81. GROUP BY t2.BUSINESS_TYPE, t2.CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END, t1.history_date
  82.  
  83. ORDER BY t2.BUSINESS_TYPE, t2.CLIENT_TYPE, CASE WHEN t2.ExecutionKey IN (32, 1024) THEN 1 ELSE 0 END, t1.history_date
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top