can u help me in writing me 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 2008 Forums

Transact-SQL (2008)

can u help me in writing me stored procedure

Author

Topic

amulya.j2
Starting Member

5 Posts

Posted - 2011-01-28 : 23:19:18

Hi ,
This is amulya, trying to write stored procedure for the below
scenario... i m struck ..can u help me in cracking this soln.

I m basically trying to find the year of pension income which is in
age as now in second time

Table – DobTable
SL_NO DOB
1 1/1/1950

Table - YearTable
SL_N0 REF_Year Pension_income_start_age
1 12/12/2010 55

Logic :
1. First i will find my current age
My current age = dob – ref_year

2. I will get the difference between current age and pension Income start age
Difference b/n my pension income age and current age = currentage – pension income start age

3. Then i will add the diiference in age with ref_year that will give me pension income start year.
Year of my pension income = ref year of table 2 + diff b/n my pension income age and current age

Regards,
Amulya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-29 : 11:31:35

[code]
SELECT DATEADD(yy,CASE WHEN MONTH(REF_Year) < MONTH(DOB) OR ((MONTH(REF_Year) = MONTH(DOB)) AND (DAY(REF_year) < DAY(DOB))) THEN DATEDIFF(yy,DOB,REF_Year)-1 ELSE DATEDIFF(yy,DOB,REF_Year) END - Pension_income_start_age,REF_Year) AS [pension income start year]
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.SL_No = t1.SL_No
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

amulya.j2
Starting Member

5 Posts

Posted - 2011-01-29 : 14:05:22

visak, thank u soo much.. please if you dont mind can you explain me on this ? i m little confused..
i guess its single select statement right ?
how easy is it to do in stored procedure visak
i tired something like this

CREATE FUNCTION dbo.GetAge ( @pDateOfBirth DATETIME, @pAsOfDate DATETIME )
RETURNS INT
AS
BEGIN

DECLARE @vAge INT

IF @pDateOfBirth >= @pAsOfDate
RETURN 0

SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate)

IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR
(MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND

DAY(@pDateOfBirth) > DAY(@pAsOfDate))

SET @vAge = @vAge - 1

RETURN @vAge

END

GO

CREATE FUNCTION dbo.GetAgeDiff(@pensionAge int, @currage int)

RETURNS INT

AS

BEGIN

DECLARE @vAge INT

set @vAge = @currage - @pensionAge

RETURN @vAge

END

GO

Create function dbo.GetPensionYear(@ageDiff int, @datetime DateTime)

RETURNS INT

AS

BEGIN

Declare @refYear INT

DECLARE @year INT

set @refYear = year(@datetime)

set @year = @refYear + @ageDiff

RETURN @year

END

GO

-- pension start year

select dbo.GetPensionYear(dbo.GetAgeDiff(pension_income_Start_Age, dbo.GetAge(Table1.DATE_OF_BIRTH,table2.PLAN_DATE)),

table2.PLAN_DATE) As Pension_Start_Year

from table1, table2 where Table1.DATE_OF_BIRTH is not null

and table2.PLAN_DATE is not null and Table1.sl_no = table2.sl_no and

table2.pension_income_start_age is not null and

table2.pension_income_start_age <>0

Go

amulya.j2
Starting Member

5 Posts

Posted - 2011-01-30 : 22:19:36

visak , i m getting this error
Msg 517, Level 16, State 1, Line 1
Adding a value to a 'datetime' column caused an overflow.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-31 : 00:21:38

getting it as a result of the select Statement? if so which one .. the one specified to by by Visakh or any other one ..
By the way if the code Specified by Visakh is correct then why you are not using it?

amulya.j2
Starting Member

5 Posts

Posted - 2011-01-31 : 22:02:04

i m getting the error from vikas code. please let me know

amulya.j2
Starting Member

5 Posts

Posted - 2011-01-31 : 22:53:59

what if age field is null

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-31 : 23:01:05

According to the error, you are providing an incorrect value for the date/time value. If age is NULL, then you need to decide what it should return.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources