71 lines
4.5 KiB
Transact-SQL
71 lines
4.5 KiB
Transact-SQL
CREATE PROCEDURE [dbo].[CreateCalendar]
|
|
/************************************************************************************************************
|
|
Purpose:
|
|
Given a start date and an end date, create a calendar of dates that easily traverses months and years. Is
|
|
capable of generating up to 179 years worth of calendar by month in any given run.
|
|
Usage Notes:
|
|
1. End date parameter is optional... if not provided, the start date will be used to return that one month.
|
|
2. Of the two input paramters, the lowest (earliest) date will be used as the start date regardless of the
|
|
order provided.
|
|
3. Returns a single result set containing all dates.
|
|
4. Does NOT use a Cursor, While Loop, Temp Table, or Table variable so that even really picky DBA's won't
|
|
reject the code.
|
|
5. Because only set-based code was used, returns 10 years worth of calendar in about 230 milliseconds.
|
|
A full year takes a scant 76 milliseconds and a single month takes 63 milliseconds. A hundred-year
|
|
calendar takes only 6.3 seconds.
|
|
6. The start date does NOT need to be the first of the month and the end date does NOT need to be the last
|
|
day of the month. The internal calculations will "auto-magically" include the whole month for both the
|
|
start and end dates as well as all months in between.
|
|
Revision History:
|
|
Rev 00 - 08/13/2007 - Jeff Moden - Initial creation and unit test.
|
|
************************************************************************************************************/
|
|
--===== Declare the input parameters
|
|
@pStartDate DATETIME, --Any date (can include time) in the desired start month
|
|
@pEndDate DATETIME = @pStartDate --Any date (can include time) in the desired end month
|
|
AS
|
|
--===== Create the correct environment
|
|
SET NOCOUNT ON --Suppress the auto-display of rowcounts to prevent false echos on GUI's
|
|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --Same as putting WITH (NOLOCK) on tables in query
|
|
--===== Create the base date that all the calculations rely on (is a Monday)
|
|
DECLARE @BaseDate DATETIME
|
|
SET @BaseDate = '1753-01-01'
|
|
--===== Make sure the inputs are in the correct order (start date must always be the earliest date)
|
|
IF @pEndDate < @pStartDate
|
|
BEGIN
|
|
DECLARE @Swap DATETIME
|
|
SELECT @Swap = @pStartDate,
|
|
@pStartDate = @pEndDate,
|
|
@pEndDate = @Swap
|
|
END
|
|
--===== Modify the start to start on the first of the same month and the end date to end on the last day
|
|
-- of the same month.
|
|
SET @pStartDate = DATEADD(mm,DATEDIFF(mm,@BaseDate,@pStartDate),@BaseDate)
|
|
SET @pEndDate = DATEADD(mm,DATEDIFF(mm,@BaseDate,@pEndDate)+1,@BaseDate)-1
|
|
|
|
--===== Create the calendar without loops, cursors, or temp tables
|
|
SELECT MAX(DATENAME(yy,d.TheDate)) AS [Year],
|
|
MAX(LEFT(DATENAME(mm,d.TheDate),3))AS [Month],
|
|
MAX(CASE WHEN DATENAME(dw,d.TheDate) = '월요일' THEN DATENAME(dd,d.TheDate) ELSE '' END) AS Mon,
|
|
MAX(CASE WHEN DATENAME(dw,d.TheDate) = '화요일' THEN DATENAME(dd,d.TheDate) ELSE '' END) AS Tue,
|
|
MAX(CASE WHEN DATENAME(dw,d.TheDate) = '수요일' THEN DATENAME(dd,d.TheDate) ELSE '' END) AS Wed,
|
|
MAX(CASE WHEN DATENAME(dw,d.TheDate) = '목요일' THEN DATENAME(dd,d.TheDate) ELSE '' END) AS Thu,
|
|
MAX(CASE WHEN DATENAME(dw,d.TheDate) = '금요일' THEN DATENAME(dd,d.TheDate) ELSE '' END) AS Fri,
|
|
MAX(CASE WHEN DATENAME(dw,d.TheDate) = '토요일' THEN DATENAME(dd,d.TheDate) ELSE '' END) AS Sat,
|
|
MAX(CASE WHEN DATENAME(dw,d.TheDate) = '일요일' THEN DATENAME(dd,d.TheDate) ELSE '' END) AS Sun
|
|
FROM (--==== Derived table "d" finds all dates for the given range of dates up to 65535 days apart
|
|
SELECT YEAR(@pStartDate+n.Number) AS TheYear,
|
|
MONTH(@pStartDate+n.Number) AS TheMonth,
|
|
DATEDIFF(dd,@BaseDate,@pStartDate+n.Number)/7 AS TheWeek,
|
|
@pStartDate+n.Number AS TheDate
|
|
FROM (--==== Derived table "n" creates numbers from 0 to 65535 (about 179 years worth of days)
|
|
SELECT t1.Number*256+t2.Number AS Number
|
|
FROM Master.dbo.spt_Values t1,
|
|
Master.dbo.spt_Values t2
|
|
WHERE t1.Type = 'P'
|
|
AND t2.Type = 'P'
|
|
)n
|
|
WHERE @pStartDate+n.Number <= @pEndDate
|
|
)d
|
|
GROUP BY d.TheYear,d.TheMonth,d.TheWeek
|
|
ORDER BY d.TheYear,d.TheMonth,d.TheWeek
|
|
|