فیزتک / استخراج ضرایب با تابع در SQL SERVER
يكشنبه, ۶ بهمن ۱۴۰۴، ۰۹:۲۹ ق.ظ
شرایطی را در نظر بگیرید که برای مساحت های مختلف در یک سری بلوک بایستی ضرایبی طبق جدول زیر اعمال شود:
| <= 200 | <= 250 | <= 300 | <= 400 | <= 500 | <= 600 | > 600 | |
| 1 | 8 | 9 | 11 | 12 | 23 | 26 | 28 |
| 2 | 6.1 | 6.9 | 8.5 | 9.2 | 17.7 | 20 | 21.5 |
| 4 | 3.1 | 3.8 | 4.2 | 4.6 | 8.5 | 10 | 10.8 |
| 5 | 1 | 1.5 | 2 | 2.5 | 3 | 3.5 | 3.9 |
| 6 | 1.3 | 1.7 | 2.1 | 2.5 | 3.8 | 4.6 | 5 |
| 7 | 1 | 1.5 | 1.7 | 2 | 2.5 | 3 | 3.5 |
| 8 | 0.9 | 1.3 | 1.5 | 1.7 | 2.6 | 3 | 3.5 |
| 9 | 2.5 | 2.8 | 3 | 3.5 | 6 | 7 | 8 |
| 10 | 5.5 | 6.5 | 7 | 8 | 10 | 12 | 13 |
| 13 | 0.8 | 1.1 | 1.3 | 1.5 | 2.6 | 3 | 3.4 |
| 14 | 0.7 | 1 | 1.2 | 1.4 | 1.7 | 1.9 | 2.1 |
| 15 | 1.2 | 1.5 | 1.9 | 2.3 | 3.1 | 3.8 | 4.6 |
| 16 | 1.1 | 1.3 | 1.6 | 1.9 | 2.9 | 3.8 | 4.1 |
| 18 | 1.3 | 1.6 | 2 | 2.3 | 3.1 | 3.9 | 4.7 |
| 19 | 2.5 | 3 | 3.5 | 4 | 6.5 | 8 | 9 |
| 21 | 2.3 | 2.6 | 2.9 | 3.3 | 4.6 | 5.2 | 5.9 |
ALTER FUNCTION [dbo].[F100]
(
@P11 SMALLINT, -- کد بلوک
@C INT -- حالت های مختلف
)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE
@Band TINYINT,
@Result DECIMAL(10,2);
/* 1. Determine area band */
SET @Band =
CASE
WHEN @C = 1 THEN 1
WHEN @C = 2 THEN 2
WHEN @C = 3 THEN 3
WHEN @C = 4 THEN 4
WHEN @C = 5 THEN 5
WHEN @C = 6 THEN 6
ELSE 7
END;
/* 2. Return rate based on Band + P11 */
SET @Result =
CASE @Band
WHEN 1 THEN
CASE @P11
WHEN 1 THEN 8.00
WHEN 2 THEN 6.10
WHEN 4 THEN 3.10
WHEN 5 THEN 1.00
WHEN 6 THEN 1.30
WHEN 7 THEN 1.00
WHEN 8 THEN 0.90
WHEN 9 THEN 2.50
WHEN 10 THEN 5.50
WHEN 13 THEN 0.80
WHEN 14 THEN 0.70
WHEN 15 THEN 1.20
WHEN 16 THEN 1.10
WHEN 18 THEN 1.30
WHEN 19 THEN 2.50
ELSE 2.30
END
WHEN 2 THEN
CASE @P11
WHEN 1 THEN 9.00
WHEN 2 THEN 6.90
WHEN 4 THEN 3.80
WHEN 5 THEN 1.50
WHEN 6 THEN 1.70
WHEN 7 THEN 1.50
WHEN 8 THEN 1.30
WHEN 9 THEN 2.80
WHEN 10 THEN 6.50
WHEN 13 THEN 1.10
WHEN 14 THEN 1.00
WHEN 15 THEN 1.50
WHEN 16 THEN 1.30
WHEN 18 THEN 1.60
WHEN 19 THEN 3.00
ELSE 2.60
END
WHEN 3 THEN
CASE @P11
WHEN 1 THEN 11.00
WHEN 2 THEN 8.50
WHEN 4 THEN 4.20
WHEN 5 THEN 2.00
WHEN 6 THEN 2.10
WHEN 7 THEN 1.70
WHEN 8 THEN 1.50
WHEN 9 THEN 3.00
WHEN 10 THEN 7.00
WHEN 13 THEN 1.30
WHEN 14 THEN 1.20
WHEN 15 THEN 1.90
WHEN 16 THEN 1.60
WHEN 18 THEN 2.00
WHEN 19 THEN 3.50
ELSE 2.90
END
WHEN 4 THEN
CASE @P11
WHEN 1 THEN 12.00
WHEN 2 THEN 9.20
WHEN 4 THEN 4.60
WHEN 5 THEN 2.50
WHEN 6 THEN 2.50
WHEN 7 THEN 2.00
WHEN 8 THEN 1.70
WHEN 9 THEN 3.50
WHEN 10 THEN 8.00
WHEN 13 THEN 1.50
WHEN 14 THEN 1.40
WHEN 15 THEN 2.30
WHEN 16 THEN 1.90
WHEN 18 THEN 2.30
WHEN 19 THEN 4.00
ELSE 3.30
END
WHEN 5 THEN
CASE @P11
WHEN 1 THEN 23.00
WHEN 2 THEN 17.70
WHEN 4 THEN 8.50
WHEN 5 THEN 3.00
WHEN 6 THEN 3.80
WHEN 7 THEN 2.50
WHEN 8 THEN 2.60
WHEN 9 THEN 6.00
WHEN 10 THEN 10.00
WHEN 13 THEN 2.60
WHEN 14 THEN 1.70
WHEN 15 THEN 3.10
WHEN 16 THEN 2.90
WHEN 18 THEN 3.10
WHEN 19 THEN 6.50
ELSE 4.60
END
WHEN 6 THEN
CASE @P11
WHEN 1 THEN 26.00
WHEN 2 THEN 20.00
WHEN 4 THEN 10.00
WHEN 5 THEN 3.50
WHEN 6 THEN 4.60
WHEN 7 THEN 3.00
WHEN 8 THEN 3.00
WHEN 9 THEN 7.00
WHEN 10 THEN 12.00
WHEN 13 THEN 3.00
WHEN 14 THEN 1.90
WHEN 15 THEN 3.80
WHEN 16 THEN 3.80
WHEN 18 THEN 3.90
WHEN 19 THEN 8.00
ELSE 5.20
END
ELSE
CASE @P11
WHEN 1 THEN 28.00
WHEN 2 THEN 21.50
WHEN 4 THEN 10.80
WHEN 5 THEN 3.90
WHEN 6 THEN 5.00
WHEN 7 THEN 3.50
WHEN 8 THEN 3.50
WHEN 9 THEN 8.00
WHEN 10 THEN 13.00
WHEN 13 THEN 3.40
WHEN 14 THEN 2.10
WHEN 15 THEN 4.60
WHEN 16 THEN 4.10
WHEN 18 THEN 4.70
WHEN 19 THEN 9.00
ELSE 5.90
END
END;
RETURN @Result;
END;
