Please help in SQL

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

1st 700 4

then
it will become

1 200 4
2 100 5
3 100 5
4 100 5
5 100 5
6 100 6|||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 ...|||Can I create a function to get the indicator, then use the function in Stored Procedure instead of using the "case"?

Thanks alot

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|||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.|||Well ... am running busy ... will try to work on the function tomorrow ... will that do ??|||will, seems far away from what I want.

How to return the table-valued function?|||I will also take a stab at this later this morning, but you may in the meantime want to look at books online (if you have access to it), or do an online search, for user defined functions - they can return tables or scalar values.

It actually seems just what you want, IMHO, if what you want returned is data that you will be writing into a new table (each of your call segments) or you can use the table returned from the function in another query or a cursor of it's own if you are doing something with the segments other than simply writing them out to a table after you create them.

Like I said, I'll take a stab later this morning, as I have not built a table from scratch in my table-returning functions, but have many that return a table built with a select statment instead (not what you want).|||Thanks guys.

I am doing research now. Books and on-line.

Tomorrow is ok for me

Thanks again.|||There is probably an easier (AKA fancier) way to accomplish this,

but I tested a few numbers, and think this works the way you want things to work.

It does return a table of rows formatted the way I think you want to insert them...so theoretically, you could just call the function inline like:

INSERT FinalTable dbo.getInd(@.TotalMins)

or, perhaps more in line with your purposes, the results returned by the function can be used as any "standard" table (I.e., "SELECT * from dbo.getInd" or any variation thereof).

CREATE FUNCTION getInd(@.CT int)
RETURNS @.CallSegments TABLE (segno int,
timeslice int,
indicator int)
AS
BEGIN

DECLARE @.SegNum int
SET @.SegNum = 1

IF (@.CT < 200)
BEGIN
INSERT @.CallSegments VALUES (@.SegNum, @.CT, (@.CT / 100))
SET @.SegNum = @.SegNum + 1
END
ELSE
WHILE (@.CT > 0)
BEGIN
IF ((@.CT >= 200) AND (@.SegNum = 1))
INSERT @.CallSegments VALUES (@.SegNum, 200, 4)
ELSE IF (@.CT < 200)
INSERT @.CallSegments VALUES (@.SegNum, @.CT, 6)
ELSE
BEGIN
INSERT @.CallSegments VALUES(@.SegNum, 100, 5)
SET @.SegNum = @.SegNum + 1
INSERT @.CallSegments VALUES(@.SegNum, 100, 5)
END
SET @.SegNum = @.SegNum + 1
SET @.CT = @.CT - 200
END

RETURN
END

Hopefully this, or a variation of it, can get you where you need to go.|||Thank you very much. It helps alot.sql