month and year calculation in Stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

month and year calculation in Stored procedure

Author

Topic

ambujnema
Starting Member

11 Posts

Posted - 2010-06-18 : 12:41:38

Hi,

I have a problem described below

Set @StartMonth = Aug
Set @EndMonth = Jan
Set @StartYear = 2009
Set @EndYear = 2010

I need to insert records into one new table based on the above informations
Month name should go from StartMonth to EndMonth and Year also should insert like below

MonthName Year
Aug 2009
Sep 2009
Oct 2009
Nov 2009
Dec 2009
Jan 2010

2) when I pass again

Set @StartMonth = Dec
Set @EndMonth = Feb
Set @StartYear = 2009
Set @EndYear = 2010

It should check for the records already in the database and should not insert the duplicate record. Based on the above case it should insert only one record

MonthName Year
Feb 2010

Regards,

Please suggest me the logic how can we implement in SQL Server 2005.

ambujnema
Starting Member

11 Posts

Posted - 2010-06-18 : 12:47:48

I need to also calculate number of days in the month except sat and sunday ..

Like Jan2010 we have 21 days except Saturday and Sunday

Is their any logic through which we calculate the number of days.?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-06-18 : 20:03:21

Here is the basic record set. You should be able to use a NOT EXISTS in your INSERT/SELECT that prevents duplicates. The number of days logic is on you ;-)[CODE]declare
@StartMonth char(3) = 'Aug',
@EndMonth char(3) = 'Jan',
@StartYear int = 2009,
@EndYear int = 2010;

declare @Months table (
strMonth char(3),
intMonth int
)

insert into @Months (strMonth, intMonth)
select 'JAN', 01 union all
select 'FEB', 02 union all
select 'MAR', 03 union all
select 'APR', 04 union all
select 'MAY', 05 union all
select 'JUN', 06 union all
select 'JUL', 07 union all
select 'AUG', 08 union all
select 'SEP', 09 union all
select 'OCT', 10 union all
select 'NOV', 11 union all
select 'DEC', 12;

-------------------------------

with Years(MyYear)
as (
select @StartYear
union all
select MyYear + 1
from Years
where MyYear < @EndYear
)
select
y.MyYear,
m.strMonth
from
@Months m
cross join
Years y
where
y.MyYear * 100 + m.intMonth between
@StartYear * 100 + (select intMonth from @Months where strMonth = @StartMonth) and
@EndYear * 100 + (select intMonth from @Months where strMonth = @EndMonth)
order by
y.MyYear,
m.intMonth[/CODE]

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

ambujnema
Starting Member

11 Posts

Posted - 2010-06-19 : 05:22:19

Hi
thanks for the reply ,i am pretty new to sql server please tell me also how to check exsisting records in the table using IF EXSITS ..the number of business day calculation i can take care.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-06-21 : 11:44:16

The basic pattern is:

insert into MyTable
select ...
from SourceTable
where NOT EXISTS (select * from MyTable where MyTable.Key = SourceTable.Key)

In essence the query is saying, "If this record already exists, don't insert it."

HTH

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources