Calendar & Fiscal Year Table
-
I have a Calendar table that need to be displaying Fiscal Start and End Date as in below:
The Calendar is currently have data for years 2023 - 2028:
-- Calculate the number of days per year in the IMETA_Calendar table
SELECT
YEAR([Date]) AS [Year],
COUNT(*) AS [NumberOfDays]
FROM [Prod].[IMETA_Calendar]
GROUP BY YEAR([Date])
ORDER BY YEAR([Date]);When i run code:
I get some output:
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]
-
you did NOT give us the SQL code you are running - so nothing for us to advise on - just giving a table content and stating you get no results is of no help to anyone.
and you have not supplied data in a consumable format either to populate the table either - so do both and maybe we will be able to help - and these should be in form of straight insert statements within your reply - not on a Excel spreadsheet.
-
By definition, a table must have a key. You also have more NULL-able columns in this one table that I have an entire schemas. Why do you need 255 Chinese characters to name quarters and periods in your data model? There is a fairly common convention for defining quarters using a sortable string. DATE date is both a reserved word in SQL as well is pretty vague. We also have a date data type and have for quite some time now.
Let's start off with some changes to your DDL
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_period CHAR(10) NOT NULL
CHECK (fiscal_period LIKE '[0-9][0-9][0-9][0-9]-Q[1-4] -P[0-9]',
) ;
Since they are not separate attributes, the fields that make up a temporal range are usually encoded as an alpha followed by integers, and separated by dashes. Thus, ' 2024-O1-P1' means the first period in the first quarter of 2024. Many SQL classes and books do not bother to explain that a field in this language has to be part of a column and that it has to have some incomplete meaning in itself. Thus, in a calendar date, the fields are {year, month, day} and they are also separated by dashes as per the ISO 8601 standards. Yes, I know you can write them as a single string of digits; spend some time with the extra punctuation, so you can use it and other applications that do follow the rules.
The temporal functions will work just fine on the cal_date column.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
By definition, a table must have a key. You also have more NULL-able columns in this one table that I have an entire schemas. Why do you need 255 Chinese characters to name quarters and periods in your data model? There is a fairly common convention for defining quarters using a sortable string. DATE date is both a reserved word in SQL as well is pretty vague. We also have a date data type and have for quite some time now.
Let's start off with some changes to your DDL
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_period CHAR(10) NOT NULL
CHECK (fiscal_period LIKE '[0-9][0-9][0-9][0-9]-Q[1-4] -P[0-9]',
) ;
Since they are not separate attributes, the fields that make up a temporal range are usually encoded as an alpha followed by integers, and separated by dashes. Thus, ' 2024-O1-P1' means the first period in the first quarter of 2024. Many SQL classes and books do not bother to explain that a field in this language has to be part of a column and that it has to have some incomplete meaning in itself. Thus, in a calendar date, the fields are {year, month, day} and they are also separated by dashes as per the ISO 8601 standards. Yes, I know you can write them as a single string of digits; spend some time with the extra punctuation, so you can use it and other applications that do follow the rules.
The temporal functions will work just fine on the cal_date column.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
If the spreadsheet is representative of your calendar table you appear to have inconsistent data. Most of the time the FY column has values like FY2021, FY2022, FY2023, FY2024, but some of the dates have FY23, FY24. These dates also appear to have Period values that are different from adjacent dates. I saw some code you supplied that contained this:
[FY] >= 'FY24'
FY2024 and FY2025 are not greater than FY24 so you may have filtered out the data you needed.
2023-02-03 00:00:00 FY2022 P2 Q3
2023-02-04 00:00:00 FY23 P7 Q3
2023-02-05 00:00:00 FY2022 P2 Q3
2023-05-05 00:00:00 FY2022 P5
2023-05-06 00:00:00 FY23 P10
2023-05-07 00:00:00 FY2022 P5
2023-08-28 00:00:00 FY24 P01
2023-08-29 00:00:00 FY24 P01
2023-08-30 00:00:00 FY24 P01
2023-08-31 00:00:00 FY24 P01
2023-09-01 00:00:00 FY2023 P9
2023-09-02 00:00:00 FY24 P2
2023-09-03 00:00:00 FY2023 P9
2023-09-04 00:00:00 FY2023 P9
2024-08-30 00:00:00 FY2024 P8
2024-08-31 00:00:00 FY25 P2
2024-09-01 00:00:00 FY2024 P9
2025-09-26 00:00:00 FY2025 P9
2025-09-27 00:00:00 FY26 P3
2025-09-28 00:00:00 FY2025 P9
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply