Calendar Table
I have a Calendar table that need to be displaying Fiscal Start and End
Date as in below:
[image: f1]
The Calendar is currently have data for years 2023 - 2028:
-- Query to get the year and number of days
SELECT
EXTRACT(YEAR FROM "Date") AS "Year",
COUNT(*) AS "NumberOfDays"
FROM "Prod"."IMETA_Calendar"
GROUP BY EXTRACT(YEAR FROM "Date")
ORDER BY EXTRACT(YEAR FROM "Date");
--
[image: f5]
When i run code:
-- Query to get the year and number of days
SELECT
EXTRACT(YEAR FROM "Date") AS "Year",
COUNT(*) AS "NumberOfDays"
FROM "Prod"."IMETA_Calendar"
GROUP BY EXTRACT(YEAR FROM "Date")
ORDER BY EXTRACT(YEAR FROM "Date");
-- Table creation script
CREATE TABLE "Prod"."IMETA_Calendar" (
"Date" timestamp NOT NULL,
"FY" varchar(255),
"Period" varchar(255),
"Quarter" varchar(255),
"Day" int NOT NULL,
"Month" int NOT NULL,
"Year" int NOT NULL,
"Loaddate" timestamp NOT NULL
);
I get some output: [image: f6]
For any other Period and Year i get no results. What is wrong with my
Calendar table. Attached is the Calendar Table Data.
CREATE TABLE [Prod].[IMETA_Calendar](
[Date] [datetime] NOT NULL,
[FY] [nvarchar](255) NULL,
[Period] [nvarchar](255) NULL,
[Quarter] [nvarchar](255) NULL,
[Day] [int] NOT NULL,
[Month] [int] NOT NULL,
[Year] [int] NOT NULL,
[Loaddate] [datetime] NOT NULL
) ON [PRIMARY]
On Wed, Jul 10, 2024 at 8:38 AM Anthony Apollis <anthony.apollis@gmail.com>
wrote:
-- Query to get the year and number of days
SELECT
EXTRACT(YEAR FROM "Date") AS "Year",
COUNT(*) AS "NumberOfDays"
FROM "Prod"."IMETA_Calendar"
GROUP BY EXTRACT(YEAR FROM "Date")
ORDER BY EXTRACT(YEAR FROM "Date");
Seems odd to be using extract year from date when you have an FY column.
I get some output: [image: f6]
Nothing you've shown produces that output. I would suggest you simplify
and make self-contained a script-based test case, ideally using psql, that
you can run and share, asking questions regarding the main piece here which
is creating a table, populating it with data, and querying it. Once you
get that part solid you can figure out how to incorporate your spreadsheets
- which is mostly outside the realm of on-topic help for a
PostgreSQL mailing list.
David J.