Re: Code does Not Read in FY 2025 Data

2024-07-16 Thread Anthony Apollis 
That very same code is picking up all the data in the Original Table. Which
only contains past data.

On Tue, 16 Jul 2024 at 17:39, Adrian Klaver 
wrote:

> On 7/16/24 08:28, Anthony Apollis wrote:
> > Only data up until 2024 is picked up in Revised table, whic contains
> > 2025 data. THe Maxdate calculation seems to be the problem.
>
> This:
>
> 'Only data up until 2024 is picked up in Revised table, whic contains
> 2025 data. .."
>
> is consistent with:
>
> MaxDate AS (
>  SELECT
>  MAX("Date") AS "MaxDate"
>  FROM
>  FullWeeks
>  WHERE
>  "Date" < CURRENT_DATE
> )
>
> I don't see how you expect to fetch data from the future when you limit
> the data to the past.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Code does Not Read in FY 2025 Data

2024-07-16 Thread Anthony Apollis 
The Calendar Tables should adhere to this business rule/calendar. Original
Table seem to be correct.
Reporting Month FY22 FY23 FY24 FY25 FY26 FY27
Period End Calendar Days Period End Calendar Days Period End Calendar
Days Period
End Calendar Days Period End Calendar Days Period End Calendar Days
P1 31-Jul 2021  (Sat) 31 30-Jul 2022  (Sat) 30 05-Aug 2023  (Sat) 36 03-Aug
2024  (Sat) 34 02-Aug 2025  (Sat) 33 01-Aug 2026  (Sat) 32
P2 28-Aug 2021  (Sat) 28 27-Aug 2022  (Sat) 28 02-Sep 2023  (Sat) 28 31-Aug
2024  (Sat) 28 30-Aug 2025  (Sat) 28 29-Aug 2026  (Sat) 28
P3 25-Sep 2021  (Sat) 28 24-Sep 2022  (Sat) 28 30-Sep 2023  (Sat) 28 28-Sep
2024  (Sat) 28 27-Sep 2025  (Sat) 28 26-Sep 2026  (Sat) 28
P4 30-Oct 2021  (Sat) 35 29-Oct 2022  (Sat) 35 04-Nov 2023  (Sat) 35 02-Nov
2024  (Sat) 35 01-Nov 2025  (Sat) 35 31-Oct 2026  (Sat) 35
P5 27-Nov 2021  (Sat) 28 26-Nov 2022  (Sat) 28 02-Dec 2023  (Sat) 28 30-Nov
2024  (Sat) 28 29-Nov 2025  (Sat) 28 28-Nov 2026  (Sat) 28
P6 31-Dec 2021  (Fri) 34 31-Dec 2022  (Sat) 35 31-Dec 2023  (Sun) 29 31-Dec
2024  (Tue) 31 31-Dec 2025  (Wed) 32 31-Dec 2026  (Thu) 33
P7 29-Jan 2022  (Sat) 29 04-Feb 2023  (Sat) 35 03-Feb 2024  (Sat) 34 01-Feb
2025  (Sat) 32 31-Jan 2026  (Sat) 31 30-Jan 2027  (Sat) 30
P8 26-Feb 2022  (Sat) 28 04-Mar 2023  (Sat) 28 02-Mar 2024  (Sat) 28 01-Mar
2025  (Sat) 28 28-Feb 2026  (Sat) 28 27-Feb 2027  (Sat) 28
P9 26-Mar 2022  (Sat) 28 01-Apr 2023  (Sat) 28 30-Mar 2024  (Sat) 28 29-Mar
2025  (Sat) 28 28-Mar 2026  (Sat) 28 27-Mar 2027  (Sat) 28
P10 30-Apr 2022  (Sat) 35 06-May 2023  (Sat) 35 04-May 2024  (Sat) 35 03-May
2025  (Sat) 35 02-May 2026  (Sat) 35 01-May 2027  (Sat) 35
P11 28-May 2022  (Sat) 28 03-Jun 2023  (Sat) 28 01-Jun 2024  (Sat) 28 31-May
2025  (Sat) 28 30-May 2026  (Sat) 28 29-May 2027  (Sat) 28
P12 30-Jun 2022  (Thu) 33 30-Jun 2023  (Fri) 27 30-Jun 2024  (Sun) 29 30-Jun
2025  (Mon) 30 30-Jun 2026  (Tue) 31 30-Jun 2027  (Wed) 32
Total Fiscal Year Days 365 365 366 365 365 365
Day 1 of Fiscal year: 30-Jun 2021  (Wed) 30-Jun 2022  (Thu) 30-Jun 2023
(Fri) 30-Jun 2024  (Sun) 30-Jun 2025  (Mon) 30-Jun 2026  (Tue)

On Tue, 16 Jul 2024 at 17:28, Anthony Apollis 
wrote:

> Only data up until 2024 is picked up in Revised table, whic contains 2025
> data. THe Maxdate calculation seems to be the problem.
> -- Step 1: Define the Fiscal Calendar
> WITH FiscalCalendar AS (
> SELECT 'FY25' AS FY, 'P1' AS Period, '2024-07-01'::date AS
> PeriodStart, '2024-08-03'::date AS PeriodEnd
> UNION ALL
> SELECT 'FY25', 'P2', '2024-08-04'::date, '2024-08-31'::date
> UNION ALL
> SELECT 'FY25', 'P3', '2024-09-01'::date, '2024-09-28'::date
> UNION ALL
> SELECT 'FY25', 'P4', '2024-09-29'::date, '2024-11-02'::date
> UNION ALL
> SELECT 'FY25', 'P5', '2024-11-03'::date, '2024-11-30'::date
> UNION ALL
> SELECT 'FY25', 'P6', '2024-12-01'::date, '2024-12-31'::date
> UNION ALL
> SELECT 'FY25', 'P7', '2025-01-01'::date, '2025-02-01'::date
> UNION ALL
> SELECT 'FY25', 'P8', '2025-02-02'::date, '2025-03-01'::date
> UNION ALL
> SELECT 'FY25', 'P9', '2025-03-02'::date, '2025-03-29'::date
> UNION ALL
> SELECT 'FY25', 'P10', '2025-03-30'::date, '2025-05-03'::date
> UNION ALL
> SELECT 'FY25', 'P11', '2025-05-04'::date, '2025-05-31'::date
> UNION ALL
> SELECT 'FY25', 'P12', '2025-06-01'::date, '2025-06-30'::date
> ),
>
> -- Step 2: Get Calendar Data with full week details
> CalendarData AS (
> SELECT DISTINCT
> c."Date",
> EXTRACT(WEEK FROM c."Date") AS "WeekNumber",
> f.PeriodEnd,
> COUNT(c."Date") OVER (PARTITION BY EXTRACT(WEEK FROM c."Date")
> ORDER BY c."Date") AS "NumberOfDays",
> ROW_NUMBER() OVER (PARTITION BY EXTRACT(WEEK FROM c."Date") ORDER
> BY c."Date") AS "DayNumberInWeek"
> FROM
> "Prod"."IMETA - Calendar Days Data_Table_Temp_Copy" c
> INNER JOIN
> FiscalCalendar f ON c."Date" BETWEEN f.PeriodStart AND f.PeriodEnd
> AND c."FY" = f.FY
> WHERE
> c."FY" = 'FY25'
> ),
>
> -- Step 3: Filter to get full weeks only
> FullWeeks AS (
> SELECT
> "Date",
> "WeekNumber",
> "PeriodEnd",
> "NumberOfDays",
> "DayNumberInWeek"
> FROM
> CalendarData
> WHERE
> "NumberOfDays" = 7
> ),
>
> -- Step 4: Get the maximum date from full weeks that is before today
> MaxDate AS (
> SELECT
> MAX("Date") AS "MaxDate"
> FROM
> FullWeeks
> WHERE
> "Date" < CURRENT_DATE
> )
>
> --

Re: Code does Not Read in FY 2025 Data

2024-07-16 Thread Anthony Apollis 
I am using Postgres and SQL Server.
Can you test the data pls.

On Tue, 16 Jul 2024 at 16:45, Adrian Klaver 
wrote:

> On 7/16/24 05:53, Anthony Apollis wrote:
> >  1. The problem is the code below reads only data up until 2024,
> > although the table has been updated with latest data that contains
> > 2025, for some odd reason it is not pulling in or showing data when
> > filtering for 2025 or even 2024 and later, which should contain all
> > the latest data.
> >  2.   SQL: "SET DATEFIRST 7;-- Holding Period Query
>
> I don't recognize SET DATEFIRST 7 as Postgres command.
>
> What database are you running this on?
>
> Have not gone through the below fully, but this:
>
> "FY" IS NOT NULL AND "FY" >= 'FY24'
>
> and this
>
> WEEK_NUMBER."Date" < CURRENT_DATE
>
> would seem to be at odds if you want to reach into 2025.
>
> >
> > SELECT
> >
> >  3.
> >
> > -- Holding Period Query
> > SELECT
> >  WEEK_NUMBER."Week Number",
> >  WM_GH."Calendar day",
> >  WM_GH."Month/Week",
> >  WM_GH."Day name",
> >  WM_GH."Company",
> >  WM_GH."Material Code",
> >  WM_GH."Metric",
> >  WM_GH."Metric Value"
> > FROM
> >  (
> >  SELECT
> >  "Calendar day",
> >  "Month/Week",
> >  "Day name",
> >  "Company",
> >  "Material Code",
> >  "Metric",
> >  "Metric Value"
> >  FROM
> >
> > "Prod"."IMETA_Weekly_Metrics_in_Focus_Global_Stock_View_SAP_BW" AS
> WM_GH
> >  WHERE
> >  WM_GH."Metric Focus" LIKE 'Weekly'
> >  AND WM_GH."Calendar day" <= (
> >  SELECT MAX(WEEK_NUMBER."Date") AS "MAX DATE"
> >  FROM (
> >  SELECT
> >  "Date",
> >  "Week number",
> >  COUNT("Date") OVER (PARTITION BY "Week
> > number" ORDER BY "Week number") AS "Number of days",
> >  COUNT("Date") OVER (PARTITION BY "Week
> > number" ORDER BY "Date") AS "Day number in weeks"
> >  FROM (
> >  SELECT DISTINCT
> >  "Date",
> >  EXTRACT(WEEK FROM WEEK_NUMBER."Date")
> > AS "Week number"
> >  FROM
> >
> > "dbo"."IMETA_Calendar_Days_Data_Table_Copy10" AS WEEK_NUMBER
> >  WHERE
> >  "FY" IS NOT NULL AND "FY" >= 'FY24'
> >  ) AS W_MAX
> >  ) AS WEEK_NUMBER
> >  WHERE
> >  WEEK_NUMBER."Date" < CURRENT_DATE
> >  AND "Number of days" = "Day number in weeks"
> >  )
> >  ) AS WM_GH
> > LEFT OUTER JOIN
> >  (
> >  SELECT
> >  *
> >  FROM (
> >  SELECT
> >  "Date",
> >  "Week number",
> >  COUNT("Date") OVER (PARTITION BY "Week number"
> > ORDER BY "Week number") AS "Number of days",
> >  COUNT("Date") OVER (PARTITION BY "Week number"
> > ORDER BY "Date") AS "Day number in weeks"
> >  FROM (
> >  SELECT DISTINCT
> >  "Date",
> >  EXTRACT(WEEK FROM WEEK_NUMBER."Date") AS "Week
> > number"
> >  FROM
> >  "dbo"."IMETA_Calendar_Days_Data_Table_Copy10"
> > AS WEEK_NUMBER
> >  ) AS W_MAX
> >  ) AS WEEK_NUMBER
> >  WHERE
> >  WEEK_NUMBER."Date" < CURRENT_DATE
> >  ) AS WEEK_NUMBER
> > ON
> >  WM_GH."Calendar day" = WEEK_NUMBER."Date"
> > ORDER BY
> >  WM_GH."Calendar day" DESC;
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Code does Not Read in FY 2025 Data

2024-07-16 Thread Anthony Apollis 
   1. The problem is the code below reads only data up until 2024, although
   the table has been updated with latest data that contains 2025, for some
   odd reason it is not pulling in or showing data when filtering for 2025 or
   even 2024 and later, which should contain all the latest data.
   2.  SQL: "SET DATEFIRST 7;-- Holding Period Query

   SELECT
   3.

   -- Holding Period Query
   SELECT
   WEEK_NUMBER."Week Number",
   WM_GH."Calendar day",
   WM_GH."Month/Week",
   WM_GH."Day name",
   WM_GH."Company",
   WM_GH."Material Code",
   WM_GH."Metric",
   WM_GH."Metric Value"
   FROM
   (
   SELECT
   "Calendar day",
   "Month/Week",
   "Day name",
   "Company",
   "Material Code",
   "Metric",
   "Metric Value"
   FROM

   "Prod"."IMETA_Weekly_Metrics_in_Focus_Global_Stock_View_SAP_BW" AS WM_GH
   WHERE
   WM_GH."Metric Focus" LIKE 'Weekly'
   AND WM_GH."Calendar day" <= (
   SELECT MAX(WEEK_NUMBER."Date") AS "MAX DATE"
   FROM (
   SELECT
   "Date",
   "Week number",
   COUNT("Date") OVER (PARTITION BY "Week number"
   ORDER BY "Week number") AS "Number of days",
   COUNT("Date") OVER (PARTITION BY "Week number"
   ORDER BY "Date") AS "Day number in weeks"
   FROM (
   SELECT DISTINCT
   "Date",
   EXTRACT(WEEK FROM WEEK_NUMBER."Date") AS
   "Week number"
   FROM

   "dbo"."IMETA_Calendar_Days_Data_Table_Copy10" AS WEEK_NUMBER
   WHERE
   "FY" IS NOT NULL AND "FY" >= 'FY24'
   ) AS W_MAX
   ) AS WEEK_NUMBER
   WHERE
   WEEK_NUMBER."Date" < CURRENT_DATE
   AND "Number of days" = "Day number in weeks"
   )
   ) AS WM_GH
   LEFT OUTER JOIN
   (
   SELECT
   *
   FROM (
   SELECT
   "Date",
   "Week number",
   COUNT("Date") OVER (PARTITION BY "Week number" ORDER BY
   "Week number") AS "Number of days",
   COUNT("Date") OVER (PARTITION BY "Week number" ORDER BY
   "Date") AS "Day number in weeks"
   FROM (
   SELECT DISTINCT
   "Date",
   EXTRACT(WEEK FROM WEEK_NUMBER."Date") AS "Week
   number"
   FROM
   "dbo"."IMETA_Calendar_Days_Data_Table_Copy10" AS
   WEEK_NUMBER
   ) AS W_MAX
   ) AS WEEK_NUMBER
   WHERE
   WEEK_NUMBER."Date" < CURRENT_DATE
   ) AS WEEK_NUMBER
   ON
   WM_GH."Calendar day" = WEEK_NUMBER."Date"
   ORDER BY
   WM_GH."Calendar day" DESC;
<>

Re: Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric arithmetic causes

2024-02-28 Thread Anthony Apollis 
Data in Excel

On Wed, 28 Feb 2024 at 22:22, Anthony Apollis 
wrote:

> Please assist. I am using SSIS to read data from an Excel sheet into
> Postgres. I have increased the column size a few times, just cant seem to
> get the data in. Getting errors relating to destination column size.
>
> [Excel Source [24]] Error: System.Exception: SqlTruncateException: Numeric 
> arithmetic causes truncation.. RowNumber=1, ColumnName=Metric Value 
> [DataType=DT_NUMERIC,Length=0], Data=[Length=12, Value=36655.63]
>at 
> ZappySys.PowerPack.Adapter.SqlDataComponentBase.HandleException(Exception ex)
>at ZappySys.PowerPack.Adapter.SqlDataComponentBase.PrimeOutput(Int32 
> outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
>at 
> Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100
>  wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr 
> ppBufferWirePacket)
>
>

Using a Conversion Table

2024-02-14 Thread Anthony Apollis 
*I am trying to convert a column from ZAR Column "
Amount_in_Company_Code_Currency"  " to USD.*
 Table:
CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW2"
(
"Company_Code" character varying(255) COLLATE pg_catalog."default",
"Posting_Period" integer,
"Fiscal_Year" integer,
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" integer,
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Amount_in_Company_Code_Currency" numeric,
"Company_Code_Currency" character varying(255) COLLATE
pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"Loaddate" date DEFAULT CURRENT_DATE,
"Row_Hash" text COLLATE pg_catalog."default",
"LoadTime" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
"ZTBR_TransactionCode" integer NOT NULL GENERATED ALWAYS AS IDENTITY (
INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey2" PRIMARY KEY
("ZTBR_TransactionCode")
)

*Conversion Table :*


CREATE TABLE IF NOT EXISTS dim."IMETA_Master_Currency_Data_TA_BR"
(
"Currency" character varying(255) COLLATE pg_catalog."default",
"Currency name" character varying(255) COLLATE pg_catalog."default",
"Currency from" character varying(255) COLLATE pg_catalog."default",
"Scenario" character varying(255) COLLATE pg_catalog."default",
"Fiscal year" double precision,
"Fiscal period" character varying(255) COLLATE pg_catalog."default",
"Currency from value" double precision,
"Currency to value" double precision,
"Loaddate" date
)

TABLESPACE pg_default;

*I am getting no output for using:*

SELECT
z."Fiscal_Year",
SUM(z."Amount_in_Company_Code_Currency") AS Total_Amount,
ROUND(SUM(z."Amount_in_Company_Code_Currency" / CASE
WHEN c."Currency" = 'USD' THEN 1
ELSE c."Currency to value" END)::numeric, 2) AS Total_Amount_USD
FROM
system."IMETA_ZTRB_MP$F_ZTBR_TA_BW2" z
LEFT JOIN
(SELECT
"Currency",
"Currency to value"
 FROM
dim."IMETA_Master_Currency_Data_TA_BR"
 WHERE
"Scenario" = 'Actual'  -- Adjust the scenario as needed
AND "Fiscal year" = 2024) c  -- Adjust the fiscal year as needed
ON
z."Company_Code_Currency" = c."Currency"
WHERE
z."Fiscal_Year" = 2024
GROUP BY
z."Fiscal_Year";

*In a previous calculation/join i used the code below and it worked, what
am i doing wrong?*

-- View: model.IMETA_ZTRB_BRACS_Model_TA_BW_View

-- DROP VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View";

CREATE OR REPLACE VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View"
 AS
 SELECT t."ZTBR_TransactionCode",
t."Company_Code",
t."Posting_Period",
t."Fiscal_Year",
t."Profit_Center",
t."Account_Number",
t."Business_Process",
t."Internal_Order",
t."Amount_in_Company_Code_Currency",
t."Company_Code_Currency",
t."BRACS_FA",
t."Expense_Type",
t."Primary_ZTBR_TransactionCode",
t."DIM_BRACS_Account_Description" AS "Acct_Type",
t."DIM_Classification",
t."DIM_Direct_Primary_Key",
t."DIM_Order",
t."SDM_BRACSFA",
t."SDM_Function",
t."BRACS_Level_1",
t."BRACS_Level_2",
t."BRACS_Level_3",
t."Roll_Up_Currency",
t."Roll_Up_Account_Description",
t."BRACS_Account",
t."BRACS_Account_Description",
t."IS_BS",
t."Classification",
t."Roll_Up_Function",
t."Region",
t."Roll_Up",
t."Entity",
t."Entity_Name",
t."Entity_Level",
t."Entity_Level_1",
t."Entity_Level_2",
t."Entity_Level_3",
t."Entity_Level_4",
t."Entity_Level_5",
t."Entity_Level_6",
t."Region_Mapping_CoCd",
t."Region_Mapping_Sub_Region",
t."Region_Mapping_Region",
t."Region_Mapping_BRACS_Entity",
t."Region_Mapping_Consul",
t."Region_Mapping_Report",
t."Region_Mapping_Region_BRACS",
t."Region_Mapping_Group",
t."Region_Mapping_Group_BRACS",
round((t."Amount_in_Company_Code_Currency"::double precision /
curr."Conversion rate")::numeric, 2) AS "Amount in USD",
CASE
WHEN t."Fiscal_Year"::double precision =
date_part('year'::text, CURRENT_DATE) THEN
t."Amount_in_Company_Code_Currency"
ELSE NULL::numeric
END AS "Current Period",
CASE
WHEN t."Fiscal_Year"::double precision =
(date_part('year'::text, CURRENT_DATE) - 1::double precision) THEN
t."Amount_in_Company_Code_Currency"
ELSE NULL::numeric
END AS "Prior Period",
CASE
WHEN t."Fiscal_Year"::double precision =

Check Code Correction Current Period Prior Period Movement

2023-11-23 Thread Anthony Apollis 
Please assist me?
I am trying to calculate
 Current Period   Prior Period   Movement   from my data, the date columns
are:
[image: image.png]

Please check my code for any errors, code runs, i just want to make sure it
does what it's supposed to do.
-- View: model.IMETA_ZTRB_BRACS_Model_TA_BW_View

-- DROP VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View";

CREATE OR REPLACE VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View"

 AS

 SELECT t."ZTBR_TransactionCode",

  t."Company_Code",

  t."Posting_Period", -- This is a period within a year

  t."Fiscal_Year", -- This contains years 2019 to present

  t."Profit_Center",

  t."Account_Number",

  t."Business_Process",

  t."Internal_Order",

  t."Amount_in_Company_Code_Currency",

  t."Company_Code_Currency",

  t."BRACS_FA",

  t."Expense_Type",

  t."Primary_ZTBR_TransactionCode",

  t."Acct_Type",

  t."BRACS_Level_1",

  t."BRACS_Level_2",

  t."BRACS_Level_3",

  t."GCoA",

  t."Account_Desc",

  t."EXPENSE_FLAG",

  t."BRACS",

  t."BRACS_DESC",

  t."Roll_Up_Currency",

  t."Roll_Up_Account_Description",

  t."BRACS_Account",

  t."BRACS_Account_Description",

  t."IS_BS",

  t."Classification",

  t."Roll_Up_Function",

  t."Region",

  t."Roll_Up",

  t."Entity",

  t."Entity_Name",

  t."Entity_Level",

  t."Entity_Level_1",

  t."Entity_Level_2",

  t."Entity_Level_3",

  t."Entity_Level_4",

  t."Entity_Level_5",

  t."Entity_Level_6",

  t."Region_Mapping_CoCd",

  t."Region_Mapping_Sub_Region",

  t."Region_Mapping_Region",

  t."Region_Mapping_BRACS_Entity",

  t."Region_Mapping_Consul",

  t."Region_Mapping_Report",

  t."Region_Mapping_Region_BRACS",

  t."Region_Mapping_Group",

  t."Region_Mapping_Group_BRACS",

  t."DIM_BRACS_Account_Description",

  t."DIM_Classification",

  t."DIM_Direct_Primary_Key",

  t."DIM_Order",

  t."SDM_BRACSFA",

  t."SDM_Function",

  round((t."Amount_in_Company_Code_Currency"::double precision /
curr."Conversion rate")::numeric, 2) AS "Amount in USD",

CASE

  WHEN t."Fiscal_Year"::double precision = date_part('year'::text,
CURRENT_DATE) THEN t."Amount_in_Company_Code_Currency"

  ELSE NULL::numeric

END AS "Current Period",

CASE

  WHEN t."Fiscal_Year"::double precision = (date_part('year'::text,
CURRENT_DATE) - 1::double precision) THEN
t."Amount_in_Company_Code_Currency"

  ELSE NULL::numeric

END AS "Prior Period",

CASE

  WHEN t."Fiscal_Year"::double precision = date_part('year'::text,
CURRENT_DATE) THEN t."Amount_in_Company_Code_Currency"

  WHEN t."Fiscal_Year"::double precision = (date_part('year'::text,
CURRENT_DATE) - 1::double precision) THEN -
t."Amount_in_Company_Code_Currency"

  ELSE NULL::numeric

END AS "Movement"

  FROM model."IMETA_ZTRB_BRACS_Model_TA_BW3" t

   LEFT JOIN ( SELECT "IMETA_Master_Currency_Data_TA_BR"."Currency",

  "IMETA_Master_Currency_Data_TA_BR"."Currency name",

CASE

  WHEN "IMETA_Master_Currency_Data_TA_BR"."Currency"::text =
'USD'::text THEN 1::double precision

  ELSE "IMETA_Master_Currency_Data_TA_BR"."Currency to value"

END AS "Conversion rate"

  FROM dim."IMETA_Master_Currency_Data_TA_BR"

 WHERE "IMETA_Master_Currency_Data_TA_BR"."Scenario"::text =
'BUD'::text) curr ON t."Company_Code_Currency" = curr."Currency"::text;

ALTER TABLE model."IMETA_ZTRB_BRACS_Model_TA_BW_View"

OR

-- Drop the view if it exists to prevent errors
DROP VIEW IF EXISTS model."IMETA_ZTRB_BRACS_Model_TA_BW_View";

-- Create or replace the view
CREATE OR REPLACE VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View" AS
SELECT
t."ZTBR_TransactionCode",
-- ... (other columns)
t."SDM_Function",
-- Convert the amount to USD
ROUND((t."Amount_in_Company_Code_Currency" / curr."Conversion
rate")::numeric, 2) AS "Amount in USD",
-- Calculate the Current Period Amount
CASE
  WHEN t."Fiscal_Year"::integer = EXTRACT(YEAR FROM CURRENT_DATE) THEN
t."Amount_in_Company_Code_Currency"
  ELSE NULL
END AS "Current Period",
-- Calculate the Prior Period Amount
CASE
  WHEN t."Fiscal_Year"::integer = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN t."Amount_in_Company_Code_Currency"
  ELSE NULL
END AS "Prior Period",
-- Calculate the Movement
CASE
  WHEN t."Fiscal_Year"::integer = EXTRACT(YEAR FROM CURRENT_DATE) THEN
t."Amount_in_Company_Code_Currency"
  WHEN t."Fiscal_Year"::integer = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN -t."Amount_in_Company_Code_Currency"
  ELSE NULL
END AS "Movement"
FROM
model."IMETA_ZTRB_BRACS_Model_TA_BW3" t
LEFT JOIN
(SELECT
"Currency",
"Currency name",
CASE
  WHEN "Currency" = 'USD' THEN 1
  ELSE "Currency to value"
END AS "Conversion rate"
  FROM dim."IMETA_Master_Currency_Data_TA_BR"
 WHERE "Scenario" = 'BUD') curr
ON t."Company_Code_Currency" = curr."Currency";

-- Change the ownership of the view
ALTER VIEW
select * from

Getting most records possibly from an Inner/Left Join

2023-11-19 Thread Anthony Apollis 
For sample data in Excel see
https://drive.google.com/file/d/17aOmG-Ynx-6U05wNNrHXJG7iywgCMiuy/view?usp=sharing

(1) The fact table is from SAP.

(2) Dimension Tables are named "B Masked". Please note that none of the
tables had primary keys, I had to bring them in myself. In the Bracs
Mapping tab of this file, there is a column GCoA which is the Account
Number. Then there is BRACS which is the Bracs(source) Account number.
these numbers match the same columns in the Mapping tab of the Cash Flow
Pivoted file.

(3) The Source Data is a combination and shorter version of the Mapping
tab. Column Account match GCoA and BRACS Account match

matches BRACS in the Bracs mapping tab above of the B Masked file.

I noticed some similarities in the different tabs/tables so I denormalised
some tabs/tables. e.g. Region Mapping and Entity Mapping in the same file I
made as 1 table.

Joining on not unique keys is of concern.

(4) Cash Flow file, tab Sap Source is a pivoted table of the Source Data.

(5) The Cash Flow tab is the final reporting structure that must be
replicated in power. Column "G" in this tab is the "Function" column in the
Pivoted tab.

I do left joins to the Fact table and some inner join. Please advise.

The Function column that makes up the bulk of giving meaning to the
figures, most of the detail of this column is left out when I do a join as
seen in Imeta_Bracs_Roll_Up joined to Fact file with SQL code.

I brought in the Mapping table to connect the roll-up table to it as seen
in Imeta_Bracs_Roll_Up joined to Bridge, this query takes. very long to
run. Also, the joins take a lot of memory and some ETL/SSIS tasks may fail.
What would you be bettering in this instance?

Re: Unique Primary Key Linked to Multiple Accounts

2023-11-13 Thread Anthony Apollis 
I cant get distinct data, im tying to break up the insert into chunks and
it does not help

On Mon, 13 Nov 2023 at 20:05, Adrian Klaver 
wrote:

> On 11/13/23 09:54, Anthony Apollis wrote:
>
> Please reply to list also
> Ccing list
>
> > Hi Adrian
> >
> > Yes, the Account number column(s) are not unique. I brought in the
> > primary keys in both tables. If I enforce referential integrity on the
> > dimension table, will this solve the issue?
>
> 1) Your original post shows no PK for dim."IMETA_BRACS_Mapping_".
>
> 2) Define '...enforce referential integrity on the dimension table,
> ...'. In other words provide the Foreign Key relationship you plan to
> set up. Though I doubt that will solve anything, because I don't
> actually see an issue.
>
> > I am struggling to select distinct values from my tables, I'm working
> > via remote server and get connection lost issues.
> > When i use "ORDER BY "Source data.Company Code"" i get distinct rows:
> > o.png
>
> 3) Do not use images, copy and paste as text. Are they distinct all the
> way through or just for a given  "Source data.Company Code"?
>
> >
> > I plan to extract distinct columns/rows from my destination table and
> > write a view that will access this unique data. I am even
> > struggling with this code below, it loses connection. Apparently it uses
> > too much memory for 5 million + records. Any suggestions?
>
> 4) Have no idea what the below has to do with creating a view?
>
> >
> > DO $$
> > DECLARE
> >row_count INTEGER := 100;
> >offset_val INTEGER := 0;
> >inserted_rows INTEGER;
> > BEGIN
> >LOOP
> >  INSERT INTO model.staging_ZTRB_BRACS_Combined (
> >"ZTBR_TransactionCode",
> >"Company_Code",
> >"Posting_Period",
> >"Fiscal_Year",
> >"Profit_Center",
> >"Account_Number",
> >"Business_Process",
> >"Internal_Order",
> >"Amount_in_Company_Code_Currency",
> >"Company_Code_Currency",
> >"BRACS_FA",
> >"Acct Type",
> >"Level 1",
> >"Level 2",
> >"Level 3",
> >"GCoA",
> >"Account Desc",
> >"EXPENSE FLAG",
> >"BRACS",
> >"BRACS_DESC",
> >"Source data.Company Code",
> >"Source data.Currency",
> >"Source data.Account",
> >"Source data.Account Description",
> >"Source data.BRACS Account",
> >"Source data.BRACS Account Description",
> >"Source data.IS/BS",
> >"Source data.Classification",
> >"Source data.Function",
> >"Source data.Region",
> >"Source data.Roll - Up"
> >  )
> >  SELECT
> >DISTINCT fact."ZTBR_TransactionCode",
> >fact."Company_Code",
> >fact."Posting_Period",
> >fact."Fiscal_Year",
> >fact."Profit_Center",
> >fact."Account_Number",
> >fact."Business_Process",
> >fact."Internal_Order",
> >fact."Amount_in_Company_Code_Currency",
> >fact."Company_Code_Currency",
> >fact."BRACS_FA",
> >bracs."Acct Type",
> >bracs."Level 1",
> >bracs."Level 2",
> >bracs."Level 3",
> >bracs."GCoA",
> >bracs."Account Desc",
> >bracs."EXPENSE FLAG",
> >bracs."BRACS",
> >bracs."BRACS_DESC",
> >bracs."Source data.Company Code",
> >bracs."Source data.Currency",
> >bracs."Source data.Account",
> >bracs."Source data.Account Description",
> >bracs."Source data.BRACS Account",
> >bracs."Source data.BRACS Account Description",
> >bracs."Source data.IS/BS",
> >bracs."Source data.Classification",
> >bracs."Source data.Function",
> >bracs."Source data.Region",

Unique Primary Key Linked to Multiple Accounts

2023-11-12 Thread Anthony Apollis 
Please advice. I brought in data from SAP and assigned unique primary key
to the table:

[image: unique]

CREATE TABLE IF NOT EXISTS fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
"ZTBR_TransactionCode" integer NOT NULL,
"Company_Code" character varying COLLATE pg_catalog."default",
"Posting_Period" integer,
"Fiscal_Year" integer,
"Profit_Center" text COLLATE pg_catalog."default",
"Account_Number" integer,
"Business_Process" character varying COLLATE pg_catalog."default",
"Internal_Order" text COLLATE pg_catalog."default",
"Amount_in_Company_Code_Currency" numeric,
"Company_Code_Currency" text COLLATE pg_catalog."default",
"BRACS_FA" character varying COLLATE pg_catalog."default",
"Expense_Type" text COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying COLLATE pg_catalog."default",
"CC_Direct" text COLLATE pg_catalog."default",
"Segment_PC" text COLLATE pg_catalog."default",
"CC_Master_FA" text COLLATE pg_catalog."default",
"Region_Secondary_Key" integer,
"Direct_Indirect_Secondary_Key" integer,
"Source_Description_Secondary_Key" integer,
"Entity_Secondary_Key" integer,
"Master_BRACS_Secondary_Key" integer,
"Loaddate" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey" PRIMARY KEY
("ZTBR_TransactionCode")
)

TABLESPACE pg_default;

I joined it with a dimension table.

Joining code

 fact."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
LEFT JOIN dim."IMETA_BRACS_Mapping" AS bracs_map
ON fact."Account_Number" = bracs_map."GCoA" AND
fact."Expense_Type" = bracs_map."EXPENSE FLAG"

It is joined on the Account numbers, which appears in the table multiple
times. Problem is the Unique Primary Key is then mapped to these Account
numbers multiple times.

[image: unique2]

CREATE TABLE IF NOT EXISTS dim."IMETA_BRACS_Mapping_"
(
"Acct Type" character varying(255) COLLATE pg_catalog."default",
"Level 1" character varying(255) COLLATE pg_catalog."default",
"Level 2" character varying(255) COLLATE pg_catalog."default",
"Level 3" character varying(255) COLLATE pg_catalog."default",
"GCoA" integer,
"Account Desc" character varying(255) COLLATE pg_catalog."default",
"EXPENSE FLAG" character varying(255) COLLATE pg_catalog."default",
"BRACS" integer,
"BRACS_DESC" character varying(255) COLLATE pg_catalog."default",
"Source data.Company Code" character varying(255) COLLATE
pg_catalog."default",
"Source data.Currency" character varying(255) COLLATE pg_catalog."default",
"Source data.Account" integer,
"Source data.Account Description" character varying(255) COLLATE
pg_catalog."default",
"Source data.BRACS Account" integer,
"Source data.BRACS Account Description" character varying(255)
COLLATE pg_catalog."default",
"Source data.IS/BS" character varying(255) COLLATE pg_catalog."default",
"Source data.Classification" character varying(255) COLLATE
pg_catalog."default",
"Source data.Function" character varying(255) COLLATE pg_catalog."default",
"Source data.Region" character varying(255) COLLATE pg_catalog."default",
"Source data.Roll - Up" character varying(255) COLLATE pg_catalog."default"
)

TABLESPACE pg_default;

Result:

[image: unique3]

Please advice.

Calculating Days/Time(Are Loops Neccessary?)

2023-09-19 Thread Anthony Apollis 
I have a table that needs to give me all data up until yesterday. This will
be part of an SSIS package that runs monthly, a day before the last day.
code is:

-- Create the new table if it doesn't exist and insert data
CREATE TABLE IF NOT EXISTS "model"."IMETA_ZTBR_BRACS_Model_TA_BW" AS
SELECT
fact."ZTBR_TransactionCode",
fact."Company_Code",
fact."Posting_Period",
fact."Fiscal_Year",
fact."Profit_Center",
fact."Account_Number",
fact."Business_Process",
fact."Internal_Order",

fact."Amount_in_Company_Code_Currency",
fact."Company_Code_Currency",
fact."BRACS_FA",
fact."Expense_Type",
fact."BRACS_ACCT_Key",
fact."CC_Direct",
fact."Segment_PC",
fact."CC_Master_FA",
fact."Region_Secondary_Key",
fact."Direct_Indirect_Secondary_Key",
fact."Source_Description_Secondary_Key",
fact."Entity_Secondary_Key",
fact."Master_BRACS_Secondary_Key",
bracs_map."Acct Type",
bracs_map."Level 1" AS "BRACS_Level1",
bracs_map."Level 2" AS "BRACS_Level2",
bracs_map."Level 3" AS "BRACS_Level3",
bracs_map."GCoA",
bracs_map."Account Desc",
bracs_map."EXPENSE FLAG",
bracs_map."BRACS",
bracs_map."BRACS_DESC",
bracs_map."Primary_ZTBR_TransactionCode",
direct_indirect_map."BRACS Account Description",
direct_indirect_map."CLASSIFICATION",
direct_indirect_map."Direct_Primary_Key",
region_map."CoCd",
region_map."Region",
region_map."Sub Region",
region_map."BRACS Entity",
region_map."Consul",
region_map."Report",
region_map."Region BRACS",
region_map."Group",
region_map."Group BRACS",
region_map."Region_Primary_Key",
entity_map."Entity",
entity_map."Entity Name",
entity_map."Entity Level",
entity_map."Level 1" AS "Entity_Level1",
entity_map."Level 2" AS "Entity_Level2",
entity_map."Level 3" AS "Entity_Level3",
entity_map."Level 4" AS "Entity_Level4",
entity_map."Level 5" AS "Entity_Level5",
entity_map."Level 6" AS "Entity_Level6",
entity_map."Entity_ID",
src_desc_map."BRACS_Key",
src_desc_map."BRACSFA",
src_desc_map."Function"
FROM
system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
LEFT JOIN
dim."IMETA_BRACS_Mapping" AS bracs_map
ON
fact."Account_Number"::text = bracs_map."GCoA"::text AND
fact."Expense_Type"::text = bracs_map."EXPENSE FLAG"::text
LEFT JOIN
dim."IMETA_Direct_Indirect_Mapping_New" AS direct_indirect_map
ON
bracs_map."Account Desc" = direct_indirect_map."BRACS Account Description"
LEFT JOIN
dim."IMETA_Region_Mapping" AS region_map
ON
fact."Company_Code"::text = region_map."CoCd"::text
LEFT JOIN
dim."IMETA_Entity_Mapping" AS entity_map
ON
region_map."BRACS Entity" = entity_map."Entity"
LEFT JOIN
dim."IMETA_Source_Description_Mapping" AS src_desc_map
ON
ltrim(fact."BRACS_FA", '0') = src_desc_map."BRACSFA"
WHERE
src_desc_map."BRACSFA" IS NOT NULL;

I have inherited this code, problem is it is over code, i believe. The
package is gonna run once a month and this code run is a loop. How can this
loop be running and checking data up until last day, if it only run once a
month?


NUMBER OF LOOPS FOR POSTGRESQL ETL:
SELECT
CASE
WHEN (((EXTRACT(DAY FROM
((CASE
WHEN
(SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar
day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS
WEEK_NUMBER)) IS NULL THEN '2020-07-01'
ELSE
(SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar
day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS
WEEK_NUMBER)) + interval '1 day'
END))::timestamp - --start date
(CAST(NOW() - INTERVAL '1 day' AS DATE))::timestamp)-1) --end date
* -1)) <= 30 THEN 1
ELSE
CEIL(((EXTRACT(DAY FROM
((CASE
WHEN
(SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar
day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS
WEEK_NUMBER)) IS NULL THEN '2020-07-01'
ELSE
(SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar
day" FROM system."IMETA_ZINV_AP1_Invoice_data_TA_BW"" AS WEEK_NUMBER))
+ interval '1 day'
END))::timestamp - --start date
(CAST(NOW() - INTERVAL '1 day' AS DATE))::timestamp)-1) --end date
* -1)/30) --30 DAY INTERVALS UNLESS LESS
END
AS "Number of days"
START DATE SCRIPT FOR POSTGRESQL
SELECT
 REPLACE(
CAST(
CAST(
CASE
 WHEN
 (SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
 WHERE
 WEEK_NUMBER."CALDAY" = (SELECT MAX(DISTINCT "CALDAY") AS "Calendar
day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS
WEEK_NUMBER)) IS NULL THEN '2020-07-01'
 ELSE
 (SELECT DISTINCT

Ensuring Rifferential Integrity

2023-09-17 Thread Anthony Apollis 
 I brought in the Primary/Secondary/Foreign keys because it does not exist
in the Fact/Dimension tables.

The Fact tables contain 6 million records and the dimension tables are
tiny. Because some columns don't exist in the Fact and Dimension table I
can not update the Foreign Keys in the Fact table to ensure relationship
integrity.

e.g Say I have a Fact table containing Apple's Sales; one of the Dimension
tables is Apple Type. Since the two tables don't contain an Apple Type
column in both I won't be able to enforce referention integrity. If my
Apple Sales table contains 6 million + sales, I won't be able to break it
down Apple sales by Type.

That is the problem I am sitting with. My fact Table is not able to give me
unique Foreign Key columns. I read about a Mapping table.

I would like to join IMETA_ZTRB_MP$F with Dimensions. I have brought in
these mapping tables as dimensions(see code and tables attached). I created
Primary and Foreign/Secondary keys to join these tables. Currently, I don’t
have a unique column within the SAP table and Dimension tables. To be sure
that the data align I needed column(s) like that.

a process in achieving this?

I have brought in table key constraints, but because matching columns are
missing I am not getting unique foreign keys for these. An example is using
this code to update the foreign key values in the Fact/SAP table



” UPDATE system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS A

SET "Master_BRACS_Secondary_Key" = B."Primary_ZTBR_TransactionCode"

FROM dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_" AS B

WHERE A."ZTBR_TransactionCode" = B."Primary_ZTBR_TransactionCode";”



It is supposed to take primary key values from: [image: image003]



And insert it into

:

[image: image004 (1)]

The problem is those values in the Foreign/Secondary keys are not unique.

Here is the SQL:



-- Table: system.IMETA_ZTRB_MP$F_ZTBR_TA_BW

-- DROP TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW";

CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
"ZTBR_TransactionCode" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTBR_TransactionCode_Seq"'::regclass),
"Company_Code" character varying COLLATE pg_catalog."default",
"Posting_Period" integer,
"Fiscal_Year" integer,
"Profit_Center" text COLLATE pg_catalog."default",
"Account_Number" integer,
"Business_Process" character varying COLLATE pg_catalog."default",
"Internal_Order" integer,
"Trading_Partner" text COLLATE pg_catalog."default",
"Amount_in_Company_Code_Currency" numeric,
"Company_Code_Currency" text COLLATE pg_catalog."default",
"BRACS_FA" character varying COLLATE pg_catalog."default",
"Expense_Type" text COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying COLLATE pg_catalog."default",
"CC_Direct" text COLLATE pg_catalog."default",
"Segment_PC" integer,
"CC_Master_FA" text COLLATE pg_catalog."default",
"Region_Secondary_Key" integer,
"Direct_Indirect_Secondary_Key" integer,
"Source_Description_Secondary_Key" integer,
"Entity_Secondary_Key" integer,
"Master_BRACS_Secondary_Key" integer,
"Loaddate" date,
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey" PRIMARY KEY
("ZTBR_TransactionCode"),
CONSTRAINT "IMETA_ZTBR_TransactionCode_unique" UNIQUE
("ZTBR_TransactionCode"),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_fkey"
FOREIGN KEY ("Master_BRACS_Secondary_Key")
REFERENCES dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_"
("Primary_ZTBR_TransactionCode") MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE NO ACTION,
CONSTRAINT fk_entity FOREIGN KEY ("Entity_Secondary_Key")
REFERENCES dim."IMETA_Entity_Mapping" ("Entity_ID") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
OWNER to apollia;
---
-- Table: dim.IMETA_Master_BRACS_to_SAP_Data_TA_BR_

-- DROP TABLE IF EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_";

CREATE TABLE IF NOT EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_"
(
"Primary_ZTBR_TransactionCode" integer NOT NULL,
"Level 1" character varying(255) COLLATE pg_catalog."default",
"Level 2" character varying(255) COLLATE pg_catalog."default",
"Level 3" character varying(255) COLLATE pg_catalog."default",
"Acct Type" character varying(255) COLLATE pg_catalog."default",
"Account Desc" character varying(255) COLLATE pg_catalog."default",
"EXPENSE FLAG" character varying(255) COLLATE pg_catalog."default",
"BRACS" character varying(255) COLLATE pg_catalog."default",
"BRACS_DESC"" " character varying(50) COLLATE pg_catalog."default",
"BRACS_DESC" character varying(255) COLLATE pg_catalog."default",
"Loaddate" date,
CONSTRAINT "Primary Key" PRIMARY KEY ("Primary_ZTBR_TransactionCode")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_"
OWNER to apollia;
ZTBR n

Re: Making Sure Primary and Secondary Keys Alligns

2023-09-13 Thread Anthony Apollis 
Yes in deed.
I am trying to make sure that the keys are aligned, but it doesnt update or
it simply shows NULL in Fact table, meaning its secondary keys.

"-- Step 1: Drop existing foreign key constraint for Entity
ALTER TABLE system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" DROP CONSTRAINT IF EXISTS
fk_entity;

-- Step 2: Drop and recreate secondary key for Entity, setting it to null
by default
ALTER TABLE system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
DROP COLUMN IF EXISTS "Entity_Secondary_Key",
ADD COLUMN "Entity_Secondary_Key" INTEGER;

-- Step 3: Update secondary key for Entity based on primary key from the
dimension table
UPDATE system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS fact
SET "Entity_Secondary_Key" = dim2."Entity_ID"
FROM dim."IMETA_Entity_Mapping" AS dim2
WHERE fact."Entity_Secondary_Key" = dim2."Entity_ID";

-- Step 4: Re-add foreign key constraint for Entity
ALTER TABLE system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
ADD CONSTRAINT fk_entity FOREIGN KEY ("Entity_Secondary_Key") REFERENCES
dim."IMETA_Entity_Mapping"("Entity_ID");
"
Thank you!

On Mon, 11 Sept 2023 at 17:34, Alban Hertroys  wrote:

>
> > On 11 Sep 2023, at 16:09, Anthony Apollis 
> wrote:
> >
> > Fact Table:
> > CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
> > (
>
> (…)
>
> > )
>
> > and Dimension:CREATE TABLE IF NOT EXISTS dim."IMETA_Region_Mapping"
> > (
>
> (…)
>
> > )
>
> > How do i get that all these columns that are joined are aligned, meaning
> if it starts with 1 in one column it must be 1 in the other columns. Or how
> would you assign unique keys in Postgres?
>
> Are you perhaps asking how to define FOREIGN KEY CONSTRAINTs?
>
> https://www.postgresql.org/docs/15/ddl-constraints.html#DDL-CONSTRAINTS-FK
>
>
> Regards,
> Alban Hertroys
> --
> There is always an exception to always.
>
>
>
>
>

Making Sure Primary and Secondary Keys Alligns

2023-09-11 Thread Anthony Apollis 
Fact Table:
-- DROP TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW";

CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
"ZTBR_TransactionCode" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_ZTBR_TransactionCode_seq"'::regclass),
"Company_Code" character varying(4) COLLATE pg_catalog."default",
"Posting_Period" character varying(7) COLLATE pg_catalog."default",
"Fiscal_Year" character varying(4) COLLATE pg_catalog."default",
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" character varying(255) COLLATE pg_catalog."default",
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Cost_Center" character varying(10) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Trading_Partner" character varying(255) COLLATE pg_catalog."default",
"Amount_in_company_code_currency" numeric(17,2),
"Company_code_currency" character varying(5) COLLATE
pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"CC_Direct" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"Master_BRACS_Secondary_Key" integer,
"Source_Description_Secondary_Key" integer,
"Direct_Indirect_Secondary_Key" integer,
"Entity_Secondary_Key" integer,
"Region_Secondary_Key" integer,
"Staging_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Staging_Secondary_Key_seq"'::regclass),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey" PRIMARY KEY
("ZTBR_TransactionCode"),
CONSTRAINT
"IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_key" UNIQUE
("Direct_Indirect_Secondary_Key"),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_key" UNIQUE
("Entity_Secondary_Key"),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_key"
UNIQUE ("Master_BRACS_Secondary_Key"),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_key" UNIQUE
("Region_Secondary_Key"),
CONSTRAINT
"IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_key" UNIQUE
("Source_Description_Secondary_Key"),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Staging_Secondary_Key_key"
UNIQUE ("Staging_Secondary_Key")
)and Dimension:CREATE TABLE IF NOT EXISTS dim."IMETA_Region_Mapping"
(
"CoCd" character varying(255) COLLATE pg_catalog."default",
"Sub Region" character varying(255) COLLATE pg_catalog."default",
"Region" character varying(255) COLLATE pg_catalog."default",
"BRACS Entity" character varying(255) COLLATE pg_catalog."default",
"Consul" character varying(255) COLLATE pg_catalog."default",
"Report" character varying(255) COLLATE pg_catalog."default",
"Region BRACS" character varying(255) COLLATE pg_catalog."default",
"Group" character varying(255) COLLATE pg_catalog."default",
"Group BRACS" character varying(255) COLLATE pg_catalog."default",
"J" character varying(255) COLLATE pg_catalog."default",
"K" character varying(255) COLLATE pg_catalog."default",
"L" character varying(255) COLLATE pg_catalog."default",
"M" character varying(255) COLLATE pg_catalog."default",
"N" character varying(255) COLLATE pg_catalog."default",
"Region_Primary_Key" integer NOT NULL DEFAULT
nextval('dim."IMETA_Region_Mapping_Region_Secondary_Key_seq"'::regclass),
CONSTRAINT "IMETA_Region_Mapping_pkey" PRIMARY KEY
("Region_Primary_Key")
)
How do i get that all these columns that are joined are aligned, meaning if
it starts with 1 in one column it must be 1 in the other columns. Or how
would you assign unique keys in Postgres?

Only getting few records inserted from millions

2023-09-07 Thread Anthony Apollis 
Please assist.

I am retrieving data from SAP BW using SSIS. The data is loaded into:

CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
"Company_Code" character varying(4) COLLATE pg_catalog."default",
"Posting_Period" character varying(7) COLLATE pg_catalog."default",
"Fiscal_Year" character varying(4) COLLATE pg_catalog."default",
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" character varying(255) COLLATE pg_catalog."default",
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Cost_Center" character varying(10) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Trading_Partner" character varying(255) COLLATE pg_catalog."default",
"Amount_in_company_code_currency" numeric(17,2),
"Company_code_currency" character varying(5) COLLATE pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"CC_Direct" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"RowInsertedTimestamp" date DEFAULT CURRENT_DATE,
"RowUpdatedTimestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
"Master_BRACS_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_seq"'::regclass),
"Source_Description_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_seq"'::regclass),
"Direct_Indirect_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_seq"'::regclass),
"Entity_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_seq"'::regclass),
"Region_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_seq"'::regclass),
CONSTRAINT "ZTBR_TA_BW_PrimaryKey" PRIMARY KEY
("Master_BRACS_Secondary_Key")
)

TABLESPACE pg_default;

Destination table is:

CREATE TABLE IF NOT EXISTS model."IMETA_ZTBR_BRACS_Model_TA_BW"
(
"Company_Code" character varying(4) COLLATE pg_catalog."default",
"Posting_Period" character varying(7) COLLATE pg_catalog."default",
"Fiscal_Year" character varying(4) COLLATE pg_catalog."default",
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" character varying(255) COLLATE pg_catalog."default",
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Cost_Center" character varying(10) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Trading_Partner" character varying(255) COLLATE pg_catalog."default",
"Amount_in_company_code_currency" numeric(17,2),
"Company_code_currency" character varying(5) COLLATE pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"CC_Direct" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"Master_BRACS_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_seq"'::regclass),
"Source_Description_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_seq"'::regclass),
"Direct_Indirect_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_seq"'::regclass),
"Entity_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_seq"'::regclass),
"Region_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_seq"'::regclass),
"RowInsertedTimestamp" date DEFAULT CURRENT_DATE,
"RowUpdatedTimestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "ZTBR_Query_Destination_pkey" PRIMARY KEY
("Master_BRACS_Secondary_Key")
)


I get the data into the destination by joining a few tables:

ALTER TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
OWNER to
---

INSERT INTO model."IMETA_ZTBR_BRACS_Model_TA_BW" (
"Company_Code",
"Posting_Period",
"Fiscal_Year",
"Profit_Center",
"Account_Number",
"Business_Process",
"Cost_Center",

Loops and Case Statements Involving Dates

2023-08-21 Thread Anthony Apollis 
*Please review my code and make recommendations where needed. I have this code:
*```
NUMBER OF LOOPS FOR POSTGRESQL ETL:
SELECT
CASE
WHEN (((EXTRACT(DAY FROM
((CASE
WHEN
(SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT 
MAX(DISTINCT "CALDAY") AS
"Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS
WEEK_NUMBER)) IS NULL THEN '2020-07-01'
ELSE
(SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT 
MAX(DISTINCT "CALDAY") AS
"Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS
WEEK_NUMBER)) + interval '1 day'
END))::timestamp - --start date
(CAST(NOW() - INTERVAL '1 day' AS 
DATE))::timestamp)-1) --end date
* -1)) <= 30 THEN 1
ELSE
CEIL(((EXTRACT(DAY FROM
((CASE
WHEN
(SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT 
MAX(DISTINCT "CALDAY") AS
"Calendar day" FROM “system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS
WEEK_NUMBER)) IS NULL THEN '2020-07-01'
ELSE
(SELECT DISTINCT "CALDAY" FROM
“system”.”IMETA_ZACTPRIC1_Cust_Pricing_TA_BW” AS WEEK_NUMBER
WHERE
WEEK_NUMBER."CALDAY" = (SELECT 
MAX(DISTINCT "CALDAY") AS
"Calendar day" FROM system."IMETA_ZINV_AP1_Invoice_data_TA_BW"" AS
WEEK_NUMBER)) + interval '1 day'
END))::timestamp - --start date
(CAST(NOW() - INTERVAL '1 day' AS 
DATE))::timestamp)-1) --end date
* -1)/30) --30 DAY INTERVALS UNLESS LESS
END
AS "Number of days"


I have re-written this code to make it less complex, still doing what
it is supposed to do. I want you to review my re-written code(code
must give me report up until yesterday):
```
WITH MaxDateCTE AS (
SELECT
COALESCE(MAX(DISTINCT "CALDAY"), '2021-07-01') AS MaxDate
FROM
"system"."IMETA_ZTRNSPCST$F_Shipment_Cost_TA_BW"
)

SELECT
CASE
WHEN EXTRACT(DAY FROM (MaxDate + INTERVAL '1 day')::timestamp
- (NOW() - INTERVAL '1 day')::timestamp) <= 30 THEN 1
ELSE CEIL(EXTRACT(DAY FROM (MaxDate + INTERVAL '1
day')::timestamp - (NOW() - INTERVAL '1 day')::timestamp) / 30)
END AS "Number of days"
FROM
MaxDateCTE;



*Full Code can be found here:
https://drive.google.com/file/d/1NaoaK0z3s3cfYilAdH4stJ1F6mq6Sc4n/view?usp=sharing
*

Re: TSQL To Postgres - Unpivot/Union All

2023-07-20 Thread Anthony Apollis 
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  wrote:

> On Thu, 20 Jul 2023 at 15:28, Anthony Apollis 
> 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
>

Fwd: TSQL To Postgres - Unpivot/Union All

2023-07-20 Thread Anthony Apollis 
-- Forwarded message -
From: Anthony Apollis 
Date: Thu, 20 Jul 2023 at 16:07
Subject: Re: TSQL To Postgres - Unpivot/Union All
To: Geoff Winkless , David G. Johnston <
david.g.johns...@gmail.com>


I am attaching my TSQL and Postgres SQL:

On Thu, 20 Jul 2023 at 15:58, Geoff Winkless  wrote:

> On Thu, 20 Jul 2023 at 13:17, Anthony Apollis 
> 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.'), 'FMmm.') 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)

Postgres SQL

2023-07-19 Thread Anthony Apollis 
Hi

What list can i post sql related errors etc?