Create view eVMutiCard
be like
Select a. badge, a. name, a. display ID, a. editor ID, a. job id, a. status, a. employee type, a. reporting object,
B. identification,
N' Unreasonable ID Card Length' Remarks
From employee b
Where (lens (B. logo) is not in (15, 18).
And B. the identification is not empty)
Or B. the logo is empty.
Joint ownership
Select a. badge, a. name, a. display ID, a. editor ID, a. job id, a. status, a. employee type, a. reporting object,
B. identification,
N' ID card contains invalid characters' as comments'
From employee b
In which Len(b.Identification) is in (15, 18).
And is numeric (case len (B. identification) when 1 8 then substring (B. identification,1,17).
Else B. End of identification) = 0
Joint ownership
Select a. badge, a. name, a. display ID, a. editor ID, a. job id, a. status, a. employee type, a. reporting object,
B. identification,
N' unreasonable birth date of ID card' as a remark
From employee b
In which Len(b.Identification) is in (15, 18).
And (IsDate (when len (b.identification) =15, then'19'+substring (b.identification, 7,2)+'-'+substring (b.identification,1).
Else substring (b.Identification, 7,4)+'-'+substring (b.Identification, 1 1 2)+'-'+ substring (b.Identification,13,2).
End)=0
Or not (
(Len(b . identificati on)= 15 Then ' 19 '+Substring(b . identificati on，7，2)+'-'+Substring(b . identificati on，9，2)+'-'+Substring(b . identificati on， 1 1，2)
Else substring (b.Identification, 7,4)+'-'+substring (b.Identification, 1 1 2)+'-'+ substring (b.Identification,13,2).
End) between "1900-01-01"and "2079-06-06").
Joint ownership
Select a. badge, a. name, a. display ID, a. editor ID, a. job id, a. status, a. employee type, a. reporting object,
B. identification,
N' ID card check bit is incorrect (18 bit does not match the check)' Remarks
From employee b
Where (len (b.identification) =18.
And substring (b.Identification, 18,19) <; & gtdbo。 Getcheckidcardcode (B. logo)
And B. the identification is not empty)
Among them, according to the calculation formula stipulated by the state, dbo. /kloc-the GetCheckIDCardCode of the 0/8 digit ID check bit is calculated as follows:
Create the function getcheckidcardcode (@ sfzhchar (18)).
Returns char( 1)
be like
begin
declare @r varchar(2)
Declare @i int
if len(@ sfzh)& lt； & gt 18
set @r = 0
other
set @i = cast(substring(@sfzh， 1， 1) as int) * 7
+cast(substring(@sfzh，2， 1) as int) * 9
+cast(substring(@sfzh，3， 1) as int) * 10
+cast(substring(@sfzh，4， 1) as int) * 5
+cast(substring(@sfzh，5， 1) as int) * 8
+cast(substring(@sfzh，6， 1) as int) * 4
+cast(substring(@sfzh，7， 1) as int) * 2
+cast(substring(@sfzh，8， 1) as int) * 1
+cast(substring(@sfzh，9， 1) as int) * 6
+cast(substring(@sfzh， 10， 1) as int) * 3
+cast(substring(@sfzh， 1 1， 1) as int) * 7
+cast(substring(@sfzh， 12， 1) as int) * 9
+cast(substring(@sfzh， 13， 1) as int) * 10
+cast(substring(@sfzh， 14， 1) as int) * 5
+cast(substring(@sfzh， 15， 1) as int) * 8
+cast(substring(@sfzh， 16， 1) as int) * 4
+cast(substring(@sfzh， 17， 1) as int) * 2
set @ I = @ I-@ I/ 1 1 * 1 1
set @r = cast((case @i
When 0, then 1
When 1, it is 0.
When 2, then 1 1
When 3 to 9
When 4 and then 8
When 5 and then 7.
When 6 and then 6.
When 7 and then 5.
When 8 and then 4.
When 9 and then 3.
When 10, then 2
Else '' end) as char)
if (@r = 1 1) set @r='X '
else set @r = @r
set @r = '' + @r +' '
return @r
end