create a resultset of calendar dates

I should use a stored procedure to fill a cal开发者_开发百科endar for 10 years in a SQL database!

Three columns should I fill in with the records: date,weekday or weekend, dayname( monday,...).

Can somebody help me?

You can do it dynamically like this:

WITH Dates AS (
        SELECT CONVERT(DATE, getdate()) as [Date]
        UNION ALL 
        SELECT DATEADD(DAY, 1, [Date])
        FROM Dates
        where Date < dateadd(yy, 10, getdate())

) 
SELECT [Date]
FROM Dates
OPTION (MAXRECURSION 4000)

try this version (floors the datetime and adds all requested columns):

;WITH Dates AS (
        SELECT DATEADD(day,DATEDIFF(day,0,GETDATE()),0) as DateOf,
            CASE WHEN datename(weekday,getdate()) IN ('Saturday','Sunday') THEN 'Weekend'
                 ELSE 'WeekDay' 
            END DayType,
            datename(weekday,getdate()) DayOfWeekName
        UNION ALL 
        SELECT DateOf+1,
            CASE WHEN datename(weekday,DateOf+1) IN ('Saturday','Sunday') THEN 'Weekend'
                 ELSE 'WeekDay' 
            END DayType,
            datename(weekday,DateOf+1) DayOfWeekName
        FROM Dates
        where DateOf < dateadd(yy, 10, getdate())

) 
SELECT DateOf,DayType,DayOfWeekName
FROM Dates
OPTION (MAXRECURSION 4000)

to insert into a table try this:

DECLARE @DateTable table (DateOf datetime, DayType char(7), DayOfWeekName varchar(10))

;WITH Dates AS (
        SELECT DATEADD(day,DATEDIFF(day,0,GETDATE()),0) as DateOf,
            CASE WHEN datename(weekday,getdate()) IN ('Saturday','Sunday') THEN 'Weekend'
                 ELSE 'WeekDay' 
            END DayType,
            datename(weekday,getdate()) DayOfWeekName
        UNION ALL 
        SELECT DateOf+1,
            CASE WHEN datename(weekday,DateOf+1) IN ('Saturday','Sunday') THEN 'Weekend'
                 ELSE 'WeekDay' 
            END DayType,
            datename(weekday,DateOf+1) DayOfWeekName
        FROM Dates
        where DateOf < dateadd(yy, 10, getdate())

)
INSERT INTO  @DateTable (DateOf,DayType,DayOfWeekName)
    SELECT DateOf,DayType,DayOfWeekName
    FROM Dates
    OPTION (MAXRECURSION 4000)

select top 10 * from @DateTable

OTUPUT:

DateOf                  DayType DayOfWeekName
----------------------- ------- -------------
2011-05-16 00:00:00.000 WeekDay Monday
2011-05-17 00:00:00.000 WeekDay Tuesday
2011-05-18 00:00:00.000 WeekDay Wednesday
2011-05-19 00:00:00.000 WeekDay Thursday
2011-05-20 00:00:00.000 WeekDay Friday
2011-05-21 00:00:00.000 Weekend Saturday
2011-05-22 00:00:00.000 Weekend Sunday
2011-05-23 00:00:00.000 WeekDay Monday
2011-05-24 00:00:00.000 WeekDay Tuesday
2011-05-25 00:00:00.000 WeekDay Wednesday

(10 row(s) affected)

I apporached this as a tally table problem. I am using spt_values from Master as my tally table. It only goes up to 2048 which is enough data for 5.5 years. You can create your own tally table with as many numbers as you need.

Declare @startDate Date = '1/1/2011'; 

SELECT DateAdd(d, number, @startDate) [Date], 
       CASE WHEN DATEPART(dw, DateAdd(d, number, @startDate)) IN (1,7) THEN 'Weekend' ELSE 'Weekday' END [WeekDayEnd], 
       DateName(weekday, DateAdd(d, number, @startDate)) DayOfWeek
FROM spt_values 
WHERE type = 'P';

This gets the following results:

Date        WeekDayEnd  DayOfWeek
2011-01-01  Weekend     Saturday
2011-01-02  Weekend     Sunday
2011-01-03  Weekday     Monday
2011-01-04  Weekday     Tuesday
2011-01-05  Weekday     Wednesday
2011-01-06  Weekday     Thursday
2011-01-07  Weekday     Friday
2011-01-08  Weekend     Saturday
2011-01-09  Weekend     Sunday
2011-01-10  Weekday     Monday

USE THIS

set nocount on

SET DATEFIRST 7;

go
select date,
datename(dw,datepart(dw,date)) Day,
datepart(dw,date) Day,
'Segment' = case  
when datepart(dw,date)in (5,6) then 'WEEK_END' else 'Week_day' end
from calenderdate

set nocount off

> set nocount on
> 
> select  date,
> substring(cast(datename(dw,datepart(dw,date))as
> varchar(10)),1,3) Day, 'Segment' =
> case   when datepart(dw,date)in (5,6)
> then 'WEEK_END' else 'Week_day' end
> from calenderdate
> 
> set nocount off

output

1/1/2011 Monday 7 Week_day 1/2/2011 Tuesday 1 Week_day