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

Print Text Month Calendar Expand / Collapse
Author
Message
Posted Wednesday, November 28, 2012 8:34 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 11:34 AM
Points: 313, Visits: 177
Comments posted to this topic are about the item Print Text Month Calendar
Post #1390244
Posted Friday, November 30, 2012 2:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 14, 2013 1:05 AM
Points: 96, Visits: 277
Hi Bernabe,
Nice idea if you have the need.

I was thinking of a less procedural approach and came up with this.

Given a "Calendar Table" with the following definition

CREATE TABLE [dbo].[Dates](
[CalendarDate] [smalldatetime] NOT NULL,
[isWeekday] [bit] NULL,
[isHoliday] [bit] NULL,
[CalendarYear] [smallint] NULL,
[CalendarQuarter] [tinyint] NULL,
[CalendarMonth] [tinyint] NULL,
[FinancialYear] [smallint] NULL,
[FinancialQuarter] [tinyint] NULL,
[FinancialMonth] [tinyint] NULL,
[DayOfMonth] [tinyint] NULL,
[DayOfWeek] [tinyint] NULL,
[MonthName] [varchar](9) NULL,
[ShortMonthName] [char](3) NULL,
[DayName] [varchar](9) NULL,
[ShortDayName] [char](3) NULL,
[SQL_Week] [tinyint] NULL,
[Week] [tinyint] NULL,
[HolidayDescription] [varchar](255) NULL
) ON [PRIMARY]

the following T-SQL seems to work quite well

DECLARE @supplieddate DATETIME
DECLARE @lastdayofmonth DATETIME
DECLARE @firstdayofmonth DATETIME
DECLARE @dayofweek INT
DECLARE @calendarbody VARCHAR(MAX)
DECLARE @title VARCHAR(MAX)
DECLARE @monthname VARCHAR(12)
DECLARE @paddedmonthname VARCHAR(24)
DECLARE @newline CHAR

SET @newline = CHAR(13)
SET @supplieddate = DATEADD(MONTH,-0,CURRENT_TIMESTAMP)

SELECT
@dayofweek = DATEPART(dw,MIN(CalendarDate)),
@firstdayofmonth = MIN(CalendarDate),
@lastdayofmonth = MAX(CalendarDate),
@monthname=DATENAME(MONTH,@supplieddate)
FROM Dates
WHERE CalendarMonth = MONTH(@supplieddate) AND CalendarYear = YEAR(@supplieddate)
GROUP BY CalendarMonth, CalendarYear


SET @paddedmonthname = REPLICATE(' ',6) +
@monthname +
' ' +
RTRIM(CAST(YEAR(@supplieddate) AS CHAR)) +
REPLICATE(' ',6 + (LEN(@monthname) % 2))

SET @title = ' <<' +
SUBSTRING(@paddedmonthname, (LEN(@paddedmonthname) / 2) - 4 + (LEN(@paddedmonthname) % 2), 16) +
'>>'
+@newline
+' Su Mo Tu We Th Fr Sa';

SET @calendarbody = REPLICATE(' ', 3 * (@dayofweek - 1))

SELECT
@calendarbody = @calendarbody +
RIGHT(' ' + CAST(DayofMonth AS VARCHAR(5)),3) +
(CASE WHEN DayOfWeek = 7 THEN @newline ELSE '' END)
FROM Dates
WHERE CalendarMonth = MONTH(@supplieddate) AND CalendarYear = YEAR(@supplieddate)

PRINT @title + @newline + @calendarbody


I realise that I am using the quirky update feature, but thought it was OK for now

My apologies for the poor formatting,
Pete
Post #1391141
Posted Friday, December 28, 2012 9:28 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:42 PM
Points: 128, Visits: 819
Here is a maintainable version...
DECLARE
@dt datetime,
@DayOneDayOfWeek int,
@DaysInMonth int,
@s varchar(200),
@crlf varchar(2)
;

SET @dt = '20130401';
SET @crlf = char(13) + char(10);

-- get max days in month: calculate first day of the next month and subtract one day
SET @DaysInMonth = (select datepart(day, dateadd(day, -1, dateadd(month, 1, cast(convert(varchar(8), @dt, 121)+'01' as datetime)))));

-- need to know day one weekday
SET @DayOneDayOfWeek = (select DatePart(WeekDay, cast(convert(varchar(8), @dt, 121)+'01' as datetime)));

-- build day numbers string and pad for first day
SET @s = REPLACE(SUBSTRING(' << January YEAR >> << February YEAR >> << March YEAR >> << April YEAR >> << May YEAR >> << June YEAR >> << July YEAR >> << August YEAR >> << September YEAR >> << October YEAR >> << November YEAR >> << December YEAR >>', (DatePart(month, @dt) - 1) * 21 + 1, 21), 'YEAR', cast(DatePart(year, @dt) as varchar(4)))
+ ' Su Mo Tu We Th Fr Sa'
+ replicate(' ', @DayOneDayOfWeek - 1)
+ left(' 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31', @DaysInMonth * 3)
;

-- Format for display by removing leading space and adding CRLF for each line.
print
SUBSTRING(@s, 2, 20)
+ @crlf + SUBSTRING(@s, 23, 20)
+ @crlf + SUBSTRING(@s, 44, 20)
+ @crlf + SUBSTRING(@s, 65, 20)
+ @crlf + SUBSTRING(@s, 86, 20)
+ @crlf + SUBSTRING(@s, 107, 20)
+ @crlf + SUBSTRING(@s, 128, 20)
+ case when len(@s) > 149 then @crlf + SUBSTRING(@s, 149, 20) else '' end
;


Post #1400952
Posted Wednesday, January 9, 2013 12:15 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 11:34 AM
Points: 313, Visits: 177
Bill, I like your version!

Thanks.
Post #1404940
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse