Welcome :Guest

Congratulations!!!

Top 5 Contributors of the Month
Melody Anderson
Eminent IT

 Home >> Code Snippets >> SQL Query >> Post New Resource

# Make a month calendar with sql server

Posted By:Kemal AL GAZZAH       Posted Date: January 26, 2016    Points: 40    Category: SQL Query    URL: http://www.dotnetspark.com

This scripts explains how to create a month calendar using sql sserver

There are many ways to display calendar without the need to write code
Calendar controls exist in DOT NET framework for example and we can add them and use them without the need to write code.

The objective of this code is to explore the possibility of creating a month calendar, then a year calendar using only sql script
The advantage of such approach is that you can later make any customisation you can imagine into the calendar you have created.

We will create a stored procedure called usp_calendar which will take as input a month and a year

How ?

First , we need to calculate how many days are in the month

declare @ed as int
if @m in (1,3,5,7,8,10,12) set @ed=31
if @m in (4,6,9,11) set @ed=30
if @y % 4=0 and @m=2 set @ed=29
if @y % 4!=0 and @m=2 set @ed=28

Then we make the the date representing the first day of the month
declare @date1 as datetime
set @date1='01/'+convert(varchar(2),@m)+'/'+convert(varchar(4),@y)

Then we calculate the week day of that date

declare @j as int
set @j=datepart(weekday,@date1)

Then we declare a table in which we will store the month calendar
declare @table  table("lun" varchar(2),"mar" varchar(2),"mer" varchar(2),"jeu" varchar(2),"ven" varchar(2),"sam" varchar(2),"dim" varchar(2))

Then according to the week day of the fisrt day we insert the first line into the table as follows

if @j=1 insert into @table values('1','2','3','4','5','6','7')
if @j=2 insert into @table values(' ','1','2','3','4','5','6')
if @j=3 insert into @table values(' ',' ','1','2','3','4','5')
if @j=4 insert into @table values(' ',' ',' ','1','2','3','4')
if @j=5 insert into @table values(' ',' ',' ',' ','1','2','3')
if @j=6 insert into @table values(' ',' ',' ',' ',' ','1','2')
if @j=7 insert into @table values(' ',' ',' ',' ',' ',' ','1')

Then we insert the remaining line as follows

declare @i as int
declare @dim1 as int
select @dim1=convert(int,dim) from @table
set @i=1
while @i<30
begin
insert into @table values(@dim1+@i,@dim1+@i+1,@dim1+@i+2,@dim1+@i+3,@dim1+@i+4,@dim1+@i+5,@dim1+@i+6)
set @i=@i+7
end

Finally, here is the complete code of the stored procedure

CREATE proc usp_calendar(@m int,@y int)
as
--kamel gazzah
--kamelgazzah@gmail.com
begin
/*declare @m as int
declare @y as int**/
declare @ed as int
/*set @m=7 set @y=2015*/
if @m in (1,3,5,7,8,10,12) set @ed=31
if @m in (4,6,9,11) set @ed=30
if @y % 4=0 and @m=2 set @ed=29
if @y % 4!=0 and @m=2 set @ed=28
declare @date1 as datetime
set @date1='01/'+convert(varchar(2),@m)+'/'+convert(varchar(4),@y)
declare @j as int
set @j=datepart(weekday,@date1)
declare @table  table("lun" varchar(2),"mar" varchar(2),"mer" varchar(2),"jeu" varchar(2),"ven" varchar(2),"sam" varchar(2),"dim" varchar(2))
if @j=1 insert into @table values('1','2','3','4','5','6','7')
if @j=2 insert into @table values(' ','1','2','3','4','5','6')
if @j=3 insert into @table values(' ',' ','1','2','3','4','5')
if @j=4 insert into @table values(' ',' ',' ','1','2','3','4')
if @j=5 insert into @table values(' ',' ',' ',' ','1','2','3')
if @j=6 insert into @table values(' ',' ',' ',' ',' ','1','2')
if @j=7 insert into @table values(' ',' ',' ',' ',' ',' ','1')
declare @i as int
declare @dim1 as int
select @dim1=convert(int,dim) from @table
set @i=1
while @i<30
begin
insert into @table values(@dim1+@i,@dim1+@i+1,@dim1+@i+2,@dim1+@i+3,@dim1+@i+4,@dim1+@i+5,@dim1+@i+6)
set @i=@i+7
end

if exists(select 1 from @table where lun=@ed)  and exists(select 1 from @table where lun='')
begin
update @table set lun='' where lun=@ed
update @table set lun=@ed where dim='1'
end

if exists(select 1 from @table where mar=@ed) and exists(select 1 from @table where lun='') and exists(select 1 from @table where mar='')
begin
update @table set lun='' where mar=@ed
update @table set mar='' where mar=@ed
update @table set lun=@ed-1  where sam=''
update @table set mar=@ed  where sam=''
end

update @table set lun='' where convert(int,lun)>@ed
update @table set mar='' where convert(int,mar)>@ed
update @table set mer='' where convert(int,mer)>@ed
update @table set jeu='' where convert(int,jeu)>@ed
update @table set ven='' where convert(int,ven)>@ed
update @table set sam='' where convert(int,sam)>@ed
update @table set dim='' where convert(int,dim)>@ed
delete from @table where lun ='' and mar='' and mer='' and jeu='' and ven='' and sam='' and dim=''
select * from @table

end

Responses

No response found. Be the first to respond this post

Post Comment