The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I need to generate a Calendar table - it is a very simple task for regular SQL but I'm using Microsoft Fabric and I've tried so far many options, however no success due to Fabric SQL limitations.
My table is:
CREATE TABLE dbo.CalendarData
(
DateValue DATE,
Year INT,
StartOfYear VARCHAR(20),
EndOfYear VARCHAR(20),
Month INT,
StartOfMonth VARCHAR(20),
EndOfMonth VARCHAR(20),
DaysInMonth INT,
Day INT,
DayName VARCHAR(20),
DayOfWeek INT,
MonthName VARCHAR(20),
Quarter INT,
StartOfQuarter VARCHAR(20),
EndOfQuarter VARCHAR(20)
);
Option 1: while loop says - Each SQL statement runs as an independent session. Session context does not persist across SQL statements. Learn more at https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-query-editor#limitati
DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-01-31';
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO dbo.CalendarData (
Date, Year, StartOfYear, EndOfYear,
Month, MonthName, StartOfMonth, EndOfMonth, DaysInMonth,
Day, DayName, DayOfWeek,
Quarter, StartOfQuarter, EndOfQuarter
)
VALUES (
@StartDate,
YEAR(@StartDate), -- Year
DATEFROMPARTS(YEAR(@StartDate), 1, 1), -- StartOfYear
DATEFROMPARTS(YEAR(@StartDate), 12, 31), -- EndOfYear
MONTH(@StartDate), -- Month
DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1), -- StartOfMonth
EOMONTH(@StartDate), -- EndOfMonth
DAY(EOMONTH(@StartDate)), -- DaysInMonth
DATENAME(MONTH, @StartDate), - MonthName
DAY(@StartDate), -- Day
DATENAME(WEEKDAY, @StartDate), -- DayName
(DATEPART(weekday, @StartDate) + 5) % 7 + 1, -- DayOfWeek
DATEPART(QUARTER, @StartDate), -- Quarter
CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @StartDate), 0) AS DATE), -- StartOfQuarter
EOMONTH(DATEFROMPARTS(YEAR(@StartDate), DATEPART(QUARTER, @StartDate) * 3, 1)) -- EndOfQuarter
);
SET @StartDate = DATEADD(day, 1, @StartDate);
END;
Option 2: CTE - The query references an object that is not supported in distributed processing mode
WITH Numbers AS (
SELECT TOP (DATEDIFF(day, @startDate, @endDate) + 1) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns
)
INSERT INTO dbo.Calendar (
Date, Year, StartOfYear, EndOfYear,
Month, MonthName, StartOfMonth, EndOfMonth, DaysInMonth,
Day, DayName, DayOfWeek,
Quarter, StartOfQuarter, EndOfQuarter
)
SELECT
DATEADD(day, n - 1, @startDate) AS Date
, YEAR(DATEADD(day, n - 1, @startDate)) AS Year
, DATEFROMPARTS(YEAR(DATEADD(day, n - 1, @startDate)), 1, 1) AS StartOfYear
, DATEFROMPARTS(YEAR(DATEADD(day, n - 1, @startDate)), 12, 31) AS EndOfYear
, MONTH(DATEADD(day, n - 1, @startDate)) AS Month
, DATENAME(MONTH, DATEADD(day, n - 1, @startDate)) AS MonthName
, DATEFROMPARTS(YEAR(DATEADD(day, n - 1, @startDate)), MONTH(DATEADD(day, n - 1, @startDate)), 1) AS StartOfMonth
, EOMONTH(DATEADD(day, n - 1, @startDate)) AS EndOfMonth
, DAY(EOMONTH(DATEADD(day, n - 1, @startDate))) AS DaysInMonth
, DAY(DATEADD(day, n - 1, @startDate)) AS Day
, DATENAME(WEEKDAY, DATEADD(day, n - 1, @startDate)) AS DayName
, (DATEPART(weekday, DATEADD(day, n - 1, @startDate)) + 5) % 7 + 1 AS DayOfWeek
, DATEPART(QUARTER, DATEADD(day, n - 1, @startDate)) AS Quarter
, CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, DATEADD(day, n - 1, @startDate)), 0) AS DATE) AS StartOfQuarter
, EOMONTH(DATEFROMPARTS(YEAR(DATEADD(day, n - 1, @startDate)), DATEPART(QUARTER, DATEADD(day, n - 1, @startDate)) * 3, 1)) EndOfQuarter
FROM Numbers
Option 3: Insert from Table function - The query references an object that is not supported in distributed processing mode.
INSERT INTO dbo.Calendar (
Date, Year, StartOfYear, EndOfYear,
Month, MonthName, StartOfMonth, EndOfMonth, DaysInMonth,
Day, DayName, DayOfWeek,
Quarter, StartOfQuarter, EndOfQuarter
)
SELECT
dr.Date AS Date
, YEAR(dr.Date) AS Year
, DATEFROMPARTS(YEAR(dr.Date), 1, 1) AS StartOfYear
, DATEFROMPARTS(YEAR(dr.Date), 12, 31) AS EndOfYear
, MONTH(dr.Date) AS Month
, DATENAME(MONTH, dr.Date) AS MonthName
, DATEFROMPARTS(YEAR(dr.Date), MONTH(dr.Date), 1) AS StartOfMonth
, EOMONTH(dr.Date) AS EndOfMonth
, DAY(EOMONTH(dr.Date)) AS DaysInMonth
, DAY(dr.Date) AS Day
, DATENAME(WEEKDAY, dr.Date) AS DayName
, (DATEPART(weekday, dr.Date) + 5) % 7 + 1 AS DayOfWeek
, DATEPART(QUARTER, dr.Date) AS Quarter
, CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, dr.Date), 0) AS DATE) AS StartOfQuarter
, EOMONTH(DATEFROMPARTS(YEAR(dr.Date), DATEPART(QUARTER, dr.Date) * 3, 1)) EndOfQuarter
FROM dbo.GenerateDateRange(@startDate, @endDate) AS dr;
How to generate simple data for table in Fabric??
Solved! Go to Solution.
Hi @Marusyk
The first one works, it just had some syntax issues (Date instead of DateValue for the first field, a comment that a '-' instead of '--' and MonthName was in the wrong spot in the list of fields to insert into. Here is the updated script:
DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-01-31';
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO dbo.CalendarData (
DateValue, Year, StartOfYear, EndOfYear,
Month, StartOfMonth, EndOfMonth, DaysInMonth, MonthName,
Day, DayName, DayOfWeek,
Quarter, StartOfQuarter, EndOfQuarter
)
VALUES (
@StartDate,
YEAR(@StartDate), -- Year
DATEFROMPARTS(YEAR(@StartDate), 1, 1), -- StartOfYear
DATEFROMPARTS(YEAR(@StartDate), 12, 31), -- EndOfYear
MONTH(@StartDate), -- Month
DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1), -- StartOfMonth
EOMONTH(@StartDate), -- EndOfMonth
DAY(EOMONTH(@StartDate)), -- DaysInMonth
DATENAME(MONTH, @StartDate), -- MonthName
DAY(@StartDate), -- Day
DATENAME(WEEKDAY, @StartDate), -- DayName
(DATEPART(weekday, @StartDate) + 5) % 7 + 1, -- DayOfWeek
DATEPART(QUARTER, @StartDate), -- Quarter
CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @StartDate), 0) AS DATE), -- StartOfQuarter
EOMONTH(DATEFROMPARTS(YEAR(@StartDate), DATEPART(QUARTER, @StartDate) * 3, 1)) -- EndOfQuarter
);
SET @StartDate = DATEADD(day, 1, @StartDate);
END;
I have created a repro for you:
Hope this helps. Please let me know if you have any further queries.
In case it is helpful to future searchers, I ended up using GENERATE_SERIES to generate the numbers table for my date table, which worked great.
Hi
Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.
The Solution would actually take a very long time to run because it just done one row at a time. If you want a calendar to span quite a few years, something like this would run much quicker as it is just a single insert action. You just need to locate a random table with many rows in it to piggy back.
Hi,
I have used this as a base:
https://fabric.guru/comprehensive-date-dimension-table-for-power-bi-datasets-in-fabric
and made adjustments to fit my needs. Then I have created a view in my warehouse based on that table and further requirements. Maybe it can help you?
//Alexander
Thank you, it is very useful but I don't use Spark
Hi @Marusyk
The first one works, it just had some syntax issues (Date instead of DateValue for the first field, a comment that a '-' instead of '--' and MonthName was in the wrong spot in the list of fields to insert into. Here is the updated script:
DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-01-31';
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO dbo.CalendarData (
DateValue, Year, StartOfYear, EndOfYear,
Month, StartOfMonth, EndOfMonth, DaysInMonth, MonthName,
Day, DayName, DayOfWeek,
Quarter, StartOfQuarter, EndOfQuarter
)
VALUES (
@StartDate,
YEAR(@StartDate), -- Year
DATEFROMPARTS(YEAR(@StartDate), 1, 1), -- StartOfYear
DATEFROMPARTS(YEAR(@StartDate), 12, 31), -- EndOfYear
MONTH(@StartDate), -- Month
DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1), -- StartOfMonth
EOMONTH(@StartDate), -- EndOfMonth
DAY(EOMONTH(@StartDate)), -- DaysInMonth
DATENAME(MONTH, @StartDate), -- MonthName
DAY(@StartDate), -- Day
DATENAME(WEEKDAY, @StartDate), -- DayName
(DATEPART(weekday, @StartDate) + 5) % 7 + 1, -- DayOfWeek
DATEPART(QUARTER, @StartDate), -- Quarter
CAST(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @StartDate), 0) AS DATE), -- StartOfQuarter
EOMONTH(DATEFROMPARTS(YEAR(@StartDate), DATEPART(QUARTER, @StartDate) * 3, 1)) -- EndOfQuarter
);
SET @StartDate = DATEADD(day, 1, @StartDate);
END;
I have created a repro for you:
Hope this helps. Please let me know if you have any further queries.
Thank you, anonymous to fix Marusik code : it works ! this is pretty useful to see how to use SQL in a FDW, which is not as obvious as classic T-SQL
Hi @Marusyk
Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.
Hi @Marusyk
Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this. We will update you once we hear back from them.
Thanks
User | Count |
---|---|
14 | |
9 | |
5 | |
3 | |
2 |
User | Count |
---|---|
44 | |
23 | |
17 | |
13 | |
12 |