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 12»»

Print The Calender 2 Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 5:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Comments posted to this topic are about the item Print The Calender 2



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1338898
Posted Wednesday, August 1, 2012 7:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:55 PM
Points: 5,333, Visits: 25,266
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
Post #1338915
Posted Friday, August 10, 2012 2:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 1:50 AM
Points: 91, 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).
Post #1343204
Posted Friday, August 10, 2012 4:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:11 AM
Points: 216, Visits: 273
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.
Post #1343245
Posted Friday, August 10, 2012 1:32 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:27 PM
Points: 109, Visits: 957
^^^^^
HA! That's exactly what I was thinking! CALENDAR <> CALENDER
(Maybe he drives around in a Camero, too...)
Post #1343634
Posted Friday, August 10, 2012 11:44 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:34 AM
Points: 51, Visits: 184
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');

Post #1343749
Posted Monday, August 13, 2012 5:29 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:10 AM
Points: 21, Visits: 178
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 <> ''


Post #1344079
Posted Monday, August 13, 2012 5:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1344088
Posted Monday, August 13, 2012 5:59 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:10 AM
Points: 21, Visits: 178
hehehe.. , so far ...I have no idea...

I just like the odd script that is off the beaten track


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.
Post #1344095
Posted Monday, August 13, 2012 12:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 11:49 AM
Points: 68, Visits: 69
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...
Post #1344360
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse