Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: Is there a better way to handle this IF..ELSE IF?

    Do I have other option beside using IF..ELSE IF? TIF

    Code:
    -- GET INFORMATION OF THE JOB
    DECLARE @JOBID						AS Char(10)
    DECLARE @VRUSERVICEHRS			AS Decimal(18,2)
    DECLARE @VRUSERVICEMIN			AS Decimal(18,2)
    DECLARE @BILLEDFLAT				AS Decimal(18,2)
    DECLARE @BILLREGRATE				AS Decimal(18,2)
    DECLARE @MIN_HRS					AS Decimal(18,2)
    
    DECLARE @COUNT_GREATER_MIN		TinyInt
    DECLARE @COUNT_LESS_MIN			TinyInt
    
    SET @VRUSERVICEMIN  = 46
    SET @BILLEDFLAT = 0
    -- PROCESS ONLY RECORDS WHERE THERE IS NO FLAT FEE
    IF @BILLEDFLAT = 0
    	BEGIN
    		IF @VRUSERVICEMIN BETWEEN 0 AND 15 
    			BEGIN
    				SET @VRUSERVICEMIN = .25
    			END
    		ELSE IF @VRUSERVICEMIN = 15
    			BEGIN
    				SET @VRUSERVICEMIN = .25
    			END
    		ELSE IF @VRUSERVICEMIN BETWEEN 15 AND 30 
    			BEGIN
    				SET @VRUSERVICEMIN = .5
    			END
    		ELSE IF @VRUSERVICEMIN = 30
    			BEGIN
    				SET @VRUSERVICEMIN = .5
    			END
    		ELSE IF @VRUSERVICEMIN BETWEEN 30 AND 45
    			BEGIN
    				SET @VRUSERVICEMIN = .75
    			END
    		ELSE IF @VRUSERVICEMIN = 45
    			BEGIN
    				SET @VRUSERVICEMIN = .75
    			END
    		ELSE IF @VRUSERVICEMIN > 45
    			BEGIN
    				SET @VRUSERVICEMIN = 1
    			END
    	END
    
    PRINT @VRUSERVICEMIN

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    try a case statement
    it is sequential logic instead of conditional (if else)

  3. #3
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    I tried it with this one but I am getting an error as follows:
    "Incorrect syntax near the keyword 'BETWEEN'."

    Code:
    	SET @VRUSERVICEMIN = 
    		CASE @VRUSERVICEMIN
    			WHEN BETWEEN 0 AND 15 THEN .25
    		END

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    PHP Code:
    SET @VRUSERVICEMIN = CASE
          
    WHEN @VRUSERVICEMIN  BETWEEN  0 AND 15 THEN .25
          WHEN 
    @VRUSERVICEMIN  BETWEEN 16 AND 30 THEN 0.5
          WHEN 
    @VRUSERVICEMIN  BETWEEN 31 AND 45 THEN .75
          WHEN 
    @VRUSERVICEMIN  BETWEEN 46 AND 60 THEN 1.0
       END 
    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...except that @VRUSERVICEMIN is defined as decimal (18,2), so you need:

    SET @VRUSERVICEMIN = CASE
    WHEN @VRUSERVICEMIN BETWEEN 0 AND 15 THEN .25
    WHEN @VRUSERVICEMIN BETWEEN 15 AND 30 THEN 0.5
    WHEN @VRUSERVICEMIN BETWEEN 30 AND 45 THEN .75
    WHEN @VRUSERVICEMIN BETWEEN 45 AND 60 THEN 1.0
    END

    which is logically equivalent to:

    SET @VRUSERVICEMIN = CASE
    WHEN @VRUSERVICEMIN >= 0 AND @VRUSERVICEMIN <= 15 THEN .25
    WHEN @VRUSERVICEMIN > 15 AND @VRUSERVICEMIN <= 30 THEN 0.5
    WHEN @VRUSERVICEMIN > 30 AND @VRUSERVICEMIN <= 45 THEN .75
    WHEN @VRUSERVICEMIN > 45 AND @VRUSERVICEMIN <= 60 THEN 1.0
    END
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    all you guys are missing the fact that

    SET @VRUSERVICEMIN = 46

    so why bother with any range tests?


    decimal(18,2)??? whoa, that can't be right for a "minutes" variable


    and what's up with @COUNT_GREATER_MIN and @COUNT_LESS_MIN being TinyInt?

    is this stored proc actually going to do counts with a cursor?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    I set the values like SET @VRUSERVICEMIN = 46 for testing purposes. That is why I have PRINT @VRUSERVICEMIN at the end of my code because I'm checking the logic of the coding if it is spitting out the right value of the @VRUSERVICEMIN when an entry is placed.

    The code is not a completed and that's is why you may not see a lot of logic behind it. It's on a testing phase and will eventually be a SPROC getting values from a VB front end.

    Thanks all.

Posting Permissions

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