calculation not working! - SQL Server Forums

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)

calculation not working!

Author

Topic

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-04-02 : 05:47:37

Hi,

I'm trying to divide the results of a case statement by the results of a datediff statement and then multiply the end result by a numerical value. I keep getting 0 when I should be getting some other figures back. Any ideas what I'm doing wrong?

here is the script:

select
case status
when 'live' then DATEDIFF(month, live_date, getdate())
else
DATEDIFF(month, live_date, exit_actioned_date)
end
AS Number_of_Months_in_Force,

DATEDIFF(month, live_date, maturity_date) AS Number_of_Months_in_full_term,
sum_assured,

(CASE
WHEN DATEDIFF(month, live_date, maturity_date) = 0 then 0
else
(case status
when 'live' then DATEDIFF(month, live_date, getdate())
else
DATEDIFF(month, live_date, exit_actioned_date)
end/
DATEDIFF(month, live_date, maturity_date))*sum_assured
END)
AS Basic_Surrender_Value,

*
from
policy

Thanks and regards

ams006

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 05:52:04

Please post some sample data.

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-04-02 : 06:02:21

data

Number_of_Months_in_Force, Number_of_Months_in_full_term, sum_assured, Basic_Surrender_Value
9, 340, 6500, 0
9, 440, 9000, 0

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 06:18:08

Ok what is one suppose to understand from the "sample data" you provided w.r.t your query.

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-04-02 : 06:27:07

Ok, I haven't made myself very clear so I'll break it down....

in my script (which is in the first post i made) i have a case statement which is labelled as 'Number_of_Months_in_Force'.
Then I have a datediff statement which is labelled as 'Number_of_Months_in_full_term'.
Then i have a field called sum_assured which is coming straight from the table.

What I'm trying to do is the following calculation and i'm labelling it 'Basic_Surrender_Value' but zero is always returned.
Calculation is:

('Number_of_Months_in_Force'/ 'Number_of_Months_in_full_term')*sum_assured

The data which i sent was showing for the first record that the
Number_of_Months_in_Force = 9
Number_of_Months_in_full_term = 340
sum_assured = 6500 and
Basic_Surrender_Value was coming out as 0 when it should calculate to around 172.

hope this helps

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 06:56:05

Try like this
('Number_of_Months_in_Force' * 1.0/ 'Number_of_Months_in_full_term' * 1.0)*sum_assured * 1.0

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 07:00:45

select (9 * 1.0/340 * 1.0)*6500 * 1.0 = 172.05500000

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-04-02 : 07:05:09

Thanks mate, will give it a try.
Just for info purposes, why multiply each field by 1.0?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-02 : 07:10:45

hi ayamas,
u can multiple 1.0 for single time
then also u will get the value as = 172.0550000
select (9 * 1.0/340 )*6500

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 07:12:12

ur welcome
Because it will treat as integer & will discard the results after decimal point.Its equivalent to converting to decimal.

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-04-02 : 07:14:35

That's great....thanks, worked a treat! :-)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-02 : 07:15:34

quote:
Originally posted by ayamas

ur welcome
Because it will treat as integer & will discard the results after decimal point.Its equivalent to converting to decimal.

yes it is equivalent to converting to decimal
but it is sufficient using 1.0 one time i that query no need of 3 times that what i had said.........

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-02 : 07:15:48

quote:
Originally posted by ayamas

ur welcome
Because it will treat as integer & will discard the results after decimal point.Its equivalent to converting to decimal.

yes it is equivalent to converting to decimal
but it is sufficient using 1.0 one time i that query no need of 3 times that what i had said.........

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-04-02 : 07:19:22

Ayamas,
I wonder if you could help me with another issue. I posted this earlier but no replies yet and it's kinda urgent.
Hope you can help mate.

Hi all,

I'm trying to calculate the month between 2 specified dates and I'm using the following script:

select
case status
when 'live' then DATEDIFF(month, live_date, getdate())
else
DATEDIFF(month, live_date, exit_actioned_date)
end
AS Number_of_Months_in_Force,
*
from
policy

I've been reading the info in the link below regarding datediff not working as it is expected to. I've created the function but just need to know how to apply it to my script.

Any pointers would be appreciated.

[url]http://www.sqlteam.com/article/datediff-function-demystified[/url]

Thanks in advance

ams006

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-02 : 07:40:55

select
case
when status='live' then DATEDIFF(month, live_date, getdate())
else
DATEDIFF(month, live_date, exit_actioned_date)
end
AS Number_of_Months_in_Force,
*
from
policy

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 07:43:55

select
case when status=
'live' then fnMonthsApart(live_date, getdate())
else
fnMonthsApart(live_date, exit_actioned_date)
end
AS Number_of_Months_in_Force,
*
from
policy

Bklr Thanks for the catch about those 2 extra 1.0 .

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-04-02 : 08:18:31

Thanks matey,

will try after lunch!...cheers once again

ams006

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 09:17:33

So whats for lunch...?

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-04-02 : 10:26:06

Worked like a treat!
cheers for all your help ayamas.....
.....Lunch was a Subway!!..:-)