September 10, 2013 at 5:14 pm
Great script! :w00t:
September 10, 2013 at 5:44 pm
Thank you!
September 10, 2013 at 9:07 pm
Sean
I haven't had a chance to review the entire script in detail nor have I gone back to read all the comments so if the following issue has already been discussed, please point it out.
Did you include an option to display the cardinality of the days, i.e., '2nd of March', '3rd of March', '4th of March', etc.
Thanks
September 11, 2013 at 9:08 am
No, not yet. Very few changes in a long time actually. SSC just decided to re-feature it despite nothing really new in the script.
September 11, 2013 at 8:21 pm
OK...here ya go...includes Brian's suggestion for Holidays. I turned it into a stored procedure:
CREATE PROCEDURE dbo.UTIL_CreateDateCalendar
@Date_Start DATETIME
,@Date_End DATETIME
AS
BEGIN
/*
EXEC dbo.UTIL_CreateDateCalendar '20100101','20201231'
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET TEXTSIZE 2147483647
----------------------------------------------------------------------------------------------------------------------
-- Permanent Table: Create Date Xref Table
----------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID(N'dbo.UTIL_DateCalendar', N'U') IS NOT NULL
DROP TABLE dbo.UTIL_DateCalendar
CREATE TABLE dbo.UTIL_DateCalendar
(
calendar_date DATETIME
NOT NULL
CONSTRAINT PK_UTIL_DateCalendar_calendar_date
PRIMARY KEY CLUSTERED
,calendar_year INT NULL
,calendar_month INT NULL
,calendar_day INT NULL
,calendar_quarter INT NULL
,first_day_in_week DATETIME NULL
,last_day_in_week DATETIME NULL
,is_week_in_same_month INT NULL
,first_day_in_month DATETIME NULL
,last_day_in_month DATETIME NULL
,is_last_day_in_month INT NULL
,first_day_in_quarter DATETIME NULL
,last_day_in_quarter DATETIME NULL
,is_last_day_in_quarter INT NULL
,day_of_week INT NULL
,week_of_month INT NULL
,week_of_quarter INT NULL
,week_of_year INT NULL
,days_in_month INT NULL
,month_days_remaining INT NULL
,weekdays_in_month INT NULL
,month_weekdays_remaining INT NULL
,month_weekdays_completed INT NULL
,days_in_quarter INT NULL
,quarter_days_remaining INT NULL
,quarter_days_completed INT NULL
,weekdays_in_quarter INT NULL
,quarter_weekdays_remaining INT NULL
,quarter_weekdays_completed INT NULL
,day_of_year INT NULL
,year_days_remaining INT NULL
,is_weekday INT NULL
,is_leap_year INT NULL
,day_name VARCHAR(10) NULL
,month_day_name_instance INT NULL
,quarter_day_name_instance INT NULL
,year_day_name_instance INT NULL
,month_name VARCHAR(10) NULL
,year_week CHAR(6) NULL
,year_month CHAR(6) NULL
,year_quarter CHAR(6) NULL
);
----------------------------------------------------------------------------------------------------------------------
-- Table Insert: Populate Base Date Values Into Permanent Table Using Common Table Expression (CTE)
----------------------------------------------------------------------------------------------------------------------
WITH cte_date_base_table
AS (
SELECT
@Date_Start AS calendar_date
UNION ALL
SELECT
DATEADD(DAY, 1, CTE.calendar_date)
FROM
cte_date_base_table CTE
WHERE
DATEADD(DAY, 1, CTE.calendar_date)<=@Date_End
)
INSERT INTO dbo.UTIL_DateCalendar
(
calendar_date
)
SELECT
CTE.calendar_date
FROM
cte_date_base_table CTE
OPTION
(MAXRECURSION 0)
----------------------------------------------------------------------------------------------------------------------
-- Table Update I: Populate Additional Date Xref Table Fields (Pass I)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.UTIL_DateCalendar
SET
calendar_year=DATEPART(YEAR, calendar_date)
,calendar_month=DATEPART(MONTH, calendar_date)
,calendar_day=DATEPART(DAY, calendar_date)
,calendar_quarter=DATEPART(QUARTER, calendar_date)
,first_day_in_week=
DATEADD(DAY, -DATEPART(WEEKDAY, calendar_date)+1,calendar_date)
,first_day_in_month=CONVERT (VARCHAR(6), calendar_date, 112) +'01'
,day_of_week=DATEPART(WEEKDAY, calendar_date)
,week_of_year=DATEPART(WEEK, calendar_date)
,day_of_year=DATEPART(DAYOFYEAR, calendar_date)
,is_weekday=
ISNULL((
CASE
WHEN ((@@DATEFIRST-1) +(DATEPART(WEEKDAY, calendar_date) -1))%7 NOT IN (5, 6)
THEN 1
END), 0)
,day_name=DATENAME(WEEKDAY, calendar_date)
,month_name=DATENAME(MONTH, calendar_date)
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN calendar_year INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN calendar_month INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN calendar_day INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN calendar_quarter INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN first_day_in_week DATETIME NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN first_day_in_month DATETIME NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN day_of_week INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN week_of_year INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN day_of_year INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN is_weekday INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN day_name VARCHAR (10) NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN month_name VARCHAR (10) NOT NULL
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_calendar_year ON dbo.UTIL_DateCalendar (calendar_year)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_calendar_month ON dbo.UTIL_DateCalendar (calendar_month)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_calendar_quarter ON dbo.UTIL_DateCalendar (calendar_quarter)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_first_day_in_week ON dbo.UTIL_DateCalendar (first_day_in_week)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_day_of_week ON dbo.UTIL_DateCalendar (day_of_week)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_is_weekday ON dbo.UTIL_DateCalendar (is_weekday)
----------------------------------------------------------------------------------------------------------------------
-- Table Update II: Populate Additional Date Xref Table Fields (Pass II)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.UTIL_DateCalendar
SET
last_day_in_week=first_day_in_week+6
,last_day_in_month=DATEADD(MONTH, 1, first_day_in_month)-1
,first_day_in_quarter=A.first_day_in_quarter
,last_day_in_quarter=A.last_day_in_quarter
,week_of_month=DATEDIFF(WEEK, first_day_in_month, calendar_date)
+1
,week_of_quarter=(week_of_year-A.min_week_of_year_in_quarter)+1
,is_leap_year=
ISNULL((
CASE
WHEN calendar_year%400=0 THEN 1
WHEN calendar_year%100=0 THEN 0
WHEN calendar_year%4=0 THEN 1
END), 0)
,year_week=
CONVERT (VARCHAR(4), calendar_year)
+RIGHT('0'+CONVERT (VARCHAR(2), week_of_year),2)
,year_month=
CONVERT (VARCHAR(4), calendar_year)
+RIGHT('0'+CONVERT (VARCHAR(2), calendar_month),2)
,year_quarter=
CONVERT (VARCHAR(4), calendar_year)+'Q'
+CONVERT (VARCHAR(1), calendar_quarter)
FROM
(
SELECT
X.calendar_year AS subquery_calendar_year
,X.calendar_quarter AS subquery_calendar_quarter
,MIN(X.calendar_date) AS first_day_in_quarter
,MAX(X.calendar_date) AS last_day_in_quarter
,MIN(X.week_of_year) AS min_week_of_year_in_quarter
FROM
dbo.UTIL_DateCalendar X
GROUP BY
X.calendar_year
,X.calendar_quarter
) A
WHERE
A.subquery_calendar_year=calendar_year
AND A.subquery_calendar_quarter=calendar_quarter
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN last_day_in_week DATETIME NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN last_day_in_month DATETIME NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN first_day_in_quarter DATETIME NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN last_day_in_quarter DATETIME NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN week_of_month INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN week_of_quarter INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN is_leap_year INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN year_week VARCHAR (6) NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN year_month VARCHAR (6) NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN year_quarter VARCHAR (6) NOT NULL
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_last_day_in_week ON dbo.UTIL_DateCalendar (last_day_in_week)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_year_month ON dbo.UTIL_DateCalendar (year_month)
CREATE NONCLUSTERED INDEX IX_UTIL_DateCalendar_year_quarter ON dbo.UTIL_DateCalendar (year_quarter)
----------------------------------------------------------------------------------------------------------------------
-- Table Update III: Populate Additional Date Xref Table Fields (Pass III)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.UTIL_DateCalendar
SET
is_last_day_in_month=
(CASE
WHEN last_day_in_month=calendar_date THEN 1
ELSE 0
END)
,is_last_day_in_quarter=
(CASE
WHEN last_day_in_quarter=calendar_date THEN 1
ELSE 0
END)
,days_in_month=DATEPART(DAY, last_day_in_month)
,weekdays_in_month=A.weekdays_in_month
,days_in_quarter=DATEDIFF(DAY, first_day_in_quarter,last_day_in_quarter)+1
,quarter_days_remaining=DATEDIFF(DAY, calendar_date,last_day_in_quarter)
,weekdays_in_quarter=B.weekdays_in_quarter
,year_days_remaining=(365+is_leap_year)-day_of_year
FROM
(
SELECT
X.year_month AS subquery_year_month
,SUM(X.is_weekday) AS weekdays_in_month
FROM
dbo.UTIL_DateCalendar X
GROUP BY
X.year_month
) A
,(
SELECT
X.year_quarter AS subquery_year_quarter
,SUM(X.is_weekday) AS weekdays_in_quarter
FROM
dbo.UTIL_DateCalendar X
GROUP BY
X.year_quarter
) B
WHERE
A.subquery_year_month=year_month
AND B.subquery_year_quarter=year_quarter
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN is_last_day_in_month INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN is_last_day_in_quarter INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN days_in_month INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN weekdays_in_month INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN days_in_quarter INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN quarter_days_remaining INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN weekdays_in_quarter INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN year_days_remaining INT NOT NULL
----------------------------------------------------------------------------------------------------------------------
-- Table Update IV: Populate Additional Date Xref Table Fields (Pass IV)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.UTIL_DateCalendar
SET
month_weekdays_remaining=
weekdays_in_month - A.month_weekdays_remaining_subtraction
,quarter_weekdays_remaining=
weekdays_in_quarter - A.quarter_weekdays_remaining_subtraction
FROM
(
SELECT
X.calendar_date AS subquery_calendar_date
,ROW_NUMBER() OVER (PARTITION BY X.year_month ORDER BY X.calendar_date) AS month_weekdays_remaining_subtraction
,ROW_NUMBER() OVER (PARTITION BY X.year_quarter ORDER BY X.calendar_date) AS quarter_weekdays_remaining_subtraction
FROM
dbo.UTIL_DateCalendar X
WHERE
X.is_weekday=1
) A
WHERE
A.subquery_calendar_date=calendar_date
----------------------------------------------------------------------------------------------------------------------
-- Table Update V: Populate Additional Date Xref Table Fields (Pass V)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.UTIL_DateCalendar
SET
month_weekdays_remaining=A.month_weekdays_remaining
,quarter_weekdays_remaining=A.quarter_weekdays_remaining
FROM
(
SELECT
X.calendar_date AS subquery_calendar_date
,COALESCE(Y.month_weekdays_remaining,
Z.month_weekdays_remaining, X.weekdays_in_month) AS month_weekdays_remaining
,COALESCE(Y.quarter_weekdays_remaining,
Z.quarter_weekdays_remaining,
X.weekdays_in_quarter) AS quarter_weekdays_remaining
FROM
dbo.UTIL_DateCalendar X
LEFT JOIN dbo.UTIL_DateCalendar Y
ON DATEADD(DAY, 1, Y.calendar_date)=X.calendar_date
AND Y.year_month=X.year_month
LEFT JOIN dbo.UTIL_DateCalendar Z
ON DATEADD(DAY, 2, Z.calendar_date)=X.calendar_date
AND Z.year_month=X.year_month
WHERE
X.month_weekdays_remaining IS NULL
) A
WHERE
A.subquery_calendar_date=calendar_date
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN month_weekdays_remaining INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN quarter_weekdays_remaining INT NOT NULL
----------------------------------------------------------------------------------------------------------------------
-- Table Update VI: Populate Additional Date Xref Table Fields (Pass VI)
----------------------------------------------------------------------------------------------------------------------
UPDATE
dbo.UTIL_DateCalendar
SET
is_week_in_same_month=A.is_week_in_same_month
,month_days_remaining=days_in_month-calendar_day
,month_weekdays_completed=
weekdays_in_month - month_weekdays_remaining
,quarter_days_completed=days_in_quarter-quarter_days_remaining
,quarter_weekdays_completed=
weekdays_in_quarter - quarter_weekdays_remaining
,month_day_name_instance=A.month_day_name_instance
,quarter_day_name_instance=A.quarter_day_name_instance
,year_day_name_instance=A.year_day_name_instance
FROM
(
SELECT
X.calendar_date AS subquery_calendar_date
,ISNULL((
CASE
WHEN DATEDIFF(MONTH, X.first_day_in_week,X.last_day_in_week)=0 THEN 1
END), 0)
AS is_week_in_same_month
,ROW_NUMBER() OVER (PARTITION BY X.year_month, X.day_name ORDER BY X.calendar_date) AS month_day_name_instance
,ROW_NUMBER() OVER (PARTITION BY X.year_quarter, X.day_name ORDER BY X.calendar_date) AS quarter_day_name_instance
,ROW_NUMBER() OVER (
PARTITION BY X.calendar_year,X.day_name
ORDER BY X.calendar_date)
AS year_day_name_instance
FROM
dbo.UTIL_DateCalendar X
) A
WHERE
A.subquery_calendar_date=calendar_date
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN is_week_in_same_month INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN month_days_remaining INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN month_weekdays_completed INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN quarter_days_completed INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN quarter_weekdays_completed INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN month_day_name_instance INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN quarter_day_name_instance INT NOT NULL
ALTER TABLE dbo.UTIL_DateCalendar ALTER COLUMN year_day_name_instance INT NOT NULL
----------------------------------------------------------------------------------------------------------------------
-- Main Query: Final Display / Output
----------------------------------------------------------------------------------------------------------------------
SELECT
URD.*
,[holiday] = CASE WHEN MONTH(URD.calendar_date)=1
AND DAY(URD.calendar_date)=1
THEN 'New Years Day'
WHEN MONTH(URD.calendar_date)=5
AND DATEPART(weekday,
DATEADD(day, @@DATEFIRST,
URD.calendar_date))=2
AND DAY(URD.calendar_date) BETWEEN 25 AND 31
THEN 'Memorial Day'
WHEN MONTH(URD.calendar_date)=7
AND DAY(URD.calendar_date)=4
THEN 'Independence Day'
WHEN MONTH(URD.calendar_date)=9
AND DATEPART(weekday,
DATEADD(day, @@DATEFIRST,
URD.calendar_date))=2
AND DAY(URD.calendar_date) BETWEEN 1 AND 7
THEN 'Labor Day'
WHEN MONTH(URD.calendar_date)=11
AND DATEPART(weekday,
DATEADD(day, @@DATEFIRST,
URD.calendar_date))=5
AND DAY(URD.calendar_date) BETWEEN 22 AND 28
THEN 'Thanksgiving'
WHEN MONTH(URD.calendar_date)=12
AND DAY(URD.calendar_date)=25
THEN 'Christmas Eve'
WHEN MONTH(URD.calendar_date)=12
AND DAY(URD.calendar_date)=26
THEN 'Christmas Day'
ELSE '' -- or NULL depending on your preference
END
,[ordinal_num] = CAST(DAY(URD.calendar_day) AS NVARCHAR(2))
+CASE WHEN CAST(DAY(URD.calendar_day) AS INT) IN (1, 21, 31)
THEN N'st'
WHEN CAST(DAY(URD.calendar_day) AS INT) IN (2, 22)
THEN N'nd'
WHEN CAST(DAY(URD.calendar_day) AS INT) IN (3, 23)
THEN N'rd'
ELSE N'th'
END
,[ordinal_txt] = CASE WHEN CAST(DAY(URD.calendar_day) AS INT)=1
THEN N'First'
WHEN CAST(DAY(URD.calendar_day) AS INT)=2
THEN N'Second'
WHEN CAST(DAY(URD.calendar_day) AS INT)=3
THEN N'Third'
WHEN CAST(DAY(URD.calendar_day) AS INT)=4
THEN N'Fourth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=5
THEN N'Fifth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=6
THEN N'Sixth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=7
THEN N'Seventh'
WHEN CAST(DAY(URD.calendar_day) AS INT)=8
THEN N'Eighth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=9
THEN N'Ninth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=10
THEN N'Tenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=11
THEN N'Elevent'
WHEN CAST(DAY(URD.calendar_day) AS INT)=12
THEN N'Twelfth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=13
THEN N'Thirteenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=14
THEN N'Fourteenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=15
THEN N'Fifteenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=16
THEN N'Sixteenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=17
THEN N'Seventeenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=18
THEN N'Eighteenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=19
THEN N'Nineteenth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=20
THEN N'Twentieth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=21
THEN N'Twenty-first'
WHEN CAST(DAY(URD.calendar_day) AS INT)=22
THEN N'Twenty-second'
WHEN CAST(DAY(URD.calendar_day) AS INT)=23
THEN N'Twenty-third'
WHEN CAST(DAY(URD.calendar_day) AS INT)=24
THEN N'Twenty-fourth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=25
THEN N'Twenty-fifth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=26
THEN N'Twenty-sixth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=27
THEN N'Twenty-seventh'
WHEN CAST(DAY(URD.calendar_day) AS INT)=28
THEN N'Twenty-eighth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=29
THEN N'Twenty-ninth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=30
THEN N'Thirtierth'
WHEN CAST(DAY(URD.calendar_day) AS INT)=31
THEN N'Thirty-first'
END
FROM
dbo.UTIL_DateCalendar URD
ORDER BY
URD.calendar_date
END
September 12, 2013 at 5:57 am
Steven, this is great! Thank you such much for taking the time to do this for everyone. 🙂
June 27, 2014 at 5:16 pm
Ran the script and it is very nice. One problem is that I am getting stange results for the quarter_weekdays_remaining and quarter_weekdays_completed fields. On 2001-09-01 and 2009-09-02 my table has 65 and 0 respectively. I checked further and found more rows that seem to be wrong when the first two days of the month are Saturday or Sunday (2002-09-01).
June 28, 2014 at 6:55 am
Scott.Skinner (6/27/2014)
Ran the script and it is very nice. One problem is that I am getting stange results for the quarter_weekdays_remaining and quarter_weekdays_completed fields. On 2001-09-01 and 2009-09-02 my table has 65 and 0 respectively. I checked further and found more rows that seem to be wrong when the first two days of the month are Saturday or Sunday (2002-09-01).
Great catch! I've isolated the issue, just need to figure out the best approach to correct it. Keep you posted and thanks again for finding this!
June 30, 2014 at 7:47 am
Yes, thanks. I ran into this puzzler and posited the question initially under the heading of 'how hard can it be.' Looking forward to your reply.
June 30, 2014 at 9:55 am
This seems to work OK. Many ways to solve it.
UPDATE
date_calendar
SET
quarter_weekdays_remaining =
(
SELECTISNULL( SUM( r.is_weekday ), 0 )
FROMdate_calendar r
WHEREr.calendar_date > s.calendar_date
ANDr.calendar_year = s.calendar_year
ANDr.calendar_quarter = s.calendar_quarter
),
quarter_weekdays_completed =
(
SELECTISNULL( SUM( c.is_weekday ), 0 )
FROMdate_calendar c
WHEREc.calendar_date <= s.calendar_date
ANDc.calendar_year = s.calendar_year
ANDc.calendar_quarter = s.calendar_quarter
)
FROM
date_calendar s
July 2, 2014 at 9:17 am
Scott.Skinner (6/30/2014)
This seems to work OK. Many ways to solve it.UPDATE
date_calendar
SET
quarter_weekdays_remaining =
(
SELECTISNULL( SUM( r.is_weekday ), 0 )
FROMdate_calendar r
WHEREr.calendar_date > s.calendar_date
ANDr.calendar_year = s.calendar_year
ANDr.calendar_quarter = s.calendar_quarter
),
quarter_weekdays_completed =
(
SELECTISNULL( SUM( c.is_weekday ), 0 )
FROMdate_calendar c
WHEREc.calendar_date <= s.calendar_date
ANDc.calendar_year = s.calendar_year
ANDc.calendar_quarter = s.calendar_quarter
)
FROM
date_calendar s
Nice. I opted to go with changing the following:
UPDATE
dbo.date_calendar
SET
month_weekdays_remaining = A.month_weekdays_remaining
,quarter_weekdays_remaining = A.quarter_weekdays_remaining
FROM
(
SELECT
X.calendar_date AS subquery_calendar_date
,COALESCE (Y.month_weekdays_remaining, Z.month_weekdays_remaining, X.weekdays_in_month) AS month_weekdays_remaining
,COALESCE (Y.quarter_weekdays_remaining, Z.quarter_weekdays_remaining, X.weekdays_in_quarter) AS quarter_weekdays_remaining
FROM
dbo.date_calendar X
LEFT JOIN dbo.date_calendar Y ON DATEADD (DAY, 1, Y.calendar_date) = X.calendar_date
AND Y.year_month = X.year_month
LEFT JOIN dbo.date_calendar Z ON DATEADD (DAY, 2, Z.calendar_date) = X.calendar_date
AND Z.year_month = X.year_month
WHERE
X.month_weekdays_remaining IS NULL
) A
WHERE
A.subquery_calendar_date = calendar_date
To:
UPDATE
X
SET
X.month_weekdays_remaining = (CASE
WHEN Y.calendar_month = X.calendar_month AND Y.month_weekdays_remaining IS NOT NULL THEN Y.month_weekdays_remaining
WHEN Z.calendar_month = X.calendar_month AND Z.month_weekdays_remaining IS NOT NULL THEN Z.month_weekdays_remaining
ELSE X.weekdays_in_month
END)
,X.quarter_weekdays_remaining = (CASE
WHEN Y.calendar_quarter = X.calendar_quarter AND Y.quarter_weekdays_remaining IS NOT NULL THEN Y.quarter_weekdays_remaining
WHEN Z.calendar_quarter = X.calendar_quarter AND Z.quarter_weekdays_remaining IS NOT NULL THEN Z.quarter_weekdays_remaining
ELSE X.weekdays_in_quarter
END)
FROM
dbo.date_calendar X
LEFT JOIN dbo.date_calendar Y ON DATEADD (DAY, 1, Y.calendar_date) = X.calendar_date
LEFT JOIN dbo.date_calendar Z ON DATEADD (DAY, 2, Z.calendar_date) = X.calendar_date
WHERE
X.month_weekdays_remaining IS NULL
October 31, 2014 at 10:00 am
This script is very helpful and it's close to what I need but not quite. I need the week# and month# to be built off the startdate parameter.
So, my client's cycle start date is 9/16/14.
This means Wk# 1 runs 9/14/14 - 9/20/14. With your table, I get the Wk# based on Calendar Year. (38 in this case)
Any suggestions for how I can make this work for me?
October 31, 2014 at 10:17 am
lduvall (10/31/2014)
This script is very helpful and it's close to what I need but not quite. I need the week# and month# to be built off the startdate parameter.So, my client's cycle start date is 9/16/14.
This means Wk# 1 runs 9/14/14 - 9/20/14. With your table, I get the Wk# based on Calendar Year. (38 in this case)
Any suggestions for how I can make this work for me?
I assume you mean the calendar_month and week_of_year fields? Although it might make sense to add additional fields with names like cycle_month and week_of_cycle or something...
How would the cycle_month work? Like, for 9/14/14, I figure the month is 1. But for 9/13/14, would that be month 1 or month 12?
Anyway, here's a start:
DECLARE @cycle_start_month int = 9
DECLARE @cycle_start_day int = 16
SELECT calendar_date, calendar_month, week_of_year
, cycle_month = (
CASE WHEN u.calendar_month - @cycle_start_month > 0
THEN u.calendar_month - @cycle_start_month
ELSE u.calendar_month - @cycle_start_month + 12
END)
, week_of_cycle = (
CASE WHEN DATEDIFF(week, CAST(ltrim(str(calendar_year))+'-'+ltrim(str(@cycle_start_month))+'-'+ltrim(str(@cycle_start_day)) AS datetime), calendar_date)+1 > 0
THEN DATEDIFF(week, CAST(ltrim(str(calendar_year))+'-'+ltrim(str(@cycle_start_month))+'-'+ltrim(str(@cycle_start_day)) AS datetime), calendar_date)+1
ELSE DATEDIFF(week, CAST(ltrim(str(calendar_year-1))+'-'+ltrim(str(@cycle_start_month))+'-'+ltrim(str(@cycle_start_day)) AS datetime), calendar_date)+1
END)
FROM dbo.UTIL_DateCalendar u
October 31, 2014 at 10:43 am
Brian J. Parker (10/31/2014)
lduvall (10/31/2014)
This script is very helpful and it's close to what I need but not quite. I need the week# and month# to be built off the startdate parameter.So, my client's cycle start date is 9/16/14.
This means Wk# 1 runs 9/14/14 - 9/20/14. With your table, I get the Wk# based on Calendar Year. (38 in this case)
Any suggestions for how I can make this work for me?
I assume you mean the calendar_month and week_of_year fields? Although it might make sense to add additional fields with names like cycle_month and week_of_cycle or something...
How would the cycle_month work? Like, for 9/14/14, I figure the month is 1. But for 9/13/14, would that be month 1 or month 12?
Anyway, here's a start:
DECLARE @cycle_start_month int = 9DECLARE @cycle_start_day int = 16
SELECT calendar_date, calendar_month, week_of_year
, cycle_month = (
CASE WHEN u.calendar_month - @cycle_start_month > 0
THEN u.calendar_month - @cycle_start_month
ELSE u.calendar_month - @cycle_start_month + 12
END)
, week_of_cycle = (
CASE WHEN DATEDIFF(week, CAST(ltrim(str(calendar_year))+'-'+ltrim(str(@cycle_start_month))+'-'+ltrim(str(@cycle_start_day)) AS datetime), calendar_date)+1 > 0
THEN DATEDIFF(week, CAST(ltrim(str(calendar_year))+'-'+ltrim(str(@cycle_start_month))+'-'+ltrim(str(@cycle_start_day)) AS datetime), calendar_date)+1
ELSE DATEDIFF(week, CAST(ltrim(str(calendar_year-1))+'-'+ltrim(str(@cycle_start_month))+'-'+ltrim(str(@cycle_start_day)) AS datetime), calendar_date)+1
END)
FROM dbo.UTIL_DateCalendar u
Thanks Brian, that's a great starting point. 🙂
January 1, 2016 at 1:05 pm
Wow, pretty comprehensive.
Viewing 15 posts - 46 through 60 (of 71 total)
You must be logged in to reply to this topic. Login to reply