Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Date and Time dimension creation and population T-SQL Expand / Collapse
Author
Message
Posted Tuesday, March 31, 2009 7:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 24, 2014 7:04 AM
Points: 92, Visits: 114
This is a wonderful bit of code. Pity I didn't find it about 4 hrs ago :) . Just 1 thing. If I want the WeekOfMonth to start on a Monday, Where and how do I need to alter the code. I can see it is somewhere in lines 142 , 143 "
, DATEPART(ww,@Date) + 0 - -----changed to set Monday as Day 1
DATEPART(ww,CAST(DATEPART(mm,@Date) AS VARCHAR) + '/1/' + CAST(DATEPART(yy,@Date) AS VARCHAR)) [WeekOfMonth]"

and as you can see I have tried with amending the + but no luck.

What I am trying to achieve is having the week of Month set to 1 for the first full week of the Mmonth starting from the fisrt Monday.

Once again, excellent code and please advise.

Nick
Post #687061
Posted Tuesday, March 31, 2009 8:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 24, 2014 7:04 AM
Points: 92, Visits: 114
Excellent code. Wish I had found this several hours back. Can you advise how to amend this so week of month starts on Moday as opposed to Sunday.
Many thanks

Nick
Post #687088
Posted Thursday, April 9, 2009 7:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
Dunno really what to make of this script. I'll have to take time and go through it some more but I like the idea of setting up tables with holidays but unfortunately every country's holidays is different so a lot of editing will be required.

Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #693971
Posted Wednesday, April 22, 2009 10:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:30 AM
Points: 8, Visits: 64
These are great!! Thanks for sharing the scripts.
Post #702520
Posted Thursday, April 23, 2009 8:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:57 AM
Points: 30, Visits: 111
The code for figuring out the week of the month is built in SQL functionality... I don't know that there is a way, non-manual, to calculate week of month if the week starts on Monday instead of Sunday...
I'm sure you can do it by putting in a loop and checking day one... and counting the days from there... etc... but that would take a lot of code and a lot of time to loop through every day for 150 years...

Yeah... it was a bit of work getting it to this point and I wish someone had done it like this before me to save me all that time... but I figured I'd save everyone else a bit of time...

Your Welcome... Your Welcome... Your Welcome...

PS>... For the holiday's comment... yeah... Those are mostly manual as you can see in the code... pick and choose your own... and if anyone has a way of programatically calculating when the Jewish calender holidays are...etc... I'd love to see it and add it to this code for everyone.

As for easter... I found the code posted by
AUTHOR: Robert Davis
SOURCE: http://www.databasejournal.com/scripts/article.php/3469911/Calculate-Easter-for-Any-Year.htm

Add this code to the script I posted after you add the function from the link above.
--Easter ------------------------------------------------------------------------------------------------------\
DECLARE @Years TABLE([ID] INT IDENTITY(1,1), [YEAR][INT])

INSERT INTO @YEARS([YEAR])
SELECT DISTINCT YEAR
FROM [dim_DATE]

DECLARE @POS BIGINT, @CNTR BIGINT, @YEAR VARCHAR(4)

SELECT @POS = 1, @CNTR = MAX([ID])
FROM @YEARS

WHILE @POS <= @CNTR
BEGIN
SELECT @YEAR = [YEAR]
FROM @YEARS
WHERE ID = @POS

UPDATE dim_DATE
SET HolidayText = ISNULL(HolidayText,'') + 'Easter'
WHERE DATE = CAST(dbo.Year2Easter(@YEAR) AS DATETIME)
SELECT @POS = @POS + 1
END
GO
------------------------------------------------------------------------
Post #703245
Posted Wednesday, November 17, 2010 5:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 7:55 AM
Points: 14, Visits: 82
Wonderful code... could someone assist with a function that uses dim_Date table(s) and works something like http://www.sqlservercentral.com/Forums/Topic207135-23-1.aspx but also accommodates for holidays???

Thinking something like this:
weekday_dateadd(dateTime,<# weekdays>,<0/1>Skip holidays,<0/1>DayOnly -drop time)

Select weekday_dateadd(getdate(),10,1,1)

Thank you in advance!!!

Post #1022082
Posted Wednesday, November 17, 2010 1:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 7:55 AM
Points: 14, Visits: 82
Anyone... would be a great help... thank you.
Post #1022465
Posted Wednesday, November 17, 2010 3:18 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
Here is how to drop the time from a date/time. I use GetDate() in the example but you can use a column just as well.

SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, GetDate() ))



ATB

Charles Kincaid

Post #1022507
Posted Wednesday, November 17, 2010 3:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 7:55 AM
Points: 14, Visits: 82
thank you but unfortunately I don't think that will accommodate my need. I need to be able to add days (weekdays only excluding holidays) to a date, my basic need would always be getdate() but more flexible as a function parameter so I can do "BASICALY" getdate()+10 (no weekend day or holiday).

This dim_Date table has everything to do it... but not sure the most efficient way to do it.

Me doing it...I would just give a start date and get X rows in the future that are weekday and not holiday and take max(date) returned... It would work... but most efficient??


Post #1022514
Posted Thursday, November 18, 2010 5:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 7:55 AM
Points: 14, Visits: 82
Guess will just post my version maybe will be of some help to someone else.

CREATE FUNCTION [dbo].[weekday_dateadd] ( @startDate SMALLDATETIME, @Days INT, @SkipHoliday BIT = 1, @DayOnly BIT = 1 )
RETURNS SMALLDATETIME
AS
BEGIN
/*
Title: weekday_dateadd(2)
Author: Mark Wolff
Date: 11/18/2010
Purpose: Adds a specified number of weekdays to a given date allowing to SkipHolidays also

Examples:
SELECT dbo.weekday_dateadd(GETDATE(),90,DEFAULT,DEFAULT)
SELECT dbo.weekday_dateadd(GETDATE(),10,DEFAULT,0)

NOTE: Will not work past a few years
*/
DECLARE @endDate SMALLDATETIME

SELECT @endDate = date
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY date) Sequence
,CASE WHEN @DayOnly = 1 THEN Date ELSE Date + CONVERT(VARCHAR,@startDate,108) END Date
FROM dim_Date(NOLOCK)
WHERE DAYOFWEEK NOT IN ('Saturday','Sunday')
AND (CASE WHEN @SkipHoliday = 1 AND HolidayText IS NOT NULL THEN 0 ELSE 1 END) = 1
AND Date BETWEEN @startDate-1 AND DATEADD(d,@Days+@Days/7*3+20,@startDate)
)d
WHERE Sequence = @Days

RETURN @endDate
END

Did some basic testing seems to work
Post #1023236
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse