public inbox for [email protected]
help / color / mirror / Atom feedTSQL To Postgres - Unpivot/Union All
8+ messages / 4 participants
[nested] [flat]
* TSQL To Postgres - Unpivot/Union All
@ 2023-07-20 11:29 Anthony Apollis <[email protected]>
2023-07-20 12:17 ` Fwd: TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Anthony Apollis @ 2023-07-20 11:29 UTC (permalink / raw)
To: [email protected]
I am trying to convert this TSQL:
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]
To Postgres. The above code gives outputs for columns:
[image: image.png]
The Postgres i used: 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 BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Issues",
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day"
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Issues",
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day"
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Issues",
"Transfers In",
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar
day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Transfers In",
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar
day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers In",
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar
day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers In",
"Transfers Out",
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M
Transfers Out",
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers
Out",
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers
Out",
"Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day"
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day"
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day"
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Return",
ROUND(COALESCE(("Transfers Out" + "Return") / NULLIF(("Issue" +
"Transfers In"), 0), 0) * 100, 2) AS "FTR%",
ROUND(COALESCE((
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) +
SUM("Return") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
) /
NULLIF((
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) +
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
), 0), 0) * 100, 2) AS "R12M FTR%",
ROUND(COALESCE((
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) +
SUM("Return") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
) /
NULLIF((
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) +
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
), 0), 0) * 100, 2) AS "R6M FTR%",
ROUND(COALESCE((
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) +
SUM("Return") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
) /
NULLIF((
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) +
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
), 0), 0) * 100, 2) AS "R3M FTR%"
FROM temp_FlowsFact
) AS FACT
UNION ALL
SELECT "Account", "Calendar day", "Financial year", "Period",
CASE
WHEN "Metric" = 'Issue' THEN 'Issue'
WHEN "Metric" = 'R12M Issues' THEN 'R12M Issues'
WHEN "Metric" = 'R6M Issues' THEN 'R6M Issues'
WHEN "Metric" = 'R3M Issues' THEN 'R3M Issues'
WHEN "Metric" = 'Transfers In' THEN 'Transfers In'
WHEN "Metric" = 'R12M Transfers In' THEN 'R12M Transfers In'
WHEN "Metric" = 'R6M Transfers In' THEN 'R6M Transfers In'
WHEN "Metric" = 'R3M Transfers In' THEN 'R3M Transfers In'
WHEN "Metric" = 'Transfers Out' THEN 'Transfers Out'
WHEN "Metric" = 'R12M Transfers Out' THEN 'R12M Transfers Out'
WHEN "Metric" = 'R6M Transfers Out' THEN 'R6M Transfers Out'
WHEN "Metric" = 'R3M Transfers Out' THEN 'R3M Transfers Out'
WHEN "Metric" = 'Return' THEN 'Return'
WHEN "Metric" = 'R12M Return' THEN 'R12M Return'
WHEN "Metric" = 'R6M Return' THEN 'R6M Return'
WHEN "Metric" = 'R3M Return' THEN 'R3M Return'
WHEN "Metric" = 'FTR%' THEN 'FTR%'
WHEN "Metric" = 'R12M FTR%' THEN 'R12M FTR%'
WHEN "Metric" = 'R6M FTR%' THEN 'R6M FTR%'
WHEN "Metric" = 'R3M FTR%' THEN 'R3M FTR%'
END AS "Period type",
"Metric Value", "Metric"
FROM temp_FlowsFact
WHERE "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%'
)
ORDER BY "Calendar day";
Does not give me outputs for columns: Metric and Pricing Channel:
[image: image.png]
What am i doing wrong? Please assist?
Attachments:
[image/png] image.png (94.1K, 3-image.png)
download | view image
[image/png] image.png (66.2K, 4-image.png)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Fwd: TSQL To Postgres - Unpivot/Union All
2023-07-20 11:29 TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
@ 2023-07-20 12:17 ` Anthony Apollis <[email protected]>
2023-07-20 12:48 ` Re: TSQL To Postgres - Unpivot/Union All David G. Johnston <[email protected]>
2023-07-20 13:58 ` Re: TSQL To Postgres - Unpivot/Union All Geoff Winkless <[email protected]>
2023-07-20 15:07 ` Re: Fwd: TSQL To Postgres - Unpivot/Union All Thomas Kellerer <[email protected]>
0 siblings, 3 replies; 8+ messages in thread
From: Anthony Apollis @ 2023-07-20 12:17 UTC (permalink / raw)
To: [email protected]
I am trying to convert this TSQL:
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]
To Postgres. The above code gives outputs for columns:
[image: image.png]
The Postgres i used: 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 BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Issues",
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day"
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Issues",
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day"
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Issues",
"Transfers In",
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar
day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Transfers In",
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar
day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers In",
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar
day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers In",
"Transfers Out",
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M
Transfers Out",
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers
Out",
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers
Out",
"Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day"
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day"
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day"
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Return",
ROUND(COALESCE(("Transfers Out" + "Return") / NULLIF(("Issue" +
"Transfers In"), 0), 0) * 100, 2) AS "FTR%",
ROUND(COALESCE((
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) +
SUM("Return") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
) /
NULLIF((
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) +
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
), 0), 0) * 100, 2) AS "R12M FTR%",
ROUND(COALESCE((
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) +
SUM("Return") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
) /
NULLIF((
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) +
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
), 0), 0) * 100, 2) AS "R6M FTR%",
ROUND(COALESCE((
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) +
SUM("Return") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
) /
NULLIF((
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) +
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY
"Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
), 0), 0) * 100, 2) AS "R3M FTR%"
FROM temp_FlowsFact
) AS FACT
UNION ALL
SELECT "Account", "Calendar day", "Financial year", "Period",
CASE
WHEN "Metric" = 'Issue' THEN 'Issue'
WHEN "Metric" = 'R12M Issues' THEN 'R12M Issues'
WHEN "Metric" = 'R6M Issues' THEN 'R6M Issues'
WHEN "Metric" = 'R3M Issues' THEN 'R3M Issues'
WHEN "Metric" = 'Transfers In' THEN 'Transfers In'
WHEN "Metric" = 'R12M Transfers In' THEN 'R12M Transfers In'
WHEN "Metric" = 'R6M Transfers In' THEN 'R6M Transfers In'
WHEN "Metric" = 'R3M Transfers In' THEN 'R3M Transfers In'
WHEN "Metric" = 'Transfers Out' THEN 'Transfers Out'
WHEN "Metric" = 'R12M Transfers Out' THEN 'R12M Transfers Out'
WHEN "Metric" = 'R6M Transfers Out' THEN 'R6M Transfers Out'
WHEN "Metric" = 'R3M Transfers Out' THEN 'R3M Transfers Out'
WHEN "Metric" = 'Return' THEN 'Return'
WHEN "Metric" = 'R12M Return' THEN 'R12M Return'
WHEN "Metric" = 'R6M Return' THEN 'R6M Return'
WHEN "Metric" = 'R3M Return' THEN 'R3M Return'
WHEN "Metric" = 'FTR%' THEN 'FTR%'
WHEN "Metric" = 'R12M FTR%' THEN 'R12M FTR%'
WHEN "Metric" = 'R6M FTR%' THEN 'R6M FTR%'
WHEN "Metric" = 'R3M FTR%' THEN 'R3M FTR%'
END AS "Period type",
"Metric Value", "Metric"
FROM temp_FlowsFact
WHERE "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%'
)
ORDER BY "Calendar day";
Does not give me outputs for columns: Metric and Pricing Channel:
[image: image.png]
What am i doing wrong? Please assist?
Attachments:
[image/png] image.png (94.1K, 3-image.png)
download | view image
[image/png] image.png (66.2K, 4-image.png)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: TSQL To Postgres - Unpivot/Union All
2023-07-20 11:29 TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
2023-07-20 12:17 ` Fwd: TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
@ 2023-07-20 12:48 ` David G. Johnston <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2023-07-20 12:48 UTC (permalink / raw)
To: Anthony Apollis <[email protected]>; +Cc: [email protected] <[email protected]>
On Thursday, July 20, 2023, Anthony Apollis <[email protected]>
wrote:
>
>
> I am trying to convert this TSQL:
>
This doesn’t really seem relevant all that relevant at this point. Given
some input data either the query produces the expected result or it doesn’t.
>
> What am i doing wrong? Please assist?
>
You need to provide more info - specifically the input data mentioned
above. Try making a reproducer on this fiddle site.
https://dbfiddle.uk/btGcOH30
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: TSQL To Postgres - Unpivot/Union All
2023-07-20 11:29 TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
2023-07-20 12:17 ` Fwd: TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
@ 2023-07-20 13:58 ` Geoff Winkless <[email protected]>
2023-07-20 14:28 ` Fwd: TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
2 siblings, 1 reply; 8+ messages in thread
From: Geoff Winkless @ 2023-07-20 13:58 UTC (permalink / raw)
To: Anthony Apollis <[email protected]>; +Cc: [email protected]
On Thu, 20 Jul 2023 at 13:17, Anthony Apollis <[email protected]>
wrote:
> The Postgres i used: INSERT INTO temp_FieldFlowsFact
> SELECT "Account", "Calendar day", "Financial year", "Period",
>
> [snip]
At the very least, include a column list in your INSERT statement. We have
no way of checking where any of your results are going.
Geoff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Fwd: TSQL To Postgres - Unpivot/Union All
2023-07-20 11:29 TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
2023-07-20 12:17 ` Fwd: TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
2023-07-20 13:58 ` Re: TSQL To Postgres - Unpivot/Union All Geoff Winkless <[email protected]>
@ 2023-07-20 14:28 ` Anthony Apollis <[email protected]>
2023-07-20 14:51 ` Re: TSQL To Postgres - Unpivot/Union All Geoff Winkless <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Anthony Apollis @ 2023-07-20 14:28 UTC (permalink / raw)
To: [email protected]
---------- Forwarded message ---------
From: Anthony Apollis <[email protected]>
Date: Thu, 20 Jul 2023 at 16:07
Subject: Re: TSQL To Postgres - Unpivot/Union All
To: Geoff Winkless <[email protected]>, David G. Johnston <
[email protected]>
I am attaching my TSQL and Postgres SQL:
On Thu, 20 Jul 2023 at 15:58, Geoff Winkless <[email protected]> wrote:
> On Thu, 20 Jul 2023 at 13:17, Anthony Apollis <[email protected]>
> wrote:
>
>> The Postgres i used: INSERT INTO temp_FieldFlowsFact
>> SELECT "Account", "Calendar day", "Financial year", "Period",
>>
>> [snip]
>
> At the very least, include a column list in your INSERT statement. We have
> no way of checking where any of your results are going.
>
> Geoff
>
-- CREATING A TEMPORAL TABLE WHERE WE APPLY THE GLOBLY METHODOLOGY OF REMOVING INTERNAL TRANSFERS
CREATE TABLE temp.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
);
Without leading zero’s
“-- Insert into temp_Flows
INSERT INTO temp.temp_Flows
SELECT DISTINCT
TO_CHAR(TO_DATE("Fiscal year period", 'MM.YYYY'), 'FMmm.YYYY') AS "Fiscal year period",
"Company Code",
"Account",
"Generic Material",
COALESCE(SUM(CASE WHEN "Metric" IN ('s (in)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Issue",
COALESCE(SUM(CASE WHEN "Metric" IN ('tDD (in)', 'tDE (in)', 'tED (in)', 'tEE (in)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Transfers In",
COALESCE(SUM(CASE WHEN "Metric" IN ('tDD (Out)', 'tDE (Out)', 'tED (Out)', 'tEE (Out)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Transfers Out",
COALESCE(SUM(CASE WHEN "Metric" IN ('r (out)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Return"
FROM (
SELECT
"Fiscal year period",
"Company Code",
"Contra Account",
"Account",
"Generic Material",
"Metric",
COALESCE("Metric Value", 0) AS "Metric Value"
FROM model."IMETA_Global_Field_Flows_Full_dataset_(Client)_TA_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",
COALESCE("Metric Value", 0) AS "Metric Value"
FROM model."IMETA_Global_Field_Flows_Full_dataset_(Client)_TA_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))')
) AS Table_1
WHERE "Company Code" IN ('AE10', 'AE20', 'SA10');”
-- Insert into temp_Flows
INSERT INTO temp.temp_Flows
SELECT DISTINCT
"Fiscal year period",
"Company Code",
"Account",
"Generic Material",
COALESCE(SUM(CASE WHEN "Metric" IN ('s (in)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Issue",
COALESCE(SUM(CASE WHEN "Metric" IN ('tDD (in)', 'tDE (in)', 'tED (in)', 'tEE (in)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Transfers In",
COALESCE(SUM(CASE WHEN "Metric" IN ('tDD (Out)', 'tDE (Out)', 'tED (Out)', 'tEE (Out)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Transfers Out",
COALESCE(SUM(CASE WHEN "Metric" IN ('r (out)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Return"
FROM (
SELECT
"Fiscal year period",
"Company Code",
"Contra Account",
"Account",
"Generic Material",
"Metric",
COALESCE("Metric Value", 0) AS "Metric Value"
FROM model."IMETA_Global_Field_Flows_Full_dataset_(Client)_TA_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",
COALESCE("Metric Value", 0) AS "Metric Value"
FROM model."IMETA_Global_Field_Flows_Full_dataset_(Client)_TA_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))')
) AS Table_1
WHERE "Company Code" IN ('AE10', 'AE20', 'SA10');
-------
--CREATE TABLE "temp.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
);
-- Table: temp.temp_flowsfact
-- DROP TABLE IF EXISTS temp.temp_flowsfact;
CREATE TABLE IF NOT EXISTS temp.temp_flowsfact
(
"Fiscal year period" character varying(20) COLLATE pg_catalog."default",
"Calendar_day" date,
"Financial year" character varying(5) COLLATE pg_catalog."default",
"Period" character varying(8) COLLATE pg_catalog."default",
"Account" character varying(20) COLLATE pg_catalog."default",
"Issue" double precision,
"Transfers In" double precision,
"Transfers Out" double precision,
"Return" double precision
);
INSERT INTO temp.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.temp_flows AS FLOWS
-- Bringing in Issue fact
LEFT OUTER JOIN (
SELECT "Account", "Fiscal year period", COALESCE(SUM("Issue"), 0) AS issues
FROM temp.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"
-- Bringing in Transfers In fact
LEFT OUTER JOIN (
SELECT "Account", "Fiscal year period", COALESCE(SUM("Transfers In"), 0) AS "Transfers In"
FROM temp.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"
-- Bringing in Transfers out fact
LEFT OUTER JOIN (
SELECT "Account", "Fiscal year period", COALESCE(SUM("Transfers Out"), 0) AS "Transfers Out"
FROM temp.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"
-- Bringing in return fact
LEFT OUTER JOIN (
SELECT "Account", "Fiscal year period", COALESCE(SUM("Return"), 0) AS "return"
FROM temp.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"
-- 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",
(DATE_PART('day', FIRST_VALUE("date") OVER (PARTITION BY "fy", "period" ORDER BY "date" DESC)) - DATE_PART('day', FIRST_VALUE("date") OVER (PARTITION BY "fy", "period" ORDER BY "date"))) + 1 AS "number of days"
FROM
prod."IMETA - Calendar Days Data_View_Other"
) 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"
) AS FlowsFact;
INSERT INTO temp.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",
"FTR%" AS "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 BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Issues",
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Issues",
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Issues",
"Transfers_In",
SUM("Transfers_In") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Transfers In",
SUM("Transfers_In") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers In",
SUM("Transfers_In") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers In",
"Transfers_Out",
SUM("Transfers_Out") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Transfers Out",
SUM("Transfers_Out") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers Out",
SUM("Transfers_Out") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers Out",
"Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Return",
-- Prevent division by zero
CASE
WHEN ("Issue" + "Transfers_In") = 0 THEN 0
ELSE ROUND((("Transfers_Out" + "Return") / ("Issue" + "Transfers_In")::NUMERIC) * 100)::NUMERIC(10,2)
END AS "FTR%",
NULL AS "Metric" -- Replace NULL with the appropriate calculation or column reference
FROM temp.temp_flowsfact
) AS FACT;
INSERT INTO temp.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"::numeric, 0) BETWEEN 98 AND 100 THEN '98 - 100'
WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 90 AND 97 THEN '90 - 97'
WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 70 AND 89 THEN '70 - 89'
WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 51 AND 69 THEN '51 - 69'
WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 0 AND 50 THEN 'Below 50'
WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) > 100 THEN 'Above 100'
WHEN "Metric" IS NULL THEN 'Uncategorised' -- Include null values in the "Metric" column
ELSE 'Uncategorised'
END AS "Pricing channel",
CASE
WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 98 AND 100 THEN 'E'
WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 90 AND 97 THEN 'D'
WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 70 AND 89 THEN 'C'
WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 51 AND 69 THEN 'B'
WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 0 AND 50 THEN 'A'
WHEN ROUND("Metric Value"::numeric, 0) > 100 THEN 'F'
WHEN "Metric" IS NULL THEN 'Uncategorised' -- Include null values in the "Metric" column
ELSE 'Uncategorised'
END AS "Pricing Category"
FROM temp.temp_fieldflowsfact
WHERE "Metric" LIKE '%FTR%' OR "Metric" IS NULL -- Include rows with null values in the "Metric" column
ORDER BY "Calendar day";
INSERT INTO temp.temp_FinalTable
SELECT DISTINCT
FlowsFact."Account",
Customer."Account Name",
Customer."Level 4 Name" AS "Reporting Parent",
Customer."Level 3 Name" AS "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 future reporting requirements
END AS "Weighted Avg Rate"
FROM
temp.temp_fieldflowsfact AS FlowsFact
INNER JOIN (
SELECT
account,
"Account Name",
"Level 4 Name",
"Level 3 Name"
FROM
prod."imeta - customer daily issues and returns analysis_model_sap bw"
WHERE
"Company Code" IN ('AE10', 'AE20', 'SA10')
) AS Customer ON FlowsFact."Account" = Customer.account
INNER JOIN temp.temp_fieldflowsfact_with_channels AS CHANNEL ON CHANNEL."Account" = FlowsFact."Account"
AND CHANNEL."Financial year" = FlowsFact."Financial year"
AND CHANNEL."Period" = FlowsFact."Period"
AND CHANNEL."Period type" = FlowsFact."Period type"
WHERE
FlowsFact."Financial year" = '2024'; -- Remove filter
Attachments:
[text/plain] posgresto.txt (14.6K, 3-posgresto.txt)
download | inline:
-- CREATING A TEMPORAL TABLE WHERE WE APPLY THE GLOBLY METHODOLOGY OF REMOVING INTERNAL TRANSFERS
CREATE TABLE temp.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
);
Without leading zero’s
“-- Insert into temp_Flows
INSERT INTO temp.temp_Flows
SELECT DISTINCT
TO_CHAR(TO_DATE("Fiscal year period", 'MM.YYYY'), 'FMmm.YYYY') AS "Fiscal year period",
"Company Code",
"Account",
"Generic Material",
COALESCE(SUM(CASE WHEN "Metric" IN ('s (in)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Issue",
COALESCE(SUM(CASE WHEN "Metric" IN ('tDD (in)', 'tDE (in)', 'tED (in)', 'tEE (in)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Transfers In",
COALESCE(SUM(CASE WHEN "Metric" IN ('tDD (Out)', 'tDE (Out)', 'tED (Out)', 'tEE (Out)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Transfers Out",
COALESCE(SUM(CASE WHEN "Metric" IN ('r (out)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Return"
FROM (
SELECT
"Fiscal year period",
"Company Code",
"Contra Account",
"Account",
"Generic Material",
"Metric",
COALESCE("Metric Value", 0) AS "Metric Value"
FROM model."IMETA_Global_Field_Flows_Full_dataset_(Client)_TA_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",
COALESCE("Metric Value", 0) AS "Metric Value"
FROM model."IMETA_Global_Field_Flows_Full_dataset_(Client)_TA_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))')
) AS Table_1
WHERE "Company Code" IN ('AE10', 'AE20', 'SA10');”
-- Insert into temp_Flows
INSERT INTO temp.temp_Flows
SELECT DISTINCT
"Fiscal year period",
"Company Code",
"Account",
"Generic Material",
COALESCE(SUM(CASE WHEN "Metric" IN ('s (in)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Issue",
COALESCE(SUM(CASE WHEN "Metric" IN ('tDD (in)', 'tDE (in)', 'tED (in)', 'tEE (in)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Transfers In",
COALESCE(SUM(CASE WHEN "Metric" IN ('tDD (Out)', 'tDE (Out)', 'tED (Out)', 'tEE (Out)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Transfers Out",
COALESCE(SUM(CASE WHEN "Metric" IN ('r (out)') THEN "Metric Value" END) OVER (PARTITION BY "Account", "Company Code", "Generic Material", "Fiscal year period", "Metric"), 0) AS "Return"
FROM (
SELECT
"Fiscal year period",
"Company Code",
"Contra Account",
"Account",
"Generic Material",
"Metric",
COALESCE("Metric Value", 0) AS "Metric Value"
FROM model."IMETA_Global_Field_Flows_Full_dataset_(Client)_TA_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",
COALESCE("Metric Value", 0) AS "Metric Value"
FROM model."IMETA_Global_Field_Flows_Full_dataset_(Client)_TA_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))')
) AS Table_1
WHERE "Company Code" IN ('AE10', 'AE20', 'SA10');
-------
--CREATE TABLE "temp.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
);
-- Table: temp.temp_flowsfact
-- DROP TABLE IF EXISTS temp.temp_flowsfact;
CREATE TABLE IF NOT EXISTS temp.temp_flowsfact
(
"Fiscal year period" character varying(20) COLLATE pg_catalog."default",
"Calendar_day" date,
"Financial year" character varying(5) COLLATE pg_catalog."default",
"Period" character varying(8) COLLATE pg_catalog."default",
"Account" character varying(20) COLLATE pg_catalog."default",
"Issue" double precision,
"Transfers In" double precision,
"Transfers Out" double precision,
"Return" double precision
);
INSERT INTO temp.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.temp_flows AS FLOWS
-- Bringing in Issue fact
LEFT OUTER JOIN (
SELECT "Account", "Fiscal year period", COALESCE(SUM("Issue"), 0) AS issues
FROM temp.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"
-- Bringing in Transfers In fact
LEFT OUTER JOIN (
SELECT "Account", "Fiscal year period", COALESCE(SUM("Transfers In"), 0) AS "Transfers In"
FROM temp.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"
-- Bringing in Transfers out fact
LEFT OUTER JOIN (
SELECT "Account", "Fiscal year period", COALESCE(SUM("Transfers Out"), 0) AS "Transfers Out"
FROM temp.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"
-- Bringing in return fact
LEFT OUTER JOIN (
SELECT "Account", "Fiscal year period", COALESCE(SUM("Return"), 0) AS "return"
FROM temp.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"
-- 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",
(DATE_PART('day', FIRST_VALUE("date") OVER (PARTITION BY "fy", "period" ORDER BY "date" DESC)) - DATE_PART('day', FIRST_VALUE("date") OVER (PARTITION BY "fy", "period" ORDER BY "date"))) + 1 AS "number of days"
FROM
prod."IMETA - Calendar Days Data_View_Other"
) 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"
) AS FlowsFact;
INSERT INTO temp.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",
"FTR%" AS "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 BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Issues",
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Issues",
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Issues",
"Transfers_In",
SUM("Transfers_In") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Transfers In",
SUM("Transfers_In") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers In",
SUM("Transfers_In") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers In",
"Transfers_Out",
SUM("Transfers_Out") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Transfers Out",
SUM("Transfers_Out") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers Out",
SUM("Transfers_Out") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers Out",
"Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar_day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Return",
-- Prevent division by zero
CASE
WHEN ("Issue" + "Transfers_In") = 0 THEN 0
ELSE ROUND((("Transfers_Out" + "Return") / ("Issue" + "Transfers_In")::NUMERIC) * 100)::NUMERIC(10,2)
END AS "FTR%",
NULL AS "Metric" -- Replace NULL with the appropriate calculation or column reference
FROM temp.temp_flowsfact
) AS FACT;
INSERT INTO temp.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"::numeric, 0) BETWEEN 98 AND 100 THEN '98 - 100'
WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 90 AND 97 THEN '90 - 97'
WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 70 AND 89 THEN '70 - 89'
WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 51 AND 69 THEN '51 - 69'
WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) BETWEEN 0 AND 50 THEN 'Below 50'
WHEN "Metric" LIKE '%FTR%' AND ROUND("Metric Value"::numeric, 0) > 100 THEN 'Above 100'
WHEN "Metric" IS NULL THEN 'Uncategorised' -- Include null values in the "Metric" column
ELSE 'Uncategorised'
END AS "Pricing channel",
CASE
WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 98 AND 100 THEN 'E'
WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 90 AND 97 THEN 'D'
WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 70 AND 89 THEN 'C'
WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 51 AND 69 THEN 'B'
WHEN ROUND("Metric Value"::numeric, 0) BETWEEN 0 AND 50 THEN 'A'
WHEN ROUND("Metric Value"::numeric, 0) > 100 THEN 'F'
WHEN "Metric" IS NULL THEN 'Uncategorised' -- Include null values in the "Metric" column
ELSE 'Uncategorised'
END AS "Pricing Category"
FROM temp.temp_fieldflowsfact
WHERE "Metric" LIKE '%FTR%' OR "Metric" IS NULL -- Include rows with null values in the "Metric" column
ORDER BY "Calendar day";
INSERT INTO temp.temp_FinalTable
SELECT DISTINCT
FlowsFact."Account",
Customer."Account Name",
Customer."Level 4 Name" AS "Reporting Parent",
Customer."Level 3 Name" AS "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 future reporting requirements
END AS "Weighted Avg Rate"
FROM
temp.temp_fieldflowsfact AS FlowsFact
INNER JOIN (
SELECT
account,
"Account Name",
"Level 4 Name",
"Level 3 Name"
FROM
prod."imeta - customer daily issues and returns analysis_model_sap bw"
WHERE
"Company Code" IN ('AE10', 'AE20', 'SA10')
) AS Customer ON FlowsFact."Account" = Customer.account
INNER JOIN temp.temp_fieldflowsfact_with_channels AS CHANNEL ON CHANNEL."Account" = FlowsFact."Account"
AND CHANNEL."Financial year" = FlowsFact."Financial year"
AND CHANNEL."Period" = FlowsFact."Period"
AND CHANNEL."Period type" = FlowsFact."Period type"
WHERE
FlowsFact."Financial year" = '2024'; -- Remove filter
[application/sql] Channel FTR.sql (14.7K, 4-Channel%20FTR.sql)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: TSQL To Postgres - Unpivot/Union All
2023-07-20 11:29 TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
2023-07-20 12:17 ` Fwd: TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
2023-07-20 13:58 ` Re: TSQL To Postgres - Unpivot/Union All Geoff Winkless <[email protected]>
2023-07-20 14:28 ` Fwd: TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
@ 2023-07-20 14:51 ` Geoff Winkless <[email protected]>
2023-07-20 15:47 ` Re: TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Geoff Winkless @ 2023-07-20 14:51 UTC (permalink / raw)
To: Anthony Apollis <[email protected]>; +Cc: [email protected]
On Thu, 20 Jul 2023 at 15:28, Anthony Apollis <[email protected]> wrote:
> I am attaching my TSQL and Postgres SQL:
You're still missing some CREATEs, for example for temp_FieldFlowsFact.
Even assuming your columns list is correct, I would still (and as a
matter of habit) include the target column list in your INSERT
statements, if only to make your own life easier, but especially
because any later changes to the tables could end up shifting the
position of the columns.
Certainly it will be easier for anyone else trying to disentangle the SQL later.
As an aside, can you clarify whether you mean temporal tables (and are
you using a temporal tables extension)? Or should that read
"temporary"?
Geoff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: TSQL To Postgres - Unpivot/Union All
2023-07-20 11:29 TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
2023-07-20 12:17 ` Fwd: TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
2023-07-20 13:58 ` Re: TSQL To Postgres - Unpivot/Union All Geoff Winkless <[email protected]>
2023-07-20 14:28 ` Fwd: TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
2023-07-20 14:51 ` Re: TSQL To Postgres - Unpivot/Union All Geoff Winkless <[email protected]>
@ 2023-07-20 15:47 ` Anthony Apollis <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Anthony Apollis @ 2023-07-20 15:47 UTC (permalink / raw)
To: Geoff Winkless <[email protected]>; +Cc: [email protected]
CREATE TEMP TABLE temp_FieldFlowsFact (
Account varchar(20),
"Calendar day" DATE,
"Financial year" varchar(5),
Period varchar(8),
"Period type" varchar(10),
Metric varchar(50),
"Metric Value" float
)
It was created as a temp table in SQLServer, but it does not have to be in
Postgres. Thanks
On Thu, 20 Jul 2023 at 16:51, Geoff Winkless <[email protected]> wrote:
> On Thu, 20 Jul 2023 at 15:28, Anthony Apollis <[email protected]>
> wrote:
> > I am attaching my TSQL and Postgres SQL:
>
> You're still missing some CREATEs, for example for temp_FieldFlowsFact.
>
> Even assuming your columns list is correct, I would still (and as a
> matter of habit) include the target column list in your INSERT
> statements, if only to make your own life easier, but especially
> because any later changes to the tables could end up shifting the
> position of the columns.
>
> Certainly it will be easier for anyone else trying to disentangle the SQL
> later.
>
> As an aside, can you clarify whether you mean temporal tables (and are
> you using a temporal tables extension)? Or should that read
> "temporary"?
>
> Geoff
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Fwd: TSQL To Postgres - Unpivot/Union All
2023-07-20 11:29 TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
2023-07-20 12:17 ` Fwd: TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
@ 2023-07-20 15:07 ` Thomas Kellerer <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Thomas Kellerer @ 2023-07-20 15:07 UTC (permalink / raw)
To: [email protected]
Anthony Apollis schrieb am 20.07.2023 um 14:17:
> Does not give me outputs for columns: Metric and Pricing Channel:
> image.png
>
> What am i doing wrong? Please assist?
UNPIVOT can be done using a LATERAL sub-query with a VALUES clause:
https://blog.sql-workbench.eu/post/unpivot-with-postgres/
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-07-20 15:47 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-20 11:29 TSQL To Postgres - Unpivot/Union All Anthony Apollis <[email protected]>
2023-07-20 12:17 ` Anthony Apollis <[email protected]>
2023-07-20 12:48 ` David G. Johnston <[email protected]>
2023-07-20 13:58 ` Geoff Winkless <[email protected]>
2023-07-20 14:28 ` Anthony Apollis <[email protected]>
2023-07-20 14:51 ` Geoff Winkless <[email protected]>
2023-07-20 15:47 ` Anthony Apollis <[email protected]>
2023-07-20 15:07 ` Thomas Kellerer <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox