Calculation - 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

General SQL Server Forums

New to SQL Server Programming

Calculation

Author

Topic

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-23 : 15:07:21

HI I am trying to do a calculation : I know it pretty simple but I am a bit in clouds today :)

here is what I want

If @numerator = 0 do nothing
if @numerator >5 then (@numerator -5). But I don't want any "negative" numbers

How can I do this?
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-23 : 15:18:11

if @numerator > 5
set @numerator = @number - 5

But what do you want to do when the calculation is less than zero?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-23 : 15:58:22

Hey Tara,

Ok if I use tis code---> this code is at the end of a long code..

SELECT @denom1 as 'Denominator',
@numer1 as 'Numerator'
(@numer1 -5) as 'Numerator' where @numer1 > 5

I get "negative" number in denominator. Like 32/-6.

I am trying to modify this code to say , if Numer1 is 0 and denom1 is 0 then 0 , if numer1 greater than 5 then subtract 5. I still want to see my 0's I don't want to remove them.

Thanks!

Don't worry about the FROM clause its already in a temp table.

quote:
Originally posted by tkizer

if @numerator > 5
set @numerator = @number - 5

But what do you want to do when the calculation is less than zero?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-23 : 16:01:01

I don't understand. Please show us several rows of sample data for us to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-23 : 16:31:28

Sorry, its just hard for me to show you sample data. its in a report.

quote:
Originally posted by tkizer

I don't understand. Please show us several rows of sample data for us to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-23 : 16:41:29

I don't need to see data from a report. I need you to clearly explain the issue with sample data for the variables plus expected outcome using the sample data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-23 : 16:52:12

OK I di explain what I am looking for, I think my calculation is wrong, based on setting up the numer1 and denom1.

How do I set up @numer1 = 0 and greater than 5? is it @numer1 =0 > 5?

quote:
Originally posted by tkizer

I don't need to see data from a report. I need you to clearly explain the issue with sample data for the variables plus expected outcome using the sample data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-23 : 17:02:29

quote:
Originally posted by Pasi

OK I di explain what I am looking for

But your explanation isn't clear to me.

quote:
Originally posted by Pasi

How do I set up @numer1 = 0 and greater than 5? is it @numer1 =0 > 5?

Show us several sample data for @numer1 and what you expect it to equal after the calculation.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-23 : 17:18:29

Hope I could copy /pate to this msg but its not that easy. I know you told me last time to upload but the instructions where vague to me.

quote:
Originally posted by tkizer

quote:
Originally posted by Pasi

OK I di explain what I am looking for

But your explanation isn't clear to me.

quote:
Originally posted by Pasi

How do I set up @numer1 = 0 and greater than 5? is it @numer1 =0 > 5?

Show us several sample data for @numer1 and what you expect it to equal after the calculation.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-23 : 18:01:02

quote:
Originally posted by Pasi

I know you told me last time to upload but the instructions where vague to me.

How are step by step instructions on how to post your question along with sample data and expected output in anyway vague?

If you are having trouble understanding the instructions, start a new topic and we might be able to help you understand them better. But, as far as I can tell, given the number of times you have been asked, providing sample data is something you don't want to be bothered doing. I'm more than happy to help when I can, but when I see someone that doesn't put any effort into trying, I'm inclined to do the same.

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-23 : 18:22:20

You got it completely wrong. What I said was The instruction is not clear to me, I didn't mean that I dont want to bother uploading data.

I will look into it again and see if I can upload sample data if time permits. Thanks.

quote:
Originally posted by Lamprey

quote:
Originally posted by Pasi

I know you told me last time to upload but the instructions where vague to me.

How are step by step instructions on how to post your question along with sample data and expected output in anyway vague?

If you are having trouble understanding the instructions, start a new topic and we might be able to help you understand them better. But, as far as I can tell, given the number of times you have been asked, providing sample data is something you don't want to be bothered doing. I'm more than happy to help when I can, but when I see someone that doesn't put any effort into trying, I'm inclined to do the same.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-23 : 18:37:27

It's really quite simple for this thread. I will give an example

Sample data:
declare @i int
set @i = 25
Expected output: 20
set @i = 5
Expected output: 0
...
Give us some sample data and expected output!

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-23 : 21:38:01

I don't have the whole picture, but here is how to resolve bits and pieces of it

quote:
if Numer1 is 0 and denom1 is 0 then 0

assuming you are doing some calculation like Numer1 / denom1

isnull(@numer1 / nullif(@denom1, 0), 0)

quote:
if numer1 greater than 5 then subtract 5

@numer1 % 5

quote:
I don't want any "negative" numbers

use ABS ( <expression> )

Now you just need to put it all together

KH
[spoiler]Time is always against us[/spoiler]

Pasi
Posting Yak Master

166 Posts

Posted - 2014-05-24 : 19:59:25

Thanx khtan, I tired your suggestion but not working.

quote:
Originally posted by khtan

I don't have the whole picture, but here is how to resolve bits and pieces of it

quote:
if Numer1 is 0 and denom1 is 0 then 0

assuming you are doing some calculation like Numer1 / denom1

isnull(@numer1 / nullif(@denom1, 0), 0)

quote:
if numer1 greater than 5 then subtract 5

@numer1 % 5

quote:
I don't want any "negative" numbers

use ABS ( <expression> )

Now you just need to put it all together

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-25 : 04:23:51

so how does your full query looks like ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources