In SQL I need to do the following:

If CallTime < 100 Minutes Then Indicator = '0'
If CallTime >100 and CallTime < 199 minutes, Then Indicator = '1'
If CallTime > 200 Then

Record CallTime Indicator
1st 200 4
n 100(each additional 200 minutes of call) 5
last Remainder minutes of call(Must be < 200) 6

The Indicator is what I need to return.

e.g.

CallTime = 500 minutes

Record CallTime Indicator
1 200 4
2 100 5
3 100 5
4 100 6

In my Stored Procedure, I wrote as:

Case When CallTime < '100' Then '0' Else
Case When (CallTime > '100' and CallTime < '199') Then '7' Else
??????

I don't know whether I can achieve in this way.

Could anyone give me a suggestion how to do that?

Thanks alot.

I got confused in the part ... could you elaborate ???

Record CallTime Indicator
1st 200 4
n 100(each additional 200 minutes of call) 5
last Remainder minutes of call(Must be < 200) 6


So, suppose the Total CallTime = 500 Then it will be seperated into 4 records as following


Record CallTime Indicator
1 200 4
2 100 5
3 100 5
4 100 6

So you mean if we have a record

then
it will become

I mean change the original table into our required table.

But currently, I only need to identify the Indicator

Thanks

Can I create a function to get the indicator, then use the function in Stored Procedure instead of using the "case"?

Thanks alot

I must say ... this looks like a job which will need a loop ... a cursor or a while loop ...

The following is my function, is this right?

Please help!

CREATE Function getInd(@CT varchar(50))
returns varchar(50)
as
begin

--@CT is CallTime


declare @Ind varchar(50)

If (@CT <= '100') Begin
Set @Ind = '0'
End
Else If (@CT > '100' and @CT < '200') Begin
Set @Ind = '1'
End
Else If (@CT > ='200') Begin
set @CT = @CT - 200
set @Ind = '4'

While (@CT - 100 > 0) Begin

Set @CT = @CT - 100
Set @Ind = '5'
End

set @Ind = '6'
End


return @Ind
End

I Think that the @ct needs to be numeric ...

I use functions in some of my queries to do something similar to what you are doing...there's no reason why you can't send the total time into the function, perform the looping logic inside the function, then spit back out the indicator. And yes, this (the function call) can be done in the actual select.

I am also a relative newbie to SQL Server, but use functions quite a bit, and I think your scenario is an excellent example of when a function should be used.

My .02...

you are also attempting to do numeric operations on your @CT varchar...

if it was me, I'd either send the call time in as an integer, then manipulate it internally to the function, or else immediately convert it to an integer once you get into the function.

I will change the @CT as numeric

The problem is that now the callingtime will be splited as

if calltime=500

calltime indicator
300 4
200 5
100 5
0 6

But I don't know how to do that?

Could you modify my function or something?

Thanks alot.

Well ... the function will return only the last value of indicator ... i think you will need to use a table valued function

Ooops :blush:

Very good point...I was assuming that he/she just wanted a single value...and now looking back on it, that is not the case...so yeah, I'll just back on out and defer to those who can read better'n I can

Yep, it would need a cursor and loop, or my choice also (now...), the table-returning function.