SQLServer2005中如何设计个税计算因为我用到了9.0版本的一些特点,所以指明了在SQLServer2005中设计.
各位提供的可以不限定这个.设计要求:1、应提供以下信息:
收入额,起征点,应纳税所得额,税率,速算扣除额,个税,税后收入额2、应能计算不同时期的个税
如收入为5606.56元,
在2007年的个税交纳情形如下:
5606.56,1600.00,4006.56,15%,125.00,475.98,5130.58
在2005年的个税交纳情形如下:
5606.56,1000.00,4606.56,15%,125.00,565.98,5040.58最后我提供我的方法。我的方法向来是莫名其妙的。对数据库我是不懂的,稍为能用一下,可怜。
这里能给300分,不知是否可以移到SQLServer版块去。
各位提供的可以不限定这个.设计要求:1、应提供以下信息:
收入额,起征点,应纳税所得额,税率,速算扣除额,个税,税后收入额2、应能计算不同时期的个税
如收入为5606.56元,
在2007年的个税交纳情形如下:
5606.56,1600.00,4006.56,15%,125.00,475.98,5130.58
在2005年的个税交纳情形如下:
5606.56,1000.00,4606.56,15%,125.00,565.98,5040.58最后我提供我的方法。我的方法向来是莫名其妙的。对数据库我是不懂的,稍为能用一下,可怜。
这里能给300分,不知是否可以移到SQLServer版块去。
解决方案 »
- 关于棋牌类数据库表设计
- 求一个SQL更新语句
- 两个不同时间的数据库文件(MDF,LDF都有)能不能恢复到他们中间的时间点????????
- 大家帮我看看这个数据库还原的问题。
- sqlserver删除用户映射的数据库权限后,该用户还可以访问
- 问问关于update的问题(自动分配数量)
- 数据库问题
- 关于一个模糊查询的问题
- 紧急求救!!sql server 70
- db2客户连接速度非常慢如何解决
- win98下用必须用MSDE的实际服务器名字,如“王丹可\YH”而不能用等同的"127.0.0.1\YH"或"localhost\yh"代替,是什么原因?而实际的数据库名“王丹可\YH”又如何直接得到?
- SQL2005如何建立链接服务器
然后select即可.
我来测试40年user的收入,用随机数,不超过10000元
--测试环境
set nocount on
create table aa(yearA int ,yearZ int ,startMoney int,MoneyA int ,moneyZ int,percents int,fix int)
insert into aa select 0,2000,800,0,500,5,0
insert into aa select 0,2000,800,500,1500,10,100
insert into aa select 0,2000,800,1500,999999999,20,200
insert into aa select 2000,2010,1000,0,500,7,0
insert into aa select 2000,2010,1000,500,1500,12,100
insert into aa select 2000,2010,1000,1500,2500,20,100
insert into aa select 2000,2010,1000,2500,999999999,30,100
insert into aa select 2010,9999,1600,0,1500,12,0
insert into aa select 2010,9999,1600,1500,3000,22,120
insert into aa select 2010,9999,1600,3000,999999999,33,240create table bb(name varchar(4),salary money,date datetime)
declare @start datetime
set @start='1980-01-01'
while @start<='2020-01-01'
begin
insert into bb
select 'User',rand(checksum(newid()))*10000,@start
set @start=@start+1
end
--计算
select name,sum(salary) as 总收入,sum(cc) as 总税 from(
select bb.name,bb.salary, (bb.salary-aa.startmoney)*aa.percents/100-fix as cc,bb.date
from bb,aa where year(bb.date)>=aa.yearA and year(bb.date)<aa.yearZ
and bb.salary-aa.startmoney>=aa.moneya and bb.salary-aa.startmoney<aa.moneyZ
and bb.name='user')a
group by name select top 20 bb.name,bb.salary as 收入, (bb.salary-aa.startmoney)*aa.percents/100-fix as 税,bb.date
from bb,aa where year(bb.date)>=aa.yearA and year(bb.date)<aa.yearZ
and bb.salary-aa.startmoney>=aa.moneya and bb.salary-aa.startmoney<aa.moneyZ
and bb.name='user'--处理垃圾
drop table aa
drop table bbset nocount off
--结果/*
name 总收入 总税
---- --------------------- ---------------------
User 72056633.9871 12341497.6068name 收入 税 date
---- --------------------- --------------------- -----------------------
User 1874.3255 7.4325 1980-01-01 00:00:00.000
User 3143.3526 268.6705 1980-01-02 00:00:00.000
User 6015.1843 843.0368 1980-01-03 00:00:00.000
User 1225.5757 21.2787 1980-01-04 00:00:00.000
User 3035.2929 247.0585 1980-01-05 00:00:00.000
User 9040.0164 1448.0032 1980-01-08 00:00:00.000
User 9857.5627 1611.5125 1980-01-09 00:00:00.000
User 9643.491 1568.6982 1980-01-10 00:00:00.000
User 2745.9648 189.1929 1980-01-11 00:00:00.000
User 6418.6444 923.7288 1980-01-12 00:00:00.000
User 2529.2835 145.8567 1980-01-14 00:00:00.000
User 7982.8776 1236.5755 1980-01-15 00:00:00.000
User 8888.3945 1417.6789 1980-01-16 00:00:00.000
User 8547.8842 1349.5768 1980-01-17 00:00:00.000
User 2208.681 40.8681 1980-01-18 00:00:00.000
User 3591.0227 358.2045 1980-01-19 00:00:00.000
User 2420.2334 124.0466 1980-01-20 00:00:00.000
User 7074.359 1054.8718 1980-01-21 00:00:00.000
User 4092.4753 458.495 1980-01-22 00:00:00.000
User 3416.9378 323.3875 1980-01-23 00:00:00.000*/
自2006年1月1日起,起征点改为1600。期间,相应的“适用税率”、“速算扣除额”这些数值并没改变。
SELECT a.*,b.* FROM income a
OUTER APPLY [dbo].[个税情况] (convert(char(8),a.[date],112),a.salary) b/*
name salary date 收入额 起征点 应纳税所得额 税率 速算扣除额 个税 税后收入额
---- --------------------- ----------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---- --------------------------------------- --------------------------------------- ---------------------------------------
B 13671.8585 1980-06-18 00:00:00.000 13671.86 NULL 0.00 0 0.00 0.00 13671.86
A 68003.9912 1980-07-24 00:00:00.000 68003.99 NULL 0.00 0 0.00 0.00 68003.99
B 30875.0575 1986-01-28 00:00:00.000 30875.06 1000.00 29875.06 25% 1375.00 6093.77 24781.29
A 971.8038 1997-01-23 00:00:00.000 971.80 1000.00 0.00 0 0.00 0.00 971.80
B 9749.4336 2009-12-26 00:00:00.000 9749.43 1600.00 8149.43 20% 375.00 1254.89 8494.54
B 52227.0272 2010-07-19 00:00:00.000 52227.03 1600.00 50627.03 30% 3375.00 11813.11 40413.92
A 57714.4594 2013-01-01 00:00:00.000 57714.46 1600.00 56114.46 30% 3375.00 13459.34 44255.12
A 52558.3045 2016-08-05 00:00:00.000 52558.30 1600.00 50958.30 30% 3375.00 11912.49 40645.81
A 12772.6305 2019-11-07 00:00:00.000 12772.63 1600.00 11172.63 20% 375.00 1859.53 10913.10
B 41.7807 2019-11-12 00:00:00.000 41.78 1600.00 0.00 0 0.00 0.00 41.78
A 48693.8515 2019-12-18 00:00:00.000 48693.85 1600.00 47093.85 30% 3375.00 10753.16 37940.69
B 11695.3468 2019-12-23 00:00:00.000 11695.35 1600.00 10095.35 20% 375.00 1644.07 10051.28*/
set nocount on
--不需要每个月保存字典,保存时间段就可以了,这里用年份作为段,实际可能会是月份
--起始年份,结束年份,起征点,税率段小,税率段大,税率,扣除数
create table aa(yearA int ,yearZ int ,startMoney int,MoneyA int ,moneyZ int,percents int,fix int)
insert into aa select 0,2000,800,0,500,5,0
insert into aa select 0,2000,800,500,1500,10,0
insert into aa select 0,2000,800,1500,999999999,20,200
insert into aa select 2000,2010,1000,0,500,7,0
insert into aa select 2000,2010,1000,500,1500,12,0
insert into aa select 2000,2010,1000,1500,2500,20,100
insert into aa select 2000,2010,1000,2500,999999999,30,100
insert into aa select 2010,9999,1600,0,1500,12,0
insert into aa select 2010,9999,1600,1500,3000,22,0
insert into aa select 2010,9999,1600,3000,999999999,33,240--收入记录
create table bb(name varchar(4),salary money,date datetime)
declare @start datetime
set @start='1980-01-01'
while @start<'2020-01-01'
begin
insert into bb
select 'User',rand(checksum(newid()))*10000,@start
set @start=dateadd(mm,1,@start)
end
--
--
----计算,公式应该是对的
select name,sum(salary) as 总收入,sum(cc) as 总税 from(
select bb.name,bb.salary, (bb.salary-aa.startmoney)*aa.percents/100-fix as cc,bb.date
from bb,aa where year(bb.date)>=aa.yearA and year(bb.date)<aa.yearZ
and bb.salary-aa.startmoney>=aa.moneya and bb.salary-aa.startmoney<aa.moneyZ
and bb.name='user')a
group by name select top 20 bb.name,bb.salary as 收入, (bb.salary-aa.startmoney)*aa.percents/100-fix as 税,bb.date
from bb,aa where year(bb.date)>=aa.yearA and year(bb.date)<aa.yearZ
and bb.salary-aa.startmoney>=aa.moneya and bb.salary-aa.startmoney<aa.moneyZ
and bb.name='user'--处理垃圾
drop table aa
drop table bbset nocount off
--结果/*
name 总收入 总税
---- --------------------- ---------------------
User 2445093.9087 425766.4913name 收入 税 date
---- --------------------- --------------------- -----------------------
User 1992.7044 119.2704 1980-01-01 00:00:00.000
User 6904.0098 1020.8019 1980-02-01 00:00:00.000
User 3029.1397 245.8279 1980-03-01 00:00:00.000
User 9658.3692 1571.6738 1980-04-01 00:00:00.000
User 2299.5095 149.9509 1980-05-01 00:00:00.000
User 8537.5257 1347.5051 1980-06-01 00:00:00.000
User 1020.382 11.0191 1980-07-01 00:00:00.000
User 7323.4345 1104.6869 1980-08-01 00:00:00.000
User 4485.2925 537.0585 1980-09-01 00:00:00.000
User 4088.1062 457.6212 1980-10-01 00:00:00.000
User 3940.8401 428.168 1980-11-01 00:00:00.000
User 1574.2895 77.4289 1980-12-01 00:00:00.000
User 5071.0671 654.2134 1981-01-01 00:00:00.000
User 3350.0764 310.0152 1981-02-01 00:00:00.000
User 4970.7551 634.151 1981-03-01 00:00:00.000
User 3697.0909 379.4181 1981-04-01 00:00:00.000
User 9747.5192 1589.5038 1981-05-01 00:00:00.000
User 8530.8469 1346.1693 1981-06-01 00:00:00.000
User 5965.9306 833.1861 1981-07-01 00:00:00.000
User 6796.8839 999.3767 1981-08-01 00:00:00.000
*/
USE [IncomeTaxWorks]
GO
/****** 对象: UserDefinedFunction [dbo].[个税情况] 脚本日期: 12/06/2007 20:55:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[个税情况]
(
@当前时间 [dbo].[LDate] = N'Current'
,@收入额 numeric(18, 2)
)
RETURNS
@个税情况表 TABLE
(
收入额 numeric(18, 2)
,起征点 numeric(18, 2)
,应纳税所得额 numeric(18, 2)
,税率 nvarchar(4)
,速算扣除额 numeric(18, 2)
,个税 numeric(18, 2)
,税后收入额 numeric(18, 2)
)
AS
BEGIN
DECLARE @起征点 numeric(18, 2)
,@应纳税所得额 numeric(18, 2)
,@税率 nvarchar(4)
,@速算扣除额 numeric(18, 2)
,@个税 numeric(18, 2)
,@税后收入额 numeric(18, 2)
,@起征点ID smallint
,@基数 numeric(18, 2) SELECT @起征点 = a.[基数]
,@起征点ID = a.[个税计算参数ID]
FROM [dbo].[F_个税计算参数值] (@当前时间) a
INNER JOIN [dbo].[个税计算参数] b
ON a.[个税计算参数ID] = b.[参数ID]
WHERE b.[参数] = N'起征点' IF (@收入额 - @起征点) > 0
BEGIN
SET @应纳税所得额 = @收入额 - @起征点 SELECT @基数 = MAX(DISTINCT [基数])
FROM [dbo].[F_个税计算参数值] (@当前时间) a
INNER JOIN [dbo].[个税计算参数] b
ON a.[个税计算参数ID] = b.[参数ID]
WHERE a.[基数] <= @应纳税所得额 AND a.[个税计算参数ID] <> @起征点ID SELECT @速算扣除额 = a.[速算扣除额]
,@税率 = b.[参数]
FROM [dbo].[F_个税计算参数值] (@当前时间) a
INNER JOIN [dbo].[个税计算参数] b
ON a.[个税计算参数ID] = b.[参数ID]
WHERE a.[基数] = @基数 AND a.[个税计算参数ID] <> @起征点ID SET @个税 = @应纳税所得额 * (CAST(LEFT(@税率,LEN(@税率) - 1) AS numeric(18, 2)) / 100) - @速算扣除额
SET @税后收入额 = @收入额 - @个税
END
ELSE
BEGIN
SET @应纳税所得额 = 0
SET @速算扣除额 = 0
SET @税率 = '0'
SET @个税 = 0
SET @税后收入额 = @收入额
END INSERT INTO @个税情况表
VALUES(
@收入额
,@起征点
,@应纳税所得额
,@税率
,@速算扣除额
,@个税
,@税后收入额
)
RETURN
END
GO
/****** 对象: UserDefinedFunction [dbo].[F_个税计算参数值] 脚本日期: 12/06/2007 20:58:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[F_个税计算参数值]
(
@当前时间 [dbo].[LDate] = N'Current'
)
RETURNS TABLE
AS
RETURN
(
SELECT a.*
FROM [dbo].[个税计算参数值] a
RIGHT OUTER JOIN
(
SELECT [个税计算参数ID]
,MIN(DISTINCT [截止日期])AS 查询时间
FROM [dbo].[个税计算参数值]
WHERE @当前时间 BETWEEN [起始日期] AND [截止日期]
GROUP BY [个税计算参数ID]
) b
ON a.[个税计算参数ID]= b.[个税计算参数ID] AND a.截止日期 = b.查询时间
)
USE [IncomeTaxWorks]
GO
/****** 对象: Default [dbo].[LDateDefault] 脚本日期: 12/06/2007 20:59:57 ******/
CREATE DEFAULT [dbo].[LDateDefault] AS N'Current'
GO
/****** 对象: UserDefinedDataType [dbo].[LDate] 脚本日期: 12/06/2007 21:00:04 ******/
CREATE TYPE [dbo].[LDate] FROM [nvarchar](19) NOT NULL
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[LDateDefault]', @objname=N'[dbo].[LDate]' , @futureonly='futureonly'
USE [IncomeTaxWorks]
GO
/****** 对象: Table [dbo].[个税计算参数] 脚本日期: 12/06/2007 21:15:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[个税计算参数](
[参数ID] [smallint] IDENTITY(0,1) NOT NULL,
[参数] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
CONSTRAINT [PK_个税计算参数] PRIMARY KEY CLUSTERED
(
[参数ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_个税计算参数] UNIQUE NONCLUSTERED
(
[参数] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]USE [IncomeTaxWorks]
GO
/****** 对象: Table [dbo].[个税计算参数值] 脚本日期: 12/06/2007 21:16:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[个税计算参数值](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[个税计算参数ID] [smallint] NOT NULL,
[基数] [smallmoney] NULL,
[速算扣除额] [smallmoney] NOT NULL,
[起始税额] [smallmoney] NULL,
[结束税额] [smallmoney] NULL,
[截止日期] [dbo].[LDate] NOT NULL,
[起始日期] [dbo].[LDate] NOT NULL,
CONSTRAINT [PK_个税计算参数值] PRIMARY KEY CLUSTERED
(
[个税计算参数ID] ASC,
[截止日期] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
EXEC sys.sp_bindefault @defname=N'[dbo].[LDateDefault]', @objname=N'[dbo].[个税计算参数值].[截止日期]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[LDateDefault]', @objname=N'[dbo].[个税计算参数值].[起始日期]' , @futureonly='futureonly'
GO
ALTER TABLE [dbo].[个税计算参数值] WITH CHECK ADD CONSTRAINT [FK_个税计算参数值_个税计算参数] FOREIGN KEY([个税计算参数ID])
REFERENCES [dbo].[个税计算参数] ([参数ID])
GO
ALTER TABLE [dbo].[个税计算参数值] CHECK CONSTRAINT [FK_个税计算参数值_个税计算参数]
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数]([参数])VALUES(N'5%')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数]([参数])VALUES(N'10%')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数]([参数])VALUES(N'15%')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数]([参数])VALUES(N'20%')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数]([参数])VALUES(N'25%')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数]([参数])VALUES(N'30%')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数]([参数])VALUES(N'35%')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数]([参数])VALUES(N'40%')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数]([参数])VALUES(N'45%')
([个税计算参数ID]
,[基数]
,[速算扣除额]
,[起始税额]
,[结束税额]
,[截止日期]
,[起始日期])
VALUES
(0
,1000
,0
,0
,0
,N'20051231'
,N'19800901')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数值]
([个税计算参数ID]
,[基数]
,[速算扣除额]
,[起始税额]
,[结束税额]
,[截止日期]
,[起始日期])
VALUES
(1
,0
,0
,0
,25
,N'Current'
,N'19800901')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数值]
([个税计算参数ID]
,[基数]
,[速算扣除额]
,[起始税额]
,[结束税额]
,[截止日期]
,[起始日期])
VALUES
(2
,500
,25
,25
,175
,N'Current'
,N'19800901')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数值]
([个税计算参数ID]
,[基数]
,[速算扣除额]
,[起始税额]
,[结束税额]
,[截止日期]
,[起始日期])
VALUES
(3
,2000
,125
,175
,625
,N'Current'
,N'19800901')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数值]
([个税计算参数ID]
,[基数]
,[速算扣除额]
,[起始税额]
,[结束税额]
,[截止日期]
,[起始日期])
VALUES
(4
,5000
,375
,625
,3625
,N'Current'
,N'19800901')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数值]
([个税计算参数ID]
,[基数]
,[速算扣除额]
,[起始税额]
,[结束税额]
,[截止日期]
,[起始日期])
VALUES
(5
,20000
,1375
,3625
,8625
,N'Current'
,N'19800901')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数值]
([个税计算参数ID]
,[基数]
,[速算扣除额]
,[起始税额]
,[结束税额]
,[截止日期]
,[起始日期])
VALUES
(6
,40000
,3375
,8625
,14625
,N'Current'
,N'19800901')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数值]
([个税计算参数ID]
,[基数]
,[速算扣除额]
,[起始税额]
,[结束税额]
,[截止日期]
,[起始日期])
VALUES
(7
,60000
,6375
,14625
,21625
,N'Current'
,N'19800901')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数值]
([个税计算参数ID]
,[基数]
,[速算扣除额]
,[起始税额]
,[结束税额]
,[截止日期]
,[起始日期])
VALUES
(8
,80000
,10375
,21625
,29625
,N'Current'
,N'19800901')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数值]
([个税计算参数ID]
,[基数]
,[速算扣除额]
,[起始税额]
,[结束税额]
,[截止日期]
,[起始日期])
VALUES
(9
,100000
,15375
,29625
,214748
,N'Current'
,N'19800901')
INSERT INTO [IncomeTaxWorks].[dbo].[个税计算参数值]
([个税计算参数ID]
,[基数]
,[速算扣除额]
,[起始税额]
,[结束税额]
,[截止日期]
,[起始日期])
VALUES
(0
,1600
,0
,0
,0
,N'Current'
,N'20060101')
包含年份,税种,起证点,结束点,税率,...等你需要的字段.然后一个select就搞定.再说一次,给出结构\数据\算法和结果,肯定帮你搞定.如果是空谈,难也.
GO
CREATE FUNCTION [Helper].[IDCard]
(
@Card varchar(18)
)
RETURNS
@TCard TABLE
(
Input varchar(18)
,IDCard varchar(18)
,Valid bit
)
AS
BEGIN
DECLARE @IDCard as varchar(18)
,@Valid as bit
,@Input as varchar(18) DECLARE @Length as smallint
,@TmpCard as varchar(18)
,@IsOld as bit SET @Valid = 0
SET @IDCard = ''
SET @Input = '' IF @Card IS NULL GOTO Finish SET @Input = LTRIM(RTRIM(@Card)) /*去空格*/
SET @Length = LEN(@Input) IF NOT @Length IN (15, 18) GOTO Finish /*非15、18位*/ IF @Length = 15
BEGIN
IF ISNUMERIC(@Input) = 0 GOTO Finish /*非数字*/
SET @TmpCard = LEFT(@Input, 6) + '19' + RIGHT(@input, 9) /*补充为17位*/
SET @IsOld = 1
END
ELSE
BEGIN
IF ISNUMERIC(Left(@Input, 17)) = 0 GOTO Finish /*非数字*/
SET @TmpCard = LEFT(@Input, 17) /*取前17位*/
SET @IsOld = 0
END DECLARE @Birthday varchar(8)
SET @Birthday = SUBSTRING(@TmpCard, 7, 8)
IF ISDATE(@birthday) = 0 GOTO Finish /*非日期*/ --前17位数与相应加权因子的积的和
DECLARE @Sum as smallint
,@WI as tinyint
,@Index as tinyint
,@Num as tinyint SET @Sum = 0
SET @Index = 1 WHILE @Index < 18
BEGIN
SET @Num = CAST(SUBSTRING(@TmpCard, @Index, 1) AS tinyint) SELECT @WI =
CASE @Index
WHEN 1 THEN 7
WHEN 2 THEN 9
WHEN 3 THEN 10
WHEN 4 THEN 5
WHEN 5 THEN 8
WHEN 6 THEN 4
WHEN 7 THEN 2
WHEN 8 THEN 1
WHEN 9 THEN 6
WHEN 10 THEN 3
WHEN 11 THEN 7
WHEN 12 THEN 9
WHEN 13 THEN 10
WHEN 14 THEN 5
WHEN 15 THEN 8
WHEN 16 THEN 4
WHEN 17 THEN 2
END SET @Sum = @Sum + @Num * @WI
SET @Index = @Index + 1
END --模11
DECLARE @Mod as tinyint
SET @Mod = @Sum % 11 --校验码
DECLARE @Parity as varchar(1)
SELECT @Parity =
CASE @Mod
WHEN 0 THEN '1'
WHEN 1 THEN '0'
WHEN 2 THEN 'X'
WHEN 3 THEN '9'
WHEN 4 THEN '8'
WHEN 5 THEN '7'
WHEN 6 THEN '6'
WHEN 7 THEN '5'
WHEN 8 THEN '4'
WHEN 9 THEN '3'
WHEN 10 THEN '2'
END --完整的18位身份证号码
SET @TmpCard = @TmpCard + @Parity IF @IsOld = 1
SET @Valid = 1
ELSE
IF @Parity = RIGHT(@Input, 1) /*校验*/
SET @Valid = 1
ELSE
SET @Valid = 0 --无论正确与否,都给出有效身份证号码
SET @IDCard = @tmpCardFinish:
INSERT INTO @TCard VALUES(@Input, @IDCard, @Valid)
RETURN
END
,[身份证号]
,b.*
FROM [EmployeeWorks].[Base].[职员]
CROSS APPLY [LzmTWWorks].[Helper].[IDCard](身份证号) b
WHERE NOT [身份证号] IS NULL AND Valid = 0
呵,当初制作身份证时,人眼加工,还真会漏误。如果象钱币的话,这个身份证该值得收藏家当宝了。
UPDATE [LzmtwDb].[IncomeTax].[TaxInfo]
SET Last ='20080229'
WHERE [Last] = 'Current' AND NameID = 0INSERT INTO [LzmtwDb].[IncomeTax].[TaxInfo]
(NameID, Last, First, 基数)
VALUES(0, 'Current', '20080301', 2000)再对比一下SELECT * FROM [LzmtwDb].[Helper].[IncomeTax] (
'20080229'
,5000)
UNION ALL
SELECT * FROM [LzmtwDb].[Helper].[IncomeTax] (
'20080301'
,5000/*
收入额 起征点 应纳税所得额 税率 速算扣除额 个税 税后收入额
--------------------------------------- --------------------------------------- --------------------------------------- ---- --------------------------------------- --------------------------------------- ---------------------------------------
5000.00 1600.00 3400.00 15% 125.00 385.00 4615.00
5000.00 2000.00 3000.00 15% 125.00 325.00 4675.00(2 行受影响)
*/