34,590
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('kh_main') IS NOT NULL DROP TABLE kh_main
GO
IF OBJECT_ID('kh_item') IS NOT NULL DROP TABLE kh_item
GO
--考核主要表,设置起始时间主要是为防止一年有多个考核标准
CREATE TABLE kh_main (
mainId INT IDENTITY(1,1) PRIMARY KEY,
[year] INT NOT NULL,
[beginTime] DATETIME NOT NULL,
endTime DATETIME NOT NULL
)
GO
CREATE TABLE kh_item(
itemId INT IDENTITY(1,1),
mainId INT NOT NULL,
[case] VARCHAR(300) NOT NULL,
beginPercent DECIMAL(10,2),
endPercent DECIMAL(10,2),
[funcPara] INT NOT NULL
)
INSERT INTO kh_main(
[year],
beginTime,
endTime
)
VALUES
(
2017,
'2017-01-01',
'2017-12-31 23:59:59'
)
--有2条记录我加了等于两个字(跟前后记录不同),业务上最好商定:标准可以随便变, 但起止包括或不包括端点要讲清楚而且前后必须一致!
--避免不必要的纷争, 当然, 这是小事,找领导商定下来就可以了。
INSERT INTO kh_item(mainId, [case],beginPercent,endPercent,[funcPara]) VALUES( 1,'完成率小于(等于)90%时,扣20分',0,90,1)
INSERT INTO kh_item(mainId, [case],beginPercent,endPercent,[funcPara]) VALUES( 1,'大于90%小于等于100%时,每减少1%扣1分',10,100,2)
INSERT INTO kh_item(mainId, [case],beginPercent,endPercent,[funcPara]) VALUES( 1,'大于100%小于(等于)110%时,每增加1%奖1分',100,110,3)
INSERT INTO kh_item(mainId, [case],beginPercent,endPercent,[funcPara]) VALUES( 1,'大于等于110%时,每增加1%奖2分',110,9999999,4)
GO
IF OBJECT_ID('Fun_Kh') IS NOT NULL DROP FUNCTION Fun_kh
GO
-- =============================================
-- Author: yenange
-- Create date: 2017-12-02
-- Description: 考核函数
-- =============================================
CREATE FUNCTION Fun_kh(
@percent INT,
@funPara INT
)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @r DECIMAL(10,2)
IF @funPara=1
BEGIN
SET @r=@percent-20
END
ELSE IF @funPara=2
BEGIN
SET @r=@percent-(@percent-90)
END
ELSE IF @funPara=3
BEGIN
SET @r=@percent+(@percent-100)
END
ELSE IF @funPara=4
BEGIN
SET @r=@percent+(@percent-110)
END
RETURN @r
END
GO
--某个项目是 2017 年度内的, 完成率为 110%
DECLARE @year INT,@percent INT
SELECT @year=2017,@percent=105
SELECT b.funcPara,dbo.FUN_kh(@percent,b.funcPara) AS r
FROM kh_main AS a
INNER JOIN kh_item AS b ON a.mainId=b.mainId AND @percent>b.beginPercent AND @percent<=b.endPercent
WHERE [year]=2017
USE tempdb
GO
IF OBJECT_ID('kh_main') IS NOT NULL DROP TABLE kh_main
GO
IF OBJECT_ID('kh_item') IS NOT NULL DROP TABLE kh_item
GO
--考核主要表,设置起始时间主要是为防止一年有多个考核标准
CREATE TABLE kh_main (
mainId INT IDENTITY(1,1) PRIMARY KEY,
[year] INT NOT NULL,
[beginTime] DATETIME NOT NULL,
endTime DATETIME NOT NULL
)
GO
CREATE TABLE kh_item(
itemId INT IDENTITY(1,1),
mainId INT NOT NULL,
[case] VARCHAR(300) NOT NULL,
changeScore INT NOT NULL
)
供参考吧