June 3, 2019 at 5:56 pm
I can see that, so another try with the code again. If I've done dup posts, admins please delete the extraneous post(s).
I've been extremely busy and so haven't had time to correct my code until now.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION dbo.GetMonthStartAndEndDates (
@date date
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN (
SELECT
DATEADD(DAY, -DATEDIFF(DAY, 0, first_day_of_month) % 7,
first_day_of_month) AS start_day_of_month, /*first Monday*/
DATEADD(DAY, -DATEDIFF(DAY, 6, last_day_of_month) % 7,
last_day_of_month) AS end_day_of_month /*last Sunday*/
FROM (
SELECT CASE WHEN @date >= DATEADD(DAY, -DATEDIFF(DAY, 0,
DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date) + 1, 0))) % 7,
DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date) + 1, 0)))
THEN 1 ELSE 0 END AS date_is_last_monday_of_month_or_later
) AS calc_which_month
CROSS APPLY (
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, @date) +
date_is_last_monday_of_month_or_later, 0) AS first_day_of_month,
DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date) + 1 +
date_is_last_monday_of_month_or_later, 0)) AS last_day_of_month
) AS calc_first_and_last_day_of_month
)
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 3, 2019 at 10:09 pm
Jeff, Yes I didn't explain the requirements as well as I could have.
Nah... you did fine. It was just a little different and the year's worth of month dates made things pretty clear. Thanks again for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2020 at 3:32 pm
I was pointed to this thread by the OP, Simon, a colleague of mine. It's that time of year where we start to look at tables used in various databases that are used for reporting. While Simon's question was answered quite brilliantly in the thread, he didn't quite ask the right question, probably because he wasn't given the rules. The rules the organisation we work for want to use are quite simple, with a couple of caveats.
We want to use a 4/4/5 week period calendar. That is to say each quarter is 3 months of 4 weeks, 4 weeks followed by a month of 5 weeks. (This is different from before, we used to do SOMonth to EOMonth, but that's not Simon's question. But I digress).
So I have created the following table and Stored Procedure to update the table as and when required
Note: We are in the UK so Weeks start on a Monday. Further note we set an arbitrary start date for 2020 which may differ from you. This is included in the setup script here
IF OBJECT_ID('dbo.Calendar445') IS NOT NULL
DROP TABLE dbo.Calendar445;
CREATE TABLE dbo.Calendar445 (PKDate DATETIME PRIMARY KEY CLUSTERED
, fiscal_year SMALLINT
, fiscal_month TINYINT
, fiscal_quarter TINYINT
, fiscal_quarter_desc VARCHAR(10)
, year SMALLINT
, month TINYINT
, quarter TINYINT
, quarter_desc VARCHAR(10));
INSERT INTO dbo.Calendar445 (
PKDate
, fiscal_year
, fiscal_quarter
, fiscal_quarter_desc
, fiscal_month
, year
, quarter
, quarter_desc
, month
)
--we insert the end of the last known fiscal_year. Required.
VALUES (N'2019-12-28T00:00:00', 2019, 4, 'Q4 2019', 12, 2019, 4, 'Q4 2019', 12)
, (N'2019-12-29T00:00:00', 2019, 4, 'Q4 2019', 12, 2019, 4, 'Q4 2019', 12)
, (N'2019-12-30T00:00:00', 2020, 1, 'Q1 2020', 1, 2019, 4, 'Q4 2019', 12)
, (N'2019-12-31T00:00:00', 2020, 1, 'Q1 2020', 1, 2019, 4, 'Q4 2019', 12);
DECLARE @SomeStartDate DATETIME = '2020' --Inclusive
, @SomeEndDate DATETIME = '2100' --Exclusive
;
WITH cteGenTestDates
AS (SELECT TOP (DATEDIFF(dd, @SomeStartDate, @SomeEndDate)) @SomeStartDate
+ ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 SomeDate
FROM
sys.all_columns ac1
CROSS JOIN sys.all_columns ac2)
INSERT dbo.Calendar445 (PKDate)
SELECT gtd.SomeDate
FROM
cteGenTestDates gtd;
SET DATEFIRST 1;
UPDATE t
SET t.year = YEAR(t.PKDate)
, t.fiscal_year = YEAR(t.PKDate)
, t.fiscal_quarter = 0
, t.fiscal_quarter_desc = 'Q0'
, t.fiscal_month = MONTH(t.PKDate)
, t.quarter = DATEPART(q, t.PKDate)
, t.quarter_desc = CONCAT('Q', CAST(DATEPART(q, t.PKDate) AS VARCHAR(12)), ' ', CAST(YEAR(t.PKDate) AS CHAR(4)))
, t.month = MONTH(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.PKDate >= '2020-01-01';
I then created a Stored Procedure
IF OBJECT_ID('dbo.usp_UpdateCalendar445') IS NOT NULL
DROP PROCEDURE dbo.usp_UpdateCalendar445;
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.usp_UpdateCalendar445
@StartYear INT
, @FiftyThreeWeeks BIT = 0
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET DATEFIRST 1;
DECLARE @CurrentYear INT = @StartYear;
DECLARE @PreviousEndDate DATE;
--DECLARE @FiftyThreeWeeks BIT = 0
DECLARE @resetFiftyThreeWeeks BIT = 1;
--Get the starting point. This must Exist.
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 12
AND t.fiscal_year = @CurrentYear - 1;
--Reset all dates in Table
UPDATE t
SET t.fiscal_month = 0
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate;
--Renumber fiscal_ columns for the year passed in and ALL following ones.
--This will need re-running when it is decided which upcoming year will have 53 weeks
WHILE EXISTS (SELECT 1 FROM dbo.Calendar445 AS t WHERE t.fiscal_month = 0)
BEGIN
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 12
AND t.fiscal_year = @CurrentYear - 1;
UPDATE t
SET t.fiscal_year = @CurrentYear
, t.fiscal_month = 1
, t.fiscal_quarter = 1
, t.fiscal_quarter_desc = CONCAT('Q1 ', @CurrentYear)
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate
AND t.PKDate <= DATEADD(wk, 4, @PreviousEndDate);
--Reset for next month
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 1
AND t.fiscal_year = @CurrentYear;
UPDATE t
SET t.fiscal_year = @CurrentYear
, t.fiscal_month = 2
, t.fiscal_quarter = 1
, t.fiscal_quarter_desc = CONCAT('Q1 ', @CurrentYear)
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate
AND t.PKDate <= DATEADD(wk, 4, @PreviousEndDate);
--Reset for next month
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 2
AND t.fiscal_year = @CurrentYear;
UPDATE t
SET t.fiscal_year = @CurrentYear
, t.fiscal_month = 3
, t.fiscal_quarter = 1
, t.fiscal_quarter_desc = CONCAT('Q1 ', @CurrentYear)
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate
AND t.PKDate <= DATEADD(wk, 5, @PreviousEndDate);
--Reset for next month
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 3
AND t.fiscal_year = @CurrentYear;
UPDATE t
SET t.fiscal_year = @CurrentYear
, t.fiscal_month = 4
, t.fiscal_quarter = 2
, t.fiscal_quarter_desc = CONCAT('Q2 ', @CurrentYear)
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate
AND t.PKDate <= DATEADD(wk, 4, @PreviousEndDate);
--Reset for next month
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 4
AND t.fiscal_year = @CurrentYear;
UPDATE t
SET t.fiscal_year = @CurrentYear
, t.fiscal_month = 5
, t.fiscal_quarter = 2
, t.fiscal_quarter_desc = CONCAT('Q2 ', @CurrentYear)
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate
AND t.PKDate <= DATEADD(wk, 4, @PreviousEndDate);
--Reset for next month
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 5
AND t.fiscal_year = @CurrentYear;
UPDATE t
SET t.fiscal_year = @CurrentYear
, t.fiscal_month = 6
, t.fiscal_quarter = 2
, t.fiscal_quarter_desc = CONCAT('Q2 ', @CurrentYear)
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate
AND t.PKDate <= DATEADD(wk, 5, @PreviousEndDate);
--Reset for next month
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 6
AND t.fiscal_year = @CurrentYear;
UPDATE t
SET t.fiscal_year = @CurrentYear
, t.fiscal_month = 7
, t.fiscal_quarter = 3
, t.fiscal_quarter_desc = CONCAT('Q3 ', @CurrentYear)
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate
AND t.PKDate <= DATEADD(wk, 4, @PreviousEndDate);
--Reset for next month
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 7
AND t.fiscal_year = @CurrentYear;
UPDATE t
SET t.fiscal_year = @CurrentYear
, t.fiscal_month = 8
, t.fiscal_quarter = 3
, t.fiscal_quarter_desc = CONCAT('Q3 ', @CurrentYear)
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate
AND t.PKDate <= DATEADD(wk, 4, @PreviousEndDate);
--Reset for next month
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 8
AND t.fiscal_year = @CurrentYear;
UPDATE t
SET t.fiscal_year = @CurrentYear
, t.fiscal_month = 9
, t.fiscal_quarter = 3
, t.fiscal_quarter_desc = CONCAT('Q3 ', @CurrentYear)
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate
AND t.PKDate <= DATEADD(wk, 5, @PreviousEndDate);
--Reset for next month
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 9
AND t.fiscal_year = @CurrentYear;
UPDATE t
SET t.fiscal_year = @CurrentYear
, t.fiscal_month = 10
, t.fiscal_quarter = 4
, t.fiscal_quarter_desc = CONCAT('Q4 ', @CurrentYear)
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate
AND t.PKDate <= DATEADD(wk, 4, @PreviousEndDate);
--Reset for next month
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 10
AND t.fiscal_year = @CurrentYear;
UPDATE t
SET t.fiscal_year = @CurrentYear
, t.fiscal_month = 11
, t.fiscal_quarter = 4
, t.fiscal_quarter_desc = CONCAT('Q4 ', @CurrentYear)
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate
AND t.PKDate <= DATEADD(wk, 4, @PreviousEndDate);
--Reset for next month
SELECT @PreviousEndDate = MAX(t.PKDate)
FROM
dbo.Calendar445 AS t
WHERE
t.fiscal_month = 11
AND t.fiscal_year = @CurrentYear;
UPDATE t
SET t.fiscal_year = @CurrentYear
, t.fiscal_month = 12
, t.fiscal_quarter = 4
, t.fiscal_quarter_desc = CONCAT('Q4 ', @CurrentYear)
FROM
dbo.Calendar445 t
WHERE
t.PKDate > @PreviousEndDate
AND t.PKDate <= CASE --If @FiftyThreeWeeks = 1 --we want to do 6 weeks on the first pass through this loop
WHEN @FiftyThreeWeeks = 1
AND @resetFiftyThreeWeeks = 1 THEN DATEADD(wk, 6, @PreviousEndDate)
ELSE DATEADD(wk, 5, @PreviousEndDate)
END;
SELECT @CurrentYear = @CurrentYear + 1;
--We only want to do this for the starting year
SELECT @resetFiftyThreeWeeks = 0;
END;
END;
GO
We then initialise it with a call to the SP
-- Do the first update
EXEC dbo.usp_UpdateCalendar445 @StartYear = 2020
/*
Despite the fact there is an ISO standard, we ignore it.
https://en.wikipedia.org/wiki/ISO_week_date
Our organisation has decided 2021 will have 53 weeks. This makes that so.
EXEC dbo.usp_UpdateCalendar445 @StartYear = 2021, @FiftyThreeWeeks = 1
*/
And then check the results
--check results
;WITH CTE
AS (SELECT MIN(ct2.PKDate) fiscal_start_date
, MAX(ct2.PKDate) fiscal_end_date
, ct2.fiscal_year
, ct2.fiscal_month
FROM
dbo.Calendar445 ct2
GROUP BY
ct2.fiscal_year
, ct2.fiscal_month)
SELECT t.PKDate
, t.fiscal_year
, t.fiscal_month
, t.fiscal_quarter
, t.fiscal_quarter_desc
, t.year
, t.month
, t.quarter
, t.quarter_desc
, c.fiscal_start_date
, c.fiscal_end_date
, DATEDIFF(dd, c.fiscal_start_date, t.PKDate) / 7 + 1 WeekOfMonth
, DATEDIFF(dd, c.fiscal_start_date, c.fiscal_end_date + 1) / 7 WeeksInMonth
FROM
dbo.Calendar445 AS t
LEFT JOIN CTE c
ON t.fiscal_year = c.fiscal_year
AND t.fiscal_month = c.fiscal_month
WHERE DATEDIFF(dd, c.fiscal_start_date, c.fiscal_end_date + 1) / 7 > 0
ORDER BY
t.PKDate ASC;
When in the future it is decided that 2027 (say) will have 53 weeks, we can run the proc and all the periods both for and after the given year will be updated
I hope someone finds this useful.
Regards
Dave Jackson
September 10, 2020 at 3:52 pm
My recommendation is that you, if you're using them, that you forget you ever saw "incremental rCTEs" regardless of the number of rows involved. Please see the following article for why.
https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2020 at 4:01 pm
You may be interested in this script: https://www.sqlservercentral.com/?p=3787591&post_type=ssc_script&preview_id=3787591
This script generates the calendar for a 3 year period - with the ability to restate the calendar - and generates the 4-5-4, 4-4-5 and 5-4-4 values. Normally, for a 4-4-5 calendar with 53 weeks the calendar is generated as a 4-5-5 moving the extra week to the previous quarter instead of adding the extra week to the end as a 4-4-6. If you need it as 4-4-6 that is easy to accomplish by using the values calculated for the 4-4-5 instead of how the code now uses the calculated values for the 4-5-4 (which generates 4-5-5 when there are 53 weeks).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 10, 2020 at 4:26 pm
@jeff - I didn't think I was using an incremental rCTE?
@Jeffrey - I did try and and d/l a couple of scripts, but I couldn't get them to run, or be clever enough to make them work. (Not necessarily the one you're pointing to). So I wrote this sledge hammer of a script. I know it's a loop but as the SP is to be run once every 4 - 5 years I'll accept that.
Cheers
Dave J
September 10, 2020 at 7:26 pm
@Jeff - I didn't think I was using an incremental rCTE?
Sorry, it wasn't directed at you. It was a post I commented on when it first came out and I, once again, saw the older posts that recommended using rCTE's and had posts to links that showed how to use them and, since someone necro'd this post, I thought I'd post a reminder to those that might be reading it as a result of it being necro'd.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2020 at 7:42 pm
@jeff - for some reason the post you quoted in your previous post isn't showing the quote. Your reply to me is though! When I view in Gmail on my phone I can see it, but not on the Web?
Cheers
Dave J
September 10, 2020 at 8:05 pm
@jeff - for some reason the post you quoted in your previous post isn't showing the quote. Your reply to me is though! When I view in Gmail on my phone I can see it, but not on the Web?
Cheers
Dave J
Dunno... it's either your phone or the software on this site. It's working just fine for me.
I guess now would be a good time to ask why you don't follow ISO for weeks if you're using Mondays as the first day of every week.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2020 at 8:38 pm
It's a good question. I don't get to decide, the business do. The rules are arbitrary so I have to use a fixed table, in fact several. This (table calendar445) gives me a base to update the various tables that we use to report from. 53 week years are the bane of my life as we have year on year reports used by the top brass that compare this week's performance with the same week last year. As well as this time last week, last month etc. I'm away from my PC right now but I'll post the definition of my main calendar table with the insane logic we wrote to handle this tomorrow. We use this in powerBI reports.
Stay safe & all that jazz
Dave J
September 10, 2020 at 9:11 pm
It's a good question. I don't get to decide, the business do. The rules are arbitrary so I have to use a fixed table, in fact several. This (table calendar445) gives me a base to update the various tables that we use to report from. 53 week years are the bane of my life as we have year on year reports used by the top brass that compare this week's performance with the same week last year. As well as this time last week, last month etc. I'm away from my PC right now but I'll post the definition of my main calendar table with the insane logic we wrote to handle this tomorrow. We use this in powerBI reports.
Stay safe & all that jazz
Dave J
I would be interested in seeing how the business alters a traditional (standard?) 4-4-5 calendar. The problems with any of the 52/53 week calendars is where the calendar has to be restated. For comparisons - you have to use the appropriate version so that the comparisons are like for like.
For example - in a non-restated version for years 2016, 2017, 2018 the start of the year (for NRF which starts at the end of January) the first of the year is: 2016-01-31, 2017-01-29 and 2018-02-04. Using the non-restated version we can compare 2016 to 2017 and be in the 'same' week - but cannot compare to 2018 because the week is shifted. Re-stating the calendar gives us: 2016-01-31, 2017-02-05 and 2018-02-04 and now we can compare like for like between 2017 and 2018 (note: 2017 has 53 weeks).
If there is a requirement for comparing across more than 2 years and one of those years has 53 weeks - then you have some problems. But generally, reporting is not across more than 2 years so the appropriate version can be utilized until the next 53 week year occurs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 10, 2020 at 10:27 pm
This is the main table definition, I warned you it was insane. 😉
CREATE TABLE dbo.tblCalendar (PKDate DATE NOT NULL
, year SMALLINT NULL
, fiscal_year SMALLINT NULL
, fiscal_quarter TINYINT NULL
, fiscal_quarter_desc VARCHAR(10) COLLATE Latin1_General_CI_AS NULL
, quarter TINYINT NULL
, quarter_desc VARCHAR(10) COLLATE Latin1_General_CI_AS NULL
, month TINYINT NULL
, month_name_long VARCHAR(30) COLLATE Latin1_General_CI_AS NULL
, month_name_short VARCHAR(10) COLLATE Latin1_General_CI_AS NULL
, week_in_year TINYINT NULL
, week_in_month TINYINT NULL
, day_in_year SMALLINT NULL
, day_in_week TINYINT NULL
, day_in_month TINYINT NULL
, dmy_name_long VARCHAR(30) COLLATE Latin1_General_CI_AS NULL
, dmy_name_long_with_suffix VARCHAR(30) COLLATE Latin1_General_CI_AS NULL
, day_name_long VARCHAR(10) COLLATE Latin1_General_CI_AS NULL
, day_name_short VARCHAR(10) COLLATE Latin1_General_CI_AS NULL
, description VARCHAR(100) COLLATE Latin1_General_CI_AS NULL
, is_weekend TINYINT NULL
, is_holiday TINYINT NULL
, is_workday TINYINT NULL
, current_week AS
(CONVERT(
BIT
, CASE
WHEN DATEPART(iso_week, PKDate) = DATEPART(
iso_week, CONVERT(DATE, GETDATE()))
AND ( DATEPART(YEAR, PKDate) >= (DATEPART(YEAR, CONVERT(DATE, GETDATE()))
- (1))
AND DATEPART(YEAR, PKDate) <= (DATEPART(YEAR, CONVERT(DATE, GETDATE()))
+ (1)))
AND ( DATEDIFF(DAY, (0), CONVERT(DATE, GETDATE())) >= (DATEDIFF(
DAY, (0), PKDate)
- (30))
AND DATEDIFF(DAY, (0), CONVERT(DATE, GETDATE())) <= (DATEDIFF(
DAY, (0), PKDate)
+ (30))) THEN (1)
ELSE (0)
END))
, last_week AS
(CONVERT(
BIT
, CASE
WHEN DATEPART(iso_week, DATEADD(WEEK, (1), PKDate)) = DATEPART(
iso_week
, CONVERT(
DATE, GETDATE()))
AND ( DATEDIFF(DAY, (0), CONVERT(DATE, GETDATE())) >= (DATEDIFF(
DAY, (0), PKDate)
- (30))
AND DATEDIFF(DAY, (0), CONVERT(DATE, GETDATE())) <= (DATEDIFF(
DAY, (0), PKDate)
+ (30))) THEN (1)
ELSE (0)
END))
, last_seven_days AS
(CONVERT(BIT
, CASE
WHEN PKDate >= DATEADD(DAY, (-6), CONVERT(DATE, GETDATE()))
AND PKDate <= CONVERT(DATE, GETDATE()) THEN (1)
ELSE (0)
END))
, next_seven_days AS
(CONVERT(BIT
, CASE
WHEN PKDate >= CONVERT(DATE, GETDATE())
AND PKDate <= DATEADD(DAY, (6), CONVERT(DATE, GETDATE())) THEN (1)
ELSE (0)
END))
, previous_seven_days AS
(CONVERT(
BIT
, CASE
WHEN PKDate >= DATEADD(DAY, (-13), CONVERT(DATE, GETDATE()))
AND PKDate <= DATEADD(DAY, (-7), CONVERT(DATE, GETDATE())) THEN (1)
ELSE (0)
END))
, current_week_last_year AS
(CONVERT(
BIT
, CASE
WHEN DATEPART(iso_week, CONVERT(DATE, GETDATE())) = (53) THEN
CASE
WHEN DATEPART(iso_week, DATEADD(YEAR, (-1), PKDate)) = (52)
AND DATEPART(YEAR, PKDate) = DATEPART(
YEAR
, DATEADD(
YEAR
, (-1)
, CONVERT(DATE, GETDATE()))) THEN
(1)
ELSE (0)
END
ELSE
CASE
WHEN DATEPART(iso_week, PKDate) = DATEPART(
iso_week
, CONVERT(DATE, GETDATE()))
AND ( DATEPART(YEAR, PKDate) >= (DATEPART(
YEAR, CONVERT(DATE, GETDATE()))
- (1))
AND DATEPART(YEAR, PKDate) <= (DATEPART(
YEAR, CONVERT(DATE, GETDATE()))
+ (1)))
AND ( DATEDIFF(DAY, (0), PKDate) >= (DATEDIFF(
DAY
, (0)
, DATEADD(
YEAR
, (-1)
, CONVERT(
DATE, GETDATE())))
- (30))
AND DATEDIFF(DAY, (0), PKDate) <= (DATEDIFF(
DAY
, (0)
, DATEADD(
YEAR
, (-1)
, CONVERT(
DATE, GETDATE())))
+ (30))) THEN (1)
ELSE (0)
END
END))
, last_week_last_year AS
(CONVERT(
BIT
, CASE
WHEN DATEPART(iso_week, PKDate) = DATEPART(
iso_week
, DATEADD(
WEEK
, (-1)
, CONVERT(DATE, GETDATE())))
AND DATEPART(YEAR, PKDate) = DATEPART(
YEAR
, DATEADD(
YEAR, (-1), CONVERT(DATE, GETDATE()))) THEN
(1)
ELSE (0)
END))
, current_quarter AS
(CONVERT(
BIT
, CASE
WHEN DATEPART(iso_week, PKDate) >= (1)
AND DATEPART(iso_week, PKDate) <= (13)
AND ( DATEPART(iso_week, CONVERT(DATE, GETDATE())) >= (1)
AND DATEPART(iso_week, CONVERT(DATE, GETDATE())) <= (13))
AND DATEPART(YEAR, PKDate) = DATEPART(YEAR, CONVERT(DATE, GETDATE())) THEN
(1)
WHEN DATEPART(iso_week, PKDate) > (39)
AND DATEPART(iso_week, CONVERT(DATE, GETDATE())) > (39)
AND DATEPART(YEAR, PKDate) = DATEPART(YEAR, CONVERT(DATE, GETDATE())) THEN
(1)
WHEN DATEPART(iso_week, PKDate) >= (14)
AND DATEPART(iso_week, PKDate) <= (26)
AND ( DATEPART(iso_week, CONVERT(DATE, GETDATE())) >= (14)
AND DATEPART(iso_week, CONVERT(DATE, GETDATE())) <= (26))
AND DATEPART(YEAR, PKDate) = DATEPART(YEAR, CONVERT(DATE, GETDATE())) THEN
(1)
WHEN DATEPART(iso_week, PKDate) >= (27)
AND DATEPART(iso_week, PKDate) <= (39)
AND ( DATEPART(iso_week, CONVERT(DATE, GETDATE())) >= (27)
AND DATEPART(iso_week, CONVERT(DATE, GETDATE())) <= (39))
AND DATEPART(YEAR, PKDate) = DATEPART(YEAR, CONVERT(DATE, GETDATE())) THEN
(1)
ELSE (0)
END))
, last_quarter AS
(CONVERT(
BIT
, CASE
WHEN (fiscal_quarter = (3) OR fiscal_quarter = (2) OR fiscal_quarter = (1))
AND (fiscal_quarter + (1)) = CASE
WHEN DATEPART(iso_week, GETDATE()) >= (1)
AND DATEPART(iso_week, GETDATE()) <= (13) THEN
(1)
WHEN DATEPART(iso_week, GETDATE()) >= (14)
AND DATEPART(iso_week, GETDATE()) <= (26) THEN
(2)
WHEN DATEPART(iso_week, GETDATE()) >= (27)
AND DATEPART(iso_week, GETDATE()) <= (39) THEN
(3)
ELSE (4)
END
AND fiscal_year = CASE
WHEN DATEPART(iso_week, GETDATE()) > (50)
AND DATEPART(MONTH, GETDATE()) < (7) THEN
DATEPART(YEAR, GETDATE()) - (1)
WHEN DATEPART(iso_week, GETDATE()) = (1)
AND DATEPART(MONTH, GETDATE()) > (7) THEN
DATEPART(YEAR, GETDATE()) + (1)
ELSE DATEPART(YEAR, GETDATE())
END THEN (1)
WHEN fiscal_quarter = (4)
AND (1) = CASE
WHEN DATEPART(iso_week, GETDATE()) >= (1)
AND DATEPART(iso_week, GETDATE()) <= (13) THEN (1)
WHEN DATEPART(iso_week, GETDATE()) >= (14)
AND DATEPART(iso_week, GETDATE()) <= (26) THEN (2)
WHEN DATEPART(iso_week, GETDATE()) >= (27)
AND DATEPART(iso_week, GETDATE()) <= (39) THEN (3)
ELSE (4)
END
AND (fiscal_year + (1)) = CASE
WHEN DATEPART(iso_week, GETDATE()) > (50)
AND DATEPART(MONTH, GETDATE()) < (7) THEN
DATEPART(YEAR, GETDATE()) - (1)
WHEN DATEPART(iso_week, GETDATE()) = (1)
AND DATEPART(MONTH, GETDATE()) > (7) THEN
DATEPART(YEAR, GETDATE()) + (1)
ELSE DATEPART(YEAR, GETDATE())
END THEN (1)
ELSE (0)
END))
, current_quarter_last_year AS
(CONVERT(
BIT
, CASE
WHEN fiscal_quarter = CASE
WHEN DATEPART(iso_week, GETDATE()) >= (1)
AND DATEPART(iso_week, GETDATE()) <= (13) THEN
(1)
WHEN DATEPART(iso_week, GETDATE()) >= (14)
AND DATEPART(iso_week, GETDATE()) <= (26) THEN
(2)
WHEN DATEPART(iso_week, GETDATE()) >= (27)
AND DATEPART(iso_week, GETDATE()) <= (39) THEN
(3)
ELSE (4)
END
AND (fiscal_year + (1)) = CASE
WHEN DATEPART(iso_week, GETDATE()) > (50)
AND DATEPART(MONTH, GETDATE()) < (7) THEN
DATEPART(YEAR, GETDATE()) - (1)
WHEN DATEPART(iso_week, GETDATE()) = (1)
AND DATEPART(MONTH, GETDATE()) > (7) THEN
DATEPART(YEAR, GETDATE()) + (1)
ELSE DATEPART(YEAR, GETDATE())
END THEN (1)
ELSE (0)
END))
, YesterdayQuotes AS
(CONVERT(
BIT
, CASE
WHEN PKDate = DATEADD(DAY, (-1), CONVERT(DATE, GETDATE())) THEN (1)
ELSE (0)
END))
, YesterdaylastWeekQuotes AS
(CONVERT(
BIT
, CASE
WHEN DATEADD(DAY, (-7), CONVERT(DATE, GETDATE() - (1))) = PKDate THEN (1)
ELSE (0)
END))
, YesterdayStock AS
(CONVERT(
BIT
, CASE
WHEN PKDate = CASE
WHEN DATENAME(WEEKDAY, CONVERT(DATE, GETDATE())) = 'Monday' THEN
DATEADD(DAY, (-3), CONVERT(DATE, GETDATE()))
WHEN DATENAME(WEEKDAY, CONVERT(DATE, GETDATE())) = 'Sunday' THEN
DATEADD(DAY, (-2), CONVERT(DATE, GETDATE()))
ELSE DATEADD(DAY, (-1), CONVERT(DATE, GETDATE()))
END THEN (1)
ELSE (0)
END))
, YesterdayLastWeekStock AS
(CONVERT(
BIT
, CASE
WHEN PKDate = DATEADD(
DAY
, (-7)
, CASE
WHEN DATENAME(WEEKDAY, CONVERT(DATE, GETDATE())) = 'Monday' THEN
DATEADD(DAY, (-3), CONVERT(DATE, GETDATE()))
WHEN DATENAME(WEEKDAY, CONVERT(DATE, GETDATE())) = 'Sunday' THEN
DATEADD(DAY, (-2), CONVERT(DATE, GETDATE()))
ELSE DATEADD(DAY, (-1), CONVERT(DATE, GETDATE()))
END) THEN (1)
ELSE (0)
END))
, UD_Day_Adjusted_Week AS
(CONVERT(
BIT
, CASE
WHEN DATEPART(iso_week, PKDate) = DATEPART(
iso_week
, CONVERT(
DATE
, CASE
WHEN DATENAME(
WEEKDAY
, CONVERT(
DATE, GETDATE())) = 'Monday' THEN
DATEADD(
DAY
, (-7)
, CONVERT(
DATE, GETDATE()))
WHEN DATENAME(
WEEKDAY
, CONVERT(
DATE, GETDATE())) = 'Tuesday' THEN
DATEADD(
DAY
, (-8)
, CONVERT(
DATE, GETDATE()))
ELSE
DATEADD(
DAY
, (-1)
, CONVERT(
DATE, GETDATE()))
END))
AND ( DATEPART(YEAR, PKDate) >= (DATEPART(
YEAR
, CONVERT(
DATE
, CASE
WHEN DATENAME(
WEEKDAY
, CONVERT(
DATE, GETDATE())) = 'Monday' THEN
DATEADD(
DAY
, (-7)
, CONVERT(
DATE, GETDATE()))
WHEN DATENAME(
WEEKDAY
, CONVERT(
DATE, GETDATE())) = 'Tuesday' THEN
DATEADD(
DAY
, (-8)
, CONVERT(
DATE, GETDATE()))
ELSE
DATEADD(
DAY
, (-1)
, CONVERT(
DATE, GETDATE()))
END)) - (1))
AND DATEPART(YEAR, PKDate) <= (DATEPART(
YEAR
, CONVERT(
DATE
, CASE
WHEN DATENAME(
WEEKDAY
, CONVERT(
DATE, GETDATE())) = 'Monday' THEN
DATEADD(
DAY
, (-7)
, CONVERT(
DATE, GETDATE()))
WHEN DATENAME(
WEEKDAY
, CONVERT(
DATE, GETDATE())) = 'Tuesday' THEN
DATEADD(
DAY
, (-8)
, CONVERT(
DATE, GETDATE()))
ELSE
DATEADD(
DAY
, (-1)
, CONVERT(
DATE, GETDATE()))
END)) + (1)))
AND ( DATEDIFF(
DAY
, (0)
, CONVERT(
DATE
, CASE
WHEN DATENAME(WEEKDAY, CONVERT(DATE, GETDATE())) = 'Monday' THEN
DATEADD(DAY, (-7), CONVERT(DATE, GETDATE()))
WHEN DATENAME(WEEKDAY, CONVERT(DATE, GETDATE())) = 'Tuesday' THEN
DATEADD(DAY, (-8), CONVERT(DATE, GETDATE()))
ELSE DATEADD(DAY, (-1), CONVERT(DATE, GETDATE()))
END)) >= (DATEDIFF(DAY, (0), PKDate) - (30))
AND DATEDIFF(
DAY
, (0)
, CONVERT(
DATE
, CASE
WHEN DATENAME(WEEKDAY, CONVERT(DATE, GETDATE())) = 'Monday' THEN
DATEADD(DAY, (-7), CONVERT(DATE, GETDATE()))
WHEN DATENAME(WEEKDAY, CONVERT(DATE, GETDATE())) = 'Tuesday' THEN
DATEADD(DAY, (-8), CONVERT(DATE, GETDATE()))
ELSE DATEADD(DAY, (-1), CONVERT(DATE, GETDATE()))
END)) <= (DATEDIFF(DAY, (0), PKDate) + (30)))
AND PKDate < CONVERT(DATE, GETDATE()) THEN (1)
ELSE (0)
END))
, UD_Day_Adjusted_previous_Week AS
(CONVERT(
BIT
, CASE
WHEN DATEPART(iso_week, PKDate) = DATEPART(
iso_week
, CONVERT(
DATE
, DATEADD(
DAY
, (-7)
, CASE
WHEN DATENAME(
WEEKDAY
, CONVERT(
DATE
, GETDATE())) = 'Monday' THEN
DATEADD(
DAY
, (-7)
, CONVERT(
DATE
, GETDATE()))
WHEN DATENAME(
WEEKDAY
, CONVERT(
DATE
, GETDATE())) = 'Tuesday' THEN
DATEADD(
DAY
, (-8)
, CONVERT(
DATE
, GETDATE()))
ELSE
DATEADD(
DAY
, (-1)
, CONVERT(
DATE
, GETDATE()))
END)))
AND ( DATEPART(YEAR, PKDate) >= (DATEPART(
YEAR
, CONVERT(
DATE
, DATEADD(
DAY
, (-7)
, CASE
WHEN DATENAME(
WEEKDAY
, CONVERT(
DATE
, GETDATE())) = 'Monday' THEN
DATEADD(
DAY
, (-7)
, CONVERT(
DATE
, GETDATE()))
WHEN DATENAME(
WEEKDAY
, CONVERT(
DATE
, GETDATE())) = 'Tuesday' THEN
DATEADD(
DAY
, (-8)
, CONVERT(
DATE
, GETDATE()))
ELSE
DATEADD(
DAY
, (-1)
, CONVERT(
DATE
, GETDATE()))
END))) - (1))
AND DATEPART(YEAR, PKDate) <= (DATEPART(
YEAR
, CONVERT(
DATE
, DATEADD(
DAY
, (-7)
, CASE
WHEN DATENAME(
WEEKDAY
, CONVERT(
DATE
, GETDATE())) = 'Monday' THEN
DATEADD(
DAY
, (-7)
, CONVERT(
DATE
, GETDATE()))
WHEN DATENAME(
WEEKDAY
, CONVERT(
DATE
, GETDATE())) = 'Tuesday' THEN
DATEADD(
DAY
, (-8)
, CONVERT(
DATE
, GETDATE()))
ELSE
DATEADD(
DAY
, (-1)
, CONVERT(
DATE
, GETDATE()))
END))) + (1)))
AND ( DATEDIFF(
DAY
, (0)
, CONVERT(
DATE
, CASE
WHEN DATENAME(WEEKDAY, CONVERT(DATE, GETDATE())) = 'Monday' THEN
DATEADD(DAY, (-7), CONVERT(DATE, GETDATE()))
WHEN DATENAME(WEEKDAY, CONVERT(DATE, GETDATE())) = 'Tuesday' THEN
DATEADD(DAY, (-8), CONVERT(DATE, GETDATE()))
ELSE DATEADD(DAY, (-1), CONVERT(DATE, GETDATE()))
END)) >= (DATEDIFF(DAY, (0), PKDate) - (30))
AND DATEDIFF(
DAY
, (0)
, CONVERT(
DATE
, DATEADD(
DAY
, (-7)
, CASE
WHEN DATENAME(WEEKDAY, CONVERT(DATE, GETDATE())) = 'Monday' THEN
DATEADD(DAY, (-7), CONVERT(DATE, GETDATE()))
WHEN DATENAME(WEEKDAY, CONVERT(DATE, GETDATE())) = 'Tuesday' THEN
DATEADD(DAY, (-8), CONVERT(DATE, GETDATE()))
ELSE DATEADD(DAY, (-1), CONVERT(DATE, GETDATE()))
END))) <= (DATEDIFF(DAY, (0), PKDate) + (30)))
AND PKDate < CONVERT(DATE, DATEADD(DAY, (-7), CONVERT(DATE, GETDATE()))) THEN
(1)
ELSE (0)
END))
, fiscal_month TINYINT NOT NULL
CONSTRAINT DF__calendar__fiscal__619622B1
DEFAULT ((0))) ON [PRIMARY];
GO
ALTER TABLE dbo.tblCalendar
ADD CONSTRAINT PK__calendar__9A073B3650F7C96A
PRIMARY KEY CLUSTERED (PKDate) ON [PRIMARY];
GO
CREATE NONCLUSTERED INDEX IX_calendar_WK_IN_Year
ON dbo.tblCalendar (week_in_year)
ON [PRIMARY];
GO
CREATE NONCLUSTERED INDEX IX_calendar_Day_in_WK
ON dbo.tblCalendar (day_in_week)
ON [PRIMARY];
GO
CREATE NONCLUSTERED INDEX IX_calendar_Fiscal_Year
ON dbo.tblCalendar (fiscal_year)
ON [PRIMARY];
GO
September 11, 2020 at 2:20 am
Such insanely bloated calendar tables have actually become much closer to the norm.
It's terrible. Create a nonwork days table with only the dates in it (you could also add a tinyint encoded value that "tells" why it is a nonworkday (1=Saturday; 2=Sunday; 3=New Year's holiday; etc.). Use this to count / work with nonwork dates.
Create a workdays table with only the work dates in it. Use this to calc future work dates, etc..
Finally, if you really need one, create a table that has all the required fiscal date and other special date needs.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 11, 2020 at 4:58 am
This was removed by the editor as SPAM
September 11, 2020 at 5:04 am
Such insanely bloated calendar tables have actually become much closer to the norm.
It's terrible.
Not to mention - it's useless.
Local government and councils have a habit establishing they own holidays, which usually are set on Sundays following by a non-working Monday. So, if a company operates in more than 1 town even within the same state, it quite likely to have different holidays for different locations.
_____________
Code for TallyGenerator
Viewing 15 posts - 46 through 60 (of 62 total)
You must be logged in to reply to this topic. Login to reply