RankTable

出自ProgWiki

目錄 1 用途 2 Table定義 3 GetRankTable 4 CalcRankValue

用途

• 級距表，常用於以範圍區間取其計算用的係數。

Table定義

欄位名稱	資料類型	用途
ID	INT	唯一編號(管理級距表時使用)
ClassID	INT	級距表類型編號, 以方便多張級距表放於同一個Table之類, 以便集中管理
RankDown	DECIMAL(18, 0)	級距下限
RankUp	DECIMAL(18, 0)	級距上限
RankValue	DECIMAL(18, 6)	級距值(用來放要計算的比例值)
RankDiff	DECIMAL(18, 0)	累進差額 (如果不計算累進差額者, 此欄位值保持0)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RankTable](
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[ClassID] [INT] NOT NULL,
	[RankDown] [DECIMAL](18, 0) NOT NULL,
	[RankUp] [DECIMAL](18, 0) NOT NULL,
	[RankValue] [DECIMAL](18, 6) NOT NULL
　　　　[RankDiff] [DECIMAL](18, 0) NOT NULL,
) ON [PRIMARY]

GetRankTable

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[GetRankTable]
(
	@ClassID AS INT
)
AS
BEGIN
	SET NOCOUNT ON;
 
	SELECT * FROM RankTable
		WHERE ClassID = @ClassID;
		ORDER BY RankDown;
END
GO

CalcRankValue

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION [dbo].[CalcRankValue]
(
	@ClassID AS INT
	,@InValue AS DECIMAL(18, 0)
)
RETURNS DECIMAL(18, 0)
AS
BEGIN
	DECLARE @ResultVar AS DECIMAL(18, 0)
	SET @ResultVar = 0
 
	SELECT @ResultVar = @InValue * [RankValue] - [RankDiff]
		FROM RankTable
		WHERE ClassID = @ClassID
		And RankDown >= @InValue
		And RankUp < @InValue;
 
	RETURN @ResultVar
END
GO