Click here to monitor SSC

Simple-Talk columnist

Monthly Calendars in Transact SQL

Published 10 January 2006 3:44 am

/*
I have been seized with the novel idea of writing something useful in a BLOG entry.

My eye was caught by the rather fetching calendar in the top right of the BLOG. I’ve had to write Transact SQL code that produces calendars like this in production systems before now; Most often when one has to get from a user the beginning date or end date for a report, or for doing complex drill-down reports. Normally, when writing a web application, I add the CSS/HTML as part of the stored procedure so that I can specify the values that are POSTed back on a user-click. (See ‘Eric Meyer on CSS’ ISBN 0-7357-1245 for everything you need to know about CSS rendering).


Here is the bare-bones routine



CREATE  PROCEDURE spCalendar 
–draw a calendar as a result set.
– you can specify the month if you want
@Date datetime=NULL–any date within the
– month that you want to calendarise.
/*
eg.    spCalendar ’1 Jan 2006′
       spCalendar ’16 Jun 2006′
*/
AS
SET 
nocount ON
DECLARE 
@sequence TABLE
(
number INT
)
–create utility sequence of a week
INSERT INTO @sequence(number)  SELECT 1
INSERT INTO @sequence(number)  SELECT 2
INSERT INTO @sequence(number)  SELECT 3
INSERT INTO @sequence(number )  SELECT 4
INSERT INTO @sequence(number)  SELECT 5
INSERT INTO @sequence(number)  SELECT 6
INSERT INTO @sequence(number)  SELECT 7

SET DATEFIRST 2
–nail down the start of the week
DECLARE @MonthLength INT –number of days in the month
DECLARE @MonthStartDW INT        –the day of the week that the…
                                –…month starts on
–if no date is specified, then use the current date
SELECT @Date=COALESCE(@date,GETDATE( ))
–get the number of days in the month and
–the day of the week that the month starts on
SELECT @MonthLength=DATEDIFF(DAY,CONVERT(CHAR(11),@Date,113),
       
CONVERT(CHAR(11),DATEADD(MONTH,1,@Date),113)),
       
@MonthStartDW=DATEPART(dw,’1 ‘+
               
SUBSTRINGCONVERT(CHAR(11),@Date,113),4,8 ))

SELECT
[Mon]=MAX(CASE WHEN DAY=AND monthdate BETWEEN AND @MonthLength 
       
THEN CONVERT(CHAR(2),monthdateELSE ” END),
[Tue]=MAX(CASE WHEN DAY=AND monthdate BETWEEN AND @MonthLength 
       
THEN CONVERT(CHAR(2),monthdateELSE ” END),
[Wed]=MAX(CASE WHEN DAY=AND monthdate BETWEEN AND @MonthLength 
       
THEN CONVERT(CHAR(2),monthdateELSE ” END),
[Thu]=MAX(CASE WHEN DAY=AND monthdate BETWEEN AND @MonthLength 
       
THEN CONVERT(CHAR(2),monthdateELSE ” END),
[Fri]=MAX(CASE WHEN DAY=AND monthdate BETWEEN AND @MonthLength 
       
THEN CONVERT(CHAR(2),monthdateELSE ” END),
[Sat]=MAX(CASE WHEN DAY=AND monthdate BETWEEN AND @MonthLength 
       
THEN CONVERT(CHAR(2),monthdateELSE ” END),
[Sun]=MAX(CASE WHEN DAY=AND monthdate BETWEEN AND @MonthLength 
       
THEN CONVERT(CHAR(2),monthdateELSE ” END)
FROM
       
(–roll out the day number and week number so
       –you can then do a pivot table of the results
       
SELECT [day]=DayNo.Number[week]=Weekno.number
       
[monthDate]=(DayNo.Number 
               
+ ((Weekno.number-1)*7))-@MonthStartDW   
       
FROM @Sequence DayNo  CROSS JOIN @sequence weekNo
       
)f
GROUP BY [week]–so that each week is on a different row
HAVING SUM(CASE WHEN DAY=AND monthdate BETWEEN 
       
AND @MonthLength THEN monthdate ELSE END)>OR week=1
–take out any weeks on the end without a valid day in them!

I once had the challenge of a boss who insisted on his financial summary reports in a calendar or diary format, as though one was chalking up the figures on a printed calendar. I rather sneered at first, because it was different, and IT people don’t instinctively like ‘different’, but it actually made a lot of sense with something like a daily revenue figures where there was a natural variation according to the day of the week and the part of the month. One naturally provides totals by week and day of week as well as the previous months summary.

It is for this sort of ‘diary-style’ reporting where a calendar routine becomes useful, but this is also where writing the HTML/CSS within the stored procedure really pays dividends, heretical though it may sound. It allows you to identify the functional components of the result. As you’d pull in a stylesheet to determine the actual rendering, the designer still has control over the appearance of the page.

I’d be interested to know of other approaches to this. Is this the simplest way of doing it, or is my obsession with cross-joins beginning to tell?

*/

11 Responses to “Monthly Calendars in Transact SQL”

  1. Anonymous says:

    Interesting. But try spCalendar ’31 Jan 2006′. The results say that January only has 28 days, because dateadd(month, 1, ’31 Jan 2006′) comes back as ’28 Feb 2006′…

  2. Phil Factor says:

    Thanks Kevin. Quite right. that bit of code aint safe!
    instead of…
    Select @Date=coalesce(@date,GetDate())
    …it should, perhaps, read…
    Select @Date=’01 ‘+substring(convert(char(11),coalesce(@date,GetDate()),113),4,8)

    I’ve been caught out that way before!

    the line….
    @MonthStartDW=Datepart(dw,’1 ‘+substring( convert(char(11),@Date,113),4,8))
    …could be simplified now.

  3. Anonymous says:

    still not right. The calendar should be correct irrespective of the DATFIRST setting (why set this to tuesday ?). Changing DATEFIRST to 1 (monday) or 7 (sunday) will give different results.

  4. Phil Factor says:

    An interesting comment. Changing any of the code within the SP would change the results, I suspect. I’m not quite sure that I understand your assertion that code should ‘be correct’ irrespective of the @@DateFirst setting.
    One should never, I believe, assume a particular value for @@DateFirst in normal circumstances. This is set for every connection from the default value for that login. This is inherited from SET LANGUAGE, which in turn gets it from the language specified for the particular login which, of course can be changed via sp_configure. This is enormously valuable if one is writing code that has to execute correctly in different countries or cultures, but I’m not sure it is relevant to a code snippet like this. If you were going to worry about it (you would need to if there were subsequent SPs on that connection which needed the value of the default setting to be in @@DateFirst) then It is perfectly possible to read the value of @@DateFirst and adjust the results of the @MonthStartDW to give it the same value irrespective of the setting of @@DateFirst. Even simpler would be save the value of @@DateFirst, change the value, via Set Datefirst to what you darned well like and to set it back after you’ve done it.
    The second solution is boring. The first is more intriguing. Can anyone come up with a simple solution that doesn’t require changing the @@Datefirst value? (assuming it is a naughty thing to do)

  5. Anonymous says:

    INGENIOUS !

  6. Anonymous says:

    This is the best I can do. It will look for the systems @@DATEFIRST setting and output the calendar that will be formatted appropriately.

    I also changed this line:
    [monthDate]=(DayNo.Number + ((Weekno.number-1)*7)) – @MonthStartDW
    to:
    [monthDate]=(DayNo.Number + ((Weekno.number-1)*7)) – (@MonthStartDW -1)

    This alleviated my problem with the calendar putting the numbers in the wrong spot depending on the @@DATEFIRST parameter.

    Body of code:
    ALTER procedure spCalendar
    –draw a calendar as a result set.
    – you can specify the month if you want
    @Date datetime=null–any date within the
    – month that you want to calendarise.
    /*
    eg. spCalendar ’1 Jan 2006′
    spCalendar ’16 Jun 2006′
    */
    as
    Set nocount on
    DECLARE @sequence TABLE
    (
    number int
    )
    –create utility sequence of a week
    INSERT INTO @sequence(number) SELECT 1
    INSERT INTO @sequence(number) SELECT 2
    INSERT INTO @sequence(number) SELECT 3
    INSERT INTO @sequence(number) SELECT 4
    INSERT INTO @sequence(number) SELECT 5
    INSERT INTO @sequence(number) SELECT 6
    INSERT INTO @sequence(number) SELECT 7

    –nail down the start of the week
    Declare @MonthLength int –number of days in the month
    Declare @MonthStartDW int –the day of the week that the month starts on
    Declare @FirstDayOfWeek int

    set @FirstDayOfWeek = @@DATEFIRST
    –if no date is specified, then use the current date
    –Select @Date=coalesce(@date,GetDate())
    Select @Date=’01 ‘+substring(convert(char(11),coalesce(@date,GetDate()),113),4,8)
    –get the number of days in the month and the day of the week that the month starts on
    Select @MonthLength=datediff(day,convert(char(11),@Date,113),convert(char(11),DateAdd(month,1,@Date),113)),
    @MonthStartDW=Datepart(dw,’1 ‘+substring( convert(char(11),@Date,113),4,8))

    if @FirstDayOfWeek = 1
    begin
    Select
    [Mon]=max(case when day=1 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Tue]=max(case when day=2 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Wed]=max(case when day=3 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Thu]=max(case when day=4 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Fri]=max(case when day=5 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sat]=max(case when day=6 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sun]=max(case when day=7 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end)
    from
    (–roll out the day number and week number so you can then do a pivot table of the results
    Select [day]=DayNo.Number, [week]=Weekno.number,
    [monthDate]=(DayNo.Number + ((Weekno.number-1)*7)) – (@MonthStartDW -1)
    from @Sequence DayNo cross join @sequence weekNo
    )f
    group by [week]–so that each week is on a different row
    having sum(case when day=1 and monthdate between 1 and @MonthLength then monthdate else 0 end)>0 or week=1
    –take out any weeks on the end without a valid day in them!
    end
    else if @FirstDayOfWeek = 2
    begin
    Select
    [Tue]=max(case when day=1 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Wed]=max(case when day=2 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Thu]=max(case when day=3 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Fri]=max(case when day=4 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sat]=max(case when day=5 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sun]=max(case when day=6 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Mon]=max(case when day=7 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end)
    from
    (–roll out the day number and week number so you can then do a pivot table of the results
    Select [day]=DayNo.Number, [week]=Weekno.number,
    [monthDate]=(DayNo.Number + ((Weekno.number-1)*7)) – (@MonthStartDW -1)
    from @Sequence DayNo cross join @sequence weekNo
    )f
    group by [week]–so that each week is on a different row
    having sum(case when day=1 and monthdate between 1 and @MonthLength then monthdate else 0 end)>0 or week=1
    –take out any weeks on the end without a valid day in them!
    end
    else if @FirstDayOfWeek = 3
    begin
    Select
    [Wed]=max(case when day=1 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Thu]=max(case when day=2 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Fri]=max(case when day=3 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sat]=max(case when day=4 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sun]=max(case when day=5 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Mon]=max(case when day=6 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Tue]=max(case when day=7 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end)
    from
    (–roll out the day number and week number so you can then do a pivot table of the results
    Select [day]=DayNo.Number, [week]=Weekno.number,
    [monthDate]=(DayNo.Number + ((Weekno.number-1)*7)) – (@MonthStartDW -1)
    from @Sequence DayNo cross join @sequence weekNo
    )f
    group by [week]–so that each week is on a different row
    having sum(case when day=1 and monthdate between 1 and @MonthLength then monthdate else 0 end)>0 or week=1
    –take out any weeks on the end without a valid day in them!
    end
    else if @FirstDayOfWeek = 4
    begin
    Select
    [Thu]=max(case when day=1 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Fri]=max(case when day=2 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sat]=max(case when day=3 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sun]=max(case when day=4 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Mon]=max(case when day=5 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Tue]=max(case when day=6 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Wed]=max(case when day=7 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end)
    from
    (–roll out the day number and week number so you can then do a pivot table of the results
    Select [day]=DayNo.Number, [week]=Weekno.number,
    [monthDate]=(DayNo.Number + ((Weekno.number-1)*7)) – (@MonthStartDW -1)
    from @Sequence DayNo cross join @sequence weekNo
    )f
    group by [week]–so that each week is on a different row
    having sum(case when day=1 and monthdate between 1 and @MonthLength then monthdate else 0 end)>0 or week=1
    –take out any weeks on the end without a valid day in them!
    end
    else if @FirstDayOfWeek = 5
    begin
    Select
    [Fri]=max(case when day=1 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sat]=max(case when day=2 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sun]=max(case when day=3 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Mon]=max(case when day=4 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Tue]=max(case when day=5 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Wed]=max(case when day=6 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Thu]=max(case when day=7 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end)
    from
    (–roll out the day number and week number so you can then do a pivot table of the results
    Select [day]=DayNo.Number, [week]=Weekno.number,
    [monthDate]=(DayNo.Number + ((Weekno.number-1)*7)) – (@MonthStartDW -1)
    from @Sequence DayNo cross join @sequence weekNo
    )f
    group by [week]–so that each week is on a different row
    having sum(case when day=1 and monthdate between 1 and @MonthLength then monthdate else 0 end)>0 or week=1
    –take out any weeks on the end without a valid day in them!
    end
    else if @FirstDayOfWeek = 6
    begin
    Select
    [Sat]=max(case when day=1 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sun]=max(case when day=2 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Mon]=max(case when day=3 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Tue]=max(case when day=4 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Wed]=max(case when day=5 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Thu]=max(case when day=6 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Fri]=max(case when day=7 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end)
    from
    (–roll out the day number and week number so you can then do a pivot table of the results
    Select [day]=DayNo.Number, [week]=Weekno.number,
    [monthDate]=(DayNo.Number + ((Weekno.number-1)*7)) – (@MonthStartDW -1)
    from @Sequence DayNo cross join @sequence weekNo
    )f
    group by [week]–so that each week is on a different row
    having sum(case when day=1 and monthdate between 1 and @MonthLength then monthdate else 0 end)>0 or week=1
    –take out any weeks on the end without a valid day in them!
    end
    else if @FirstDayOfWeek = 7
    begin
    Select
    [Sun]=max(case when day=1 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Mon]=max(case when day=2 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Tue]=max(case when day=3 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Wed]=max(case when day=4 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Thu]=max(case when day=5 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Fri]=max(case when day=6 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sat]=max(case when day=7 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end)
    from
    (–roll out the day number and week number so you can then do a pivot table of the results
    Select [day]=DayNo.Number, [week]=Weekno.number,
    [monthDate]=(DayNo.Number + ((Weekno.number-1)*7)) – (@MonthStartDW -1)
    from @Sequence DayNo cross join @sequence weekNo
    )f
    group by [week]–so that each week is on a different row
    having sum(case when day=1 and monthdate between 1 and @MonthLength then monthdate else 0 end)>0 or week=1
    –take out any weeks on the end without a valid day in them!
    end

  7. Phil Factor says:

    /*
    if anyone gets this far, this version should do the trick.
    */
    ALTER procedure spCalendar
    –draw a calendar as a result set. you can specify the month if you wwant
    @Date datetime=null–any date within the month that you want to calendarise.
    /*
    eg. spCalendar ’1 Jan 2006′
    Execute spCalendar ’1 sep 2005′
    Execute spCalendar ’1 nov 2004′
    Execute spCalendar ’28 feb 2008′
    Execute spCalendar ’1 mar 1949′
    Execute spCalendar ’1 aug 2000′

    */
    as
    Set nocount on
    DECLARE @sequence TABLE
    (
    number int
    )
    –create utility sequence of a week
    INSERT INTO @sequence(number) SELECT 1
    INSERT INTO @sequence(number) SELECT 2
    INSERT INTO @sequence(number) SELECT 3
    INSERT INTO @sequence(number) SELECT 4
    INSERT INTO @sequence(number) SELECT 5
    INSERT INTO @sequence(number) SELECT 6
    INSERT INTO @sequence(number) SELECT 7

    –nail down the start of the week
    Declare @MonthLength int –number of days in the month
    Declare @MonthStartDW int –the day of the week that the month starts on
    –if no date is specified, then use the current date
    Select @Date=’01 ‘+substring(convert(char(11),coalesce(@date,GetDate()),113),4,8)
    –get the number of days in the month and the day of the week that the month starts on
    Select @MonthLength=datediff(day,convert(char(11),@Date,113),convert(char(11),DateAdd(month,1,@Date),113)),
    @MonthStartDW=((Datepart(dw,@date)+@@DateFirst-3) % 7)+1

    Select
    [Mon]=max(case when day=1 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Tue]=max(case when day=2 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Wed]=max(case when day=3 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Thu]=max(case when day=4 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Fri]=max(case when day=5 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sat]=max(case when day=6 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end),
    [Sun]=max(case when day=7 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else ” end)
    from
    (–roll out the day number and week number so you can then do a pivot table of the results
    Select [day]=DayNo.Number, [week]=Weekno.number,
    [monthDate]=(DayNo.Number + ((Weekno.number-1)*7))-@MonthStartDW
    from @Sequence DayNo cross join @sequence weekNo
    )f
    group by [week]–so that each week is on a different row
    having max(case when day=1 and monthdate between 1 and @MonthLength then monthdate else 0 end)>0
    or (week=1 and sum(MonthDate)>-21)
    –take out any weeks on the end without a valid day in them!

  8. Anonymous says:

    Here is an alternate method for your amusement. I didn’t go so far as to get the column names to follow the correct “first day of week”:

    CREATE PROCEDURE usp_Calendar
    @Date DATETIME = NULL,
    @firstDay CHAR(3) = ‘SUN’
    AS
    SET NOCOUNT ON
    SELECT @Date = ISNULL(@Date,GetDate())

    DECLARE @T TABLE (Day_of_month int, Day_of_week int, Calendar_row int, Calendar_Column int)
    DECLARE @tmpDate datetime, @Day int, @Month int, @year int

    SELECT @month = month(@date), @Year = year(@date)
    SELECT @date = cast(@year as varchar) + N’-’ + cast(@month as varchar) + ‘-01′

    SELECT @day=0, @tmpDate = @Date
    WHILE month(@tmpDate) = @month
    BEGIN
    INSERT INTO @T (Day_of_month, Day_of_week)
    SELECT DAY(@tmpDate), DATEPART(dw,@tmpDate)
    set @day=@Day+1
    set @tmpDate = DATEADD(d,@DAY,@Date)
    END

    declare @FirstDayOffset int
    SET @FirstDayOffset =
    CASE UPPER(@firstDay)
    when ‘SUN’ THEN 0 when ‘MON’ THEN 6 when ‘TUE’ THEN 5 when ‘WED’ THEN 4
    when ‘THU’ THEN 3 when ‘FRI’ THEN 2 when ‘SAT’ THEN 1
    END
    Update @T set Calendar_Column = ((Day_of_week-1 + @FirstDayOffset)%7)+1

    declare @startFirstFull int, @firstWeekOffset int
    select @startFirstFull = min(Day_of_month) from @T where Calendar_column = 1
    select @firstWeekOffset = CASE @startFirstFull WHEN 1 THEN 0 ELSE 1 END

    Update @T set Calendar_Row = ((Day_of_month+7-@startFirstFull)/7)+@firstWeekOffset
    PRINT ‘ ‘ + DATENAME(m, @Date) + N’ ‘+ CAST(@YEAR as CHAR(4))
    PRINT @firstDay
    SELECT CASE SUM(D1) WHEN 0 THEN ‘ ‘ ELSE CAST(SUM(D1) AS CHAR(2)) END,
    CASE SUM(D2) WHEN 0 THEN ‘ ‘ ELSE CAST(SUM(D2) AS CHAR(2)) END,
    CASE SUM(D3) WHEN 0 THEN ‘ ‘ ELSE CAST(SUM(D3) AS CHAR(2)) END,
    CASE SUM(D4) WHEN 0 THEN ‘ ‘ ELSE CAST(SUM(D4) AS CHAR(2)) END,
    CASE SUM(D5) WHEN 0 THEN ‘ ‘ ELSE CAST(SUM(D5) AS CHAR(2)) END,
    CASE SUM(D6) WHEN 0 THEN ‘ ‘ ELSE CAST(SUM(D6) AS CHAR(2)) END,
    CASE SUM(D7) WHEN 0 THEN ‘ ‘ ELSE CAST(SUM(D7) AS CHAR(2)) END
    FROM
    (SELECT Calendar_Row as ROWNUM,
    CASE Calendar_Column WHEN 1 THEN Day_of_month ELSE 0 END as D1,
    CASE Calendar_Column WHEN 2 THEN Day_of_month ELSE 0 END as D2,
    CASE Calendar_Column WHEN 3 THEN Day_of_month ELSE 0 END as D3,
    CASE Calendar_Column WHEN 4 THEN Day_of_month ELSE 0 END as D4,
    CASE Calendar_Column WHEN 5 THEN Day_of_month ELSE 0 END as D5,
    CASE Calendar_Column WHEN 6 THEN Day_of_month ELSE 0 END as D6,
    CASE Calendar_Column WHEN 7 THEN Day_of_month ELSE 0 END as D7
    FROM @T) X
    GROUP BY ROWNUM
    ORDER BY ROWNUM

  9. Anonymous says:

    Actually, the trick is that the C.E. (Common Era nee Georgian) Calendar has a 19 year cycle that matches days of the month to days of the week. That is about 7,000 rows in a calendar table with all the information you need. A calendar table for US Secular holidays can be built from the data at this website:

    http://www.smart.net/~mmontes/ushols.html

  10. Phil Factor says:

    Joe’s link is a most interesting one. One implication of Joe’s suggestion is that the calculation of many holidays, saints days and festival days based on the ‘third saturday in the month’ or whatever, need not be calculated as 7000 rows is nowdays a pifflingly small table. However, I’m not sure if this would help with the calculation of Ramadan or even Easter. Does anyone know?
    I have a friend who is studying mediaeval documents where dates are all reckoned by proximity to saints’ days. It would be handy to have a calculator for him that would allow him when a document says ‘on Tuesday next after the feast of St. Matthew the Apostle, A. D. 1308, in the first year of the reign of King Edward, son of King Edward,’, to work out the date!

  11. Anonymous says:

    Iwant to create view from Some views and Iwant to select data from othor view
    by month and year

    thanks

Leave a Reply