Calculations in Database

I have a procedure that is performing a calculation and returning a amount, how do I store or save that amount and use it in different procedure to perform another calculationYou can simply store the value in a temporatry table.|||

Quote:

Originally Posted by debasisdas

You can simply store the value in a temporatry table.

how does one create a temporary table|||Hi,

maybe I am not understanding this but I would ask why you want to save a derived value - why not just call the first calculation procedure from the second then your data is guaranteed to be an acurate reflection of what is on the database at the time of the query..

Just my thoughts..

Regards Purple|||Yes that will be a better option.|||Thank you all, it worked out perfectly. I tried Purples advice and it was exactly what was needed. Thanks again|||Hi hopechirume,

I am pleased we were able to assist - look forward to seeing you around again soon.

Regards Purple

calculating age using sql server function

Hi ya,

I have a table which will be having DOB and I want to calculate the current age of persons, i want to save it into currentage as default.

I have searched over the interent and find some solutions but in my database the DOB is stored as = 'dd/mm/yyyy' and none of the solutions work on this.

Any ideas?

Prince:

I assume by your post that your DOB is stored as a string. Try:

select datediff (year, convert (datetime, '07/15/1908'), getdate())

Dave

|||

Mugambo wrote:

Prince:

I assume by your post that your DOB is stored as a string. Try:

select datediff (year, convert (datetime, '07/15/1908'), getdate())

Dave

Hello Dave,

No you got me wrong, the DOB is stored as Datetime in Sql 2005, the other thing is that datediff is not going to calculate right and thirdly my dates are stored as datetime but in dd/mm/yyyy format.

You can try it by yourself.

|||

First, create this user defined function.

ALTER function [dbo].[fn_GetAge]

(@.in_DOB AS datetime,@.now as datetime)

returns int

as

begin

DECLARE @.age int

IF cast(datepart(m,@.now) as int) > cast(datepart(m,@.in_DOB) as int)

SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)

else

IF cast(datepart(m,@.now) as int) = cast(datepart(m,@.in_DOB) as int)

IF datepart(d,@.now) >= datepart(d,@.in_DOB)

SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)

ELSE

SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) -1

ELSE

SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) - 1

RETURN @.age

end

-

Then, to use it...

Select DOB, dbo.fn_GetAge(DOB, GetDate()) As Age From Table

Since all calculations are based on DateTime's, your formatting shouldn't matter

|||

thanks mastros,

it works really great.