Channel FTR.sql
application/sql
Filename: Channel FTR.sql
Type: application/sql
Part: 1
--SELECT * FROM A
ALTER PROCEDURE [Prod].[ - Princing_Channel_Model]
AS
BEGIN
Drop table if exists #temp_Flows
--CREATING A TEMPORAL TABLE WHERE WE APPLY THE GLOBLY METHODOLOGY OF REMOVING INTERNAL TRANSFERS
Create Table #temp_Flows (
[Fiscal year period] varchar(20),
[Company Code] Varchar(5),
[Account] varchar(20),
[Generic Material] Varchar(8),
[Issue] float,
[Transfers In] float,
[Transfers Out] float,
[Return] float
)
Insert into #temp_Flows
SELECT DISTINCT [Fiscal year period],[Company Code],[Account],[Generic Material],
SUM(CASE WHEN [Metric] IN ('s (in)') THEN coalesce([Metric Value],0) END) OVER (PARTITION BY [Account], [Company Code],[Generic Material],[Fiscal year period],[Metric]) AS [Issue]
,SUM(CASE WHEN [Metric] IN ( 'tDD (in)','tDE (in)','tED (in)','tEE (in)') THEN coalesce([Metric Value],0) END) OVER (PARTITION BY [Account],[Company Code],[Generic Material] ,[Fiscal year period],[Metric]) AS [Transfers In]
,SUM(CASE WHEN [Metric] IN ('tDD (Out)','tDE (Out)','tED (Out)','tEE (Out)') THEN coalesce([Metric Value],0) END) OVER (PARTITION BY [Account],[Company Code],[Generic Material] ,[Fiscal year period],[Metric]) AS [Transfers Out]
,SUM(CASE WHEN [Metric] IN ( 'r (out)') THEN ([Metric Value]) END) OVER (PARTITION BY [Account],[Company Code],[Generic Material],[Fiscal year period],[Metric]) AS [Return]
FROM
(
SELECT [Fiscal year period],[Company Code],[Contra Account]
,[Account],[Generic Material],[Metric],[Metric Value]
FROM [
].[Prod].[ - Global Field Flows (S_GL_KPI_PRSF_002) Full dataset (Client)_Table_SAP BW]
-------------Applying the Global Methodology for Transfers
WHERE [Account] != [Contra Account]
AND [Metric] IN ( 'tDD (Out)','tDE (Out)','tEE (Out)','tED (Out)' ,'tDD (in)','tDE (in)','tEE (in)','tED (in)')
-----Bringing in other data excluding the filtering of inta and contra movements/internal flows
UNION ALL
SELECT [Fiscal year period],[Company Code],[Contra Account]
,[Account],[Generic Material],[Metric],[Metric Value]
FROM [].[Prod].[ - Global Field Flows (S_GL_KPI_PRSF_002) Full dataset (Client)_Table_SAP BW]
WHERE [Metric] IN ('r (out)','s (in)','r (star) (in)','rDP (in)','rEP (in)','sPE (out)','sPD (out)'
,'i (star) out (q+dDE (star)-out)','y (Based on P.Ord.)','x','z','q (in) - (s (in))'
)
) Table_1
WHERE [Company Code] IN ('AE10','AE20','SA10')
-----We instert the modelled date which excluded internal transfers into FlowsFact temp table Flows
Drop table if exists #temp_FlowsFact
Create Table #temp_FlowsFact (
[Fiscal year period] varchar(20),
[Calendar day] DATE,
[Financial year] Varchar (5),
[Period] Varchar(8),
[Account] varchar(20),
--[Generic Material] Varchar(8),
[Issue] float,
[Transfers In] float,
[Transfers Out] float,
[Return] float
)
INSERT INTO #temp_FlowsFact
SELECT * FROM
(
SELECT DISTINCT FLOWS.[Fiscal year period],Calendar_Table.[Last Day of period],Calendar_Table.[Financial Year],Calendar_Table.[Period]
,FLOWS.[Account],ISSUE.Issues,TRANS_IN.[Transfers In],TRANS_OUT.[Transfers Out],RETURNS.[Return]
FROM #temp_Flows AS FLOWS
-----Bringing in Issue fact
LEFT OUTER JOIN (SELECT [Account], [Fiscal year period], COALESCE(SUM([Issue]) ,0) AS [Issues] FROM #temp_Flows AS Issues
GROUP BY [Fiscal year period],[Account] ) AS ISSUE
ON FLOWS.Account=ISSUE.Account AND FLOWS.[Fiscal year period] =ISSUE.[Fiscal year period] --AND FLOWS.[Generic Material]=ISSUE.[Generic Material]
-----Bringing in Transfers In fact
LEFT OUTER JOIN (SELECT [Account], [Fiscal year period], COALESCE(SUM([Transfers In]) ,0) AS [Transfers In] FROM #temp_Flows AS TRANS_IN
GROUP BY [Fiscal year period],[Account] ) AS TRANS_IN
ON FLOWS.Account=TRANS_IN.Account AND FLOWS.[Fiscal year period] =TRANS_IN.[Fiscal year period] --AND FLOWS.[Generic Material]=TRANS_IN.[Generic Material]
-----Bringing in Transfers out fact
LEFT OUTER JOIN (SELECT [Account], [Fiscal year period], COALESCE(SUM([Transfers Out]) ,0) AS [Transfers Out] FROM #temp_Flows AS TRANS_OUT
GROUP BY [Fiscal year period],[Account] ) AS TRANS_OUT
ON FLOWS.Account=TRANS_OUT.Account AND FLOWS.[Fiscal year period] =TRANS_OUT.[Fiscal year period] --AND FLOWS.[Generic Material]=TRANS_OUT.[Generic Material]
-----Bringing in return fact
LEFT OUTER JOIN (SELECT [Account], [Fiscal year period], COALESCE(SUM([Return]) ,0) AS [Return] FROM #temp_Flows AS RETURNS
GROUP BY [Fiscal year period],[Account] ) AS RETURNS
ON FLOWS.Account=RETURNS.Account AND FLOWS.[Fiscal year period] =RETURNS.[Fiscal year period] --AND FLOWS.[Generic Material]=RETURNS.[Generic Material]
----Bringing in last day of reporting Date for ease of reporting and calculations of rolling numbers
LEFT OUTER JOIN (
Select
CAST([Date] AS DATE) AS [Date]
,CONCAT('20',SUBSTRING([FY],3,2)) AS [Financial Year]
,[Period]
,FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date]) AS [First Day of period]
,FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date] DESC) AS [Last Day of period]
,DATEDIFF(DAY,(FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date])),(FIRST_VALUE([Date]) OVER (PARTITION BY FY,[Period] ORDER BY [Date] DESC))) +1 AS [Number of days]
From [].[Prod].[ - Calendar Days Data_View_Other] AS CDF
Where
[FY] IS NOT NULL
)
AS Calendar_Table ON LEFT(FLOWS.[Fiscal year period],3) =CONCAT('0',REPLACE( Calendar_Table.[Period],'P','')) AND RIGHT(FLOWS.[Fiscal year period],4)= Calendar_Table.[Financial Year]
) FlowsFact ;
------------------------------ Creating temp table for Unpivoted results as well as the calculation for FTRs
DROP TABLE IF EXISTS #temp_FieldFlowsFact
Create Table #temp_FieldFlowsFact (
[Account] varchar(20),
[Calendar day] DATE,
[Financial year] Varchar (5),
[Period] Varchar(8),
[Period type] Varchar(10),
--[Generic Material] Varchar(8),
[Metric] Varchar(50),
[Metric Value] float
)
INSERT INTO #temp_FieldFlowsFact
SELECT [Account],[Calendar day],[Financial year],[Period]
,CASE
WHEN [Metric] like '%R12M%' THEN 'R12M'
WHEN [Metric] like '%R6M%' THEN 'R6M'
WHEN [Metric] like '%R3M%' THEN 'R3M'
ELSE 'Periodic'
END AS [Period type]
,[Metric],[Metric Value]
FROM
(
----Calculating Rolling FTRs
SELECT [Account], [Calendar day] ,[Financial year],[Period]
,[Issue]
,SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Issues]
,SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Issues]
,SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Issues]
,[Transfers In]
,SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Transfers In]
,SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Transfers In]
,SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Transfers In]
,[Transfers Out]
,SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Transfers Out]
,SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Transfers Out]
,SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Transfers Out]
,[Return]
,SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Return]
,SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Return]
,SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Return]
,ROUND(ISNULL(([Transfers Out]+[Return])/NULLIF(([Issue]+[Transfers In]),0),0)*100,2) AS [FTR%] --Calculating FTR%
,ROUND(ISNULL(( SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
+
SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
)
/NULLIF((
SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
+
SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
),0),0)*100,2) AS [R12M FTR%] --Calculating R12M FTR%
,ROUND(ISNULL(( SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
+
SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING)
)
/NULLIF((
SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING)
+
SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING)
),0),0)*100,2) AS [R6M FTR%] --Calculating R6M FTR%
,ROUND(ISNULL(( SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING)
+
SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING)
)
/NULLIF((
SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING)
+
SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING)
),0),0)*100,2) AS [R3M FTR%] ---Calculating R3M FTR%
FROM #temp_FlowsFact) AS FACT
--WHERE [Account]='SA11'
UNPIVOT
( [Metric Value] FOR [Metric] IN (
[Issue],[R12M Issues],[R6M Issues],[R3M Issues],[Transfers In],[R12M Transfers In]
,[R6M Transfers In],[R3M Transfers In],[Transfers Out],[R12M Transfers Out],[R6M Transfers Out]
,[R3M Transfers Out],[Return],[R12M Return],[R6M Return],[R3M Return],[FTR%],[R12M FTR%],[R6M FTR%],[R3M FTR%]
)
)AS unpvt
--WHERE [Account] ='AE25' AND [Metric] like '%FTR%'
ORDER BY [Calendar day]
--Creating pricing Buckets
DROP TABLE IF EXISTS #temp_FieldFlowsFact_with_Channels
Create Table #temp_FieldFlowsFact_with_Channels (
[Account] varchar(20),
[Financial year] Varchar (5),
[Period] Varchar(8),
[Period type] Varchar(10),
[Pricing channel] Varchar(20),
[Pricing Category] Varchar(20),
)
INSERT INTO #temp_FieldFlowsFact_with_Channels
SELECT [Account],[Financial year],[Period],
CASE
WHEN [Metric] like '%R12M%' THEN 'R12M'
WHEN [Metric] like '%R6M%' THEN 'R6M'
WHEN [Metric] like '%R3M%' THEN 'R3M'
ELSE 'Periodic'
END AS [Period type]
,CASE
WHEN [Metric] LIKE '%FTR%' AND ROUND([Metric Value],0) BETWEEN 98 AND 100 THEN '98 - 100'
WHEN [Metric] LIKE '%FTR%' AND ROUND([Metric Value],0) BETWEEN 90 AND 97 THEN '90 - 97'
WHEN [Metric] LIKE '%FTR%' AND ROUND([Metric Value],0) BETWEEN 70 AND 89 THEN '70 - 89'
WHEN [Metric] LIKE '%FTR%' AND ROUND([Metric Value],0) BETWEEN 51 AND 69 THEN '51 - 69'
WHEN [Metric] LIKE '%FTR%' AND ROUND([Metric Value],0) BETWEEN 0 AND 50 THEN 'Below 50'
WHEN [Metric] LIKE '%FTR%' AND ROUND([Metric Value],0) >100 THEN 'Above 100'
ELSE 'Uncategorised'
END AS [Pricing channel]
,CASE
WHEN ROUND([Metric Value],0) BETWEEN 98 AND 100 THEN 'E'
WHEN ROUND([Metric Value],0) BETWEEN 90 AND 97 THEN 'D'
WHEN ROUND([Metric Value],0) BETWEEN 70 AND 89 THEN 'C'
WHEN ROUND([Metric Value],0) BETWEEN 51 AND 69 THEN 'B'
WHEN ROUND([Metric Value],0) BETWEEN 0 AND 50 THEN 'A'
WHEN ROUND([Metric Value],0) >100 THEN 'F'
ELSE 'Uncategorised'
END AS [Pricing Category]
FROM #temp_FieldFlowsFact
WHERE [Metric] like '%FTR%%'
ORDER BY [Calendar day]
----------Final Table
--Note that The pricing channel is based on the R6M Rolling FTR
DROP TABLE IF EXISTS #temp_FinalTable
Create Table #temp_FinalTable (
[Account] varchar(20),
[Account Name] varchar(100),
[Reporting Parent] Varchar (100),
[CRM Parent] VARCHAR (100),
[Financial year] Varchar(8),
[Period] Varchar(8),
[Period type] Varchar(10),
[Metric] Varchar (50),
[Metric Value] float,
[Pricing channel] Varchar(20),
[Pricing Category] Varchar(20),
[Weighted Avg Rate] Nvarchar(20)
)
INSERT INTO #temp_FinalTable
SELECT DISTINCT FlowsFact.[Account],Customer.[Account Name],Customer.[Reporting Parent],Customer.[CRM Parent], FlowsFact.[Financial year], FlowsFact.[Period],FlowsFact.[Period type],FlowsFact.[Metric],
FlowsFact.[Metric Value],CHANNEL.[Pricing channel],CHANNEL.[Pricing Category],
CASE
WHEN CHANNEL.[Pricing Category]='E' AND FlowsFact.[Metric]='R12M FTR%' THEN '-'
WHEN CHANNEL.[Pricing Category]='D' AND FlowsFact.[Metric]='R12M FTR%' THEN '1.87'
WHEN CHANNEL.[Pricing Category]='C' AND FlowsFact.[Metric]='R12M FTR%' THEN '5.13'
WHEN CHANNEL.[Pricing Category]='B' AND FlowsFact.[Metric]='R12M FTR%' THEN '10.4'
WHEN CHANNEL.[Pricing Category]='A' AND FlowsFact.[Metric]='R12M FTR%' THEN '21.79'
ELSE '' ------This may change depending on furture reporting requiremnts
END AS [Weighted Avg Rate]
FROM #temp_FieldFlowsFact AS FlowsFact
INNER JOIN
(SELECT * FROM #temp_FieldFlowsFact_with_Channels ) AS CHANNEL ON CHANNEL.[Account]=FlowsFact.[Account]
AND CHANNEL.[Financial year]=FlowsFact.[Financial year] AND CHANNEL.[Period]=FlowsFact.[Period] AND FlowsFact.[Period type]=CHANNEL.[Period type]
INNER JOIN (
SELECT [Account],[CRM Parent],[Reporting Parent],[Account Name]
FROM [Stag].[IMETA - Customer Master Data (ZZ_GL_COBJ_AMR)_Table_SAP BW]
WHERE [Company Code] IN ('AE10','AE20','SA10')
) AS Customer ON FlowsFact.Account=Customer.Account
WHERE FlowsFact.[Financial year]='2024' ---Remove filters for complete datas et
-------------Writing Data into the new Prod Table
DROP TABLE IF EXISTS [Prod].[IMETA_Pricing_Channel_Model_SAP_BW]
CREATE TABLE [Prod].[IMETA_Pricing_Channel_Model_SAP_BW]
(
[Account] varchar(20),
[Account Name] varchar(100),
[CRM Parent] Varchar(100),
[Reporting Parent] Varchar (100),
[Financial year] Varchar(8),
[Period] Varchar(8),
[Period type] Varchar(10),
[Metric] Varchar (50),
[Metric Value] float,
[Pricing channel] Varchar(20),
[Pricing Category] Varchar(20),
[Weighted Avg Rate] Nvarchar(20)
)
INSERT INTO [Prod].[IMETA_Pricing_Channel_Model_SAP_BW]
SELECT [Account]
,[Account Name]
,[CRM Parent]
,[Reporting Parent]
,[Financial year]
,[Period]
,[Period type]
,[Metric]
,[Metric Value]
,[Pricing channel]
,[Pricing Category]
,[Weighted Avg Rate]
FROM #temp_FinalTable
--SELECT * FROM [Prod].[IMETA_Pricing_Channel_Model_SAP_BW] WHERE [Period]='P12'
-------------------------------------------------------------------------------------------------------
END