Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2004
    Posts
    33

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

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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 ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Feb 2004
    Posts
    33

    sorry about the confused

    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

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    So you mean if we have a record

    Code:
    1st     700    4
    then
    it will become
    Code:
    1     200      4
    2     100      5
    3     100      5
    4     100      5
    5     100      5
    6     100      6
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Feb 2004
    Posts
    33

    yeah

    I mean change the original table into our required table.

    But currently, I only need to identify the Indicator

    Thanks
    Last edited by leau; 05-17-04 at 13:02.

  6. #6
    Join Date
    Feb 2004
    Posts
    33
    Can I create a function to get the indicator, then use the function in Stored Procedure instead of using the "case"?

    Thanks alot

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I must say ... this looks like a job which will need a loop ... a cursor or a while loop ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Feb 2004
    Posts
    33

    my function

    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
    Last edited by leau; 05-17-04 at 14:07.

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I Think that the @ct needs to be numeric ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by leau
    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...
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  12. #12
    Join Date
    Feb 2004
    Posts
    33

    thanks

    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
    Last edited by leau; 05-17-04 at 14:16.

  13. #13
    Join Date
    Feb 2004
    Posts
    33

    to TallCowboy0614

    But I don't know how to do that?

    Could you modify my function or something?

    Thanks alot.

  14. #14
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Well ... the function will return only the last value of indicator ... i think you will need to use a table valued function
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  15. #15
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by Enigma
    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.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •