SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Print The Calender 2


Print The Calender 2

Author
Message
Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2772 Visits: 1549
Comments posted to this topic are about the item Print The Calender 2

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
bitbucket-25253
bitbucket-25253
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24301 Visits: 25280
Nice thanks added it to my SANDBOX for future use

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Jack B.
Jack B.
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 85
You are a bit US centric... ;-) Should have "SET DATEFIRST 7" in there if you want to make sure that your script runs correctly everywhere (or take @@DATEFIRST into account when identifiying the given day of week).
Andrew Diniz
Andrew Diniz
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 293
A calender is a series of hard pressure rollers used to form or smooth a sheet of material (see http://en.wikipedia.org/wiki/Calender).

Not sure how this relates to dates. Hehe
ACinAZ
ACinAZ
Mr or Mrs. 500
Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)Mr or Mrs. 500 (599 reputation)

Group: General Forum Members
Points: 599 Visits: 957
^^^^^
HA! That's exactly what I was thinking! CALENDAR <> CALENDER
(Maybe he drives around in a Camero, too...)
Arjen Krap
Arjen Krap
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 215
Another way to print a calendar using PIVOT:


CREATE FUNCTION CalenderUsingPivot
(
@BeginDate date
, @EndDate date
)
RETURNS @Calendar TABLE
(
Week tinyint
, Monday tinyint
, Tuesday tinyint
, Wednesday tinyint
, Thursday tinyint
, Friday tinyint
, Saturday tinyint
, Sunday tinyint
)
AS
BEGIN
DECLARE @date TABLE
(
Date date
, Day AS DAY(Date)
, Week AS DATEPART(week, Date)
, Weekday AS DATENAME(weekday, Date)
);

DECLARE @i date;

SET @i = @BeginDate;

WHILE @i <= @EndDate
BEGIN
INSERT @date VALUES (@i);
SET @i = DATEADD(day, 1, @i);
END;

INSERT @Calendar
SELECT
Week
, p.Monday
, p.Tuesday
, p.Wednesday
, p.Thursday
, p.Friday
, p.Saturday
, p.Sunday
FROM
(
SELECT Day, Week, Weekday
FROM @date
) AS c
PIVOT
(
MAX(Day) FOR Weekday IN
(
Monday
, Tuesday
, Wednesday
, Thursday
, Friday
, Saturday
, Sunday
)
) AS p
ORDER BY Week;

RETURN;
END;

GO

SET LANGUAGE English;

SELECT *
FROM dbo.CalenderUsingPivot('2012-01-01', '2012-12-31');


hannes.malan
hannes.malan
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 252
I really liked your calendar idea, so I amended it a bit for my use..

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[calendar2print]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].calendar2print
GO

/***************************************************************************************
Created By :- Vinay Kumar
Created on :- 29 Jan 2008
EXEC calender '01/01/2016'
Purpose :- Print the calender from the given Date until end of current year
------------------------------------
Amended by Hannes Malan
Amended On :- Aug 13, 2012
EXEC calendar2print @StartDate=null, @fullmonth=1, @months=1
Purpose :- Print the calender from the given Date ...
..and if @StartDate is null, use current date (@StartDate)
..also from specified date or from first day of specified month (@fullmonth)
..for a certain amount of months (@months)
****************************************************************************************/

Create Procedure calendar2print
@StartDate DateTime
,@fullmonth int = 1
,@months int = 1
As
set nocount on

if @StartDate is null set @StartDate = getdate()

if (@fullmonth = 1) begin
set @StartDate = '01 ' + datename(mm,@StartDate) +' '+ datename(yy,@StartDate)
end

set datefirst 7
DECLARE @tbl Table(RowID int identity(1,1), Sun varchar(20) default '', Mon varchar(20) default '', Tue varchar(20) default '',
Wed varchar(20) default '', Thu varchar(20) default '', Fri varchar(20) default '', Sat varchar(20) default '')

declare @monthCount int
set @monthCount = 1
DECLARE @currentMonth int
DECLARE @CurrentDate int
DECLARE @CurrentDay int
DECLARE @CurrentRowID int
DECLARE @TotalMonthDays int
DECLARE @monthName varchar(20)


set @CurrentDate = datepart(dd, @StartDate)
set @currentMonth = datepart(mm, @StartDate)
set @CurrentDay = datepart(dw, @StartDate)

insert @tbl(Mon) values ('')
set @CurrentRowID = @@Identity

while @monthCount <= @months begin
set @monthCount = @monthCount + 1
set @TotalMonthDays = case when month(@StartDate) in (1,3,5,7,8,10,12) then 31
when month(@StartDate) in (4,6,9,11) then 30
when month(@StartDate) = 2 and year(@StartDate) % 4 = 0 then 29
when month(@StartDate) = 2 and year(@StartDate) % 4 <> 0 then 28
end

set @monthName = datename(m, @StartDate)
insert @tbl(Tue, Thu) values (@monthName, Convert(Varchar, year(@StartDate)))
set @CurrentRowID = @@Identity

insert @tbl(Sun,Mon,Tue,Wed, Thu, Fri,Sat) values ('_______', '_______', '_______', '_______', '_______', '_______', '_______')

insert @tbl(Sun) values ('')
set @CurrentRowID = @@Identity

while @CurrentDate <= @TotalMonthDays begin
if @CurrentDay = 1 begin
insert @tbl(Sun) values ('')
set @CurrentRowID = @@Identity
end
if @CurrentDay = 1
update @tbl set Sun = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 2
update @tbl set Mon = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 3
update @tbl set Tue = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 4
update @tbl set wed = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 5
update @tbl set Thu = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 6
update @tbl set Fri = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
else if @CurrentDay = 7
update @tbl set Sat = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID


set @CurrentDate = @CurrentDate + 1
if @CurrentDate = @TotalMonthDays + 1 begin
IF (Datepart(DD,@StartDate)>1)
set @StartDate = DATEADD(Day,-(Datepart(DD,@StartDate)-1),@StartDate)
set @StartDate = DATEADD(Month,1,@StartDate)
set @CurrentDay = datepart(dw, @StartDate)
insert @tbl(Sun,Mon,Tue,Wed,Thu,Fri,Sat)
values ('*******', '*******', '*******', '*******', '*******', '*******', '*******')
end else begin
set @CurrentDay = (@CurrentDay + 1) % 8
if (@CurrentDay = 0) set @CurrentDay = 1
end
end
set @CurrentDate = 1
end


-- Finaly show the output
select Sun, Mon, Tue, Wed, Thu, Fri, Sat
from @tbl
where Mon <> '' or Tue <> '' or Wed <> ''
or Thu <> '' or Fri <> '' or Sat <> '' or Sun <> ''



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)SSC Guru (340K reputation)

Group: General Forum Members
Points: 340880 Visits: 42650
hannes.malan (8/13/2012)
I really liked your calendar idea, so I amended it a bit for my use.


I have to ask... what will you actually end up using this for? Not challenging what you're doing. I just really want to know.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
hannes.malan
hannes.malan
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 252
hehehe.. , so far ...I have no idea...

I just like the odd script that is off the beaten track
:-P

to expound a bit further...
...one might not use the script it was written for, but at times you get to a situation where you need similar logic to solve some kind of bussiness need.
timothy.shawley
timothy.shawley
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 93
One issue - the Leap Day calculation is missing one constraint - February does not have 29 days in Centuries not divisible by 400, ie 2100 will be 28 days, but 2000 was 29 days.

Otherwise the script looks like it works like a charm...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search