Situatie
Avem nevoie uneori, in rapoarte, de varsta clientului la data curenta sau la o anumita data.
Si aici sunt doua situatii: avem nevoie de varsta in ani impliniti sau de varsta exacta, adica ani si zile.
Ca atare am facut o functie ca are trei parametri:
primul este cnp-ul
al doilea este data la care va fi calculata varsta
al treilea este un flag care specifica tipul calcului: pt 0 se calculeaza anii impliniti iar pt 1 se calculeaza varsta exacta in ani si zile.
Solutie
create function [dbo].[BDGetVarstaCNP](@CNP varchar(13), @DataAzi datetime, @Type tinyint = 0) –0 ani impliniti, 1 varsta calculata exact la data
returns varchar(max)
as
begin
declare
@Varsta int,
@VarstaChar varchar(max),
@TipCNP tinyint,
@Anul int
select @TipCNP = convert(tinyint, substring(@CNP, 1, 1)), @Anul = convert(int, substring(@CNP, 2, 2))
if(@TipCNP in (1, 2))
select @Anul = 1900 + @Anul
else
select @Anul = 2000 + @Anul
if(@Type = 0)
begin
select @Varsta = datepart(year, @DataAzi) – @Anul
select @VarstaChar = convert (varchar(max), @Varsta)+ ‘ ani’
end
if(@Type = 1)
begin
DECLARE @datan datetime
DECLARE @Sex int
DECLARE @An varchar(10)
DECLARE @Luna varchar(10)
DECLARE @Zi varchar(10)
select @Sex = substring(@CNP, 1, 1),
@An = substring(@CNP, 2, 2),
@Luna = substring(@CNP, 4, 2),
@Zi = substring(@CNP, 6, 2)
if @Sex in (1,2,7)
begin
set @An = ’19’+ @An
end
else
begin
set @An = ’20’+@An
end
–select @Sex, @An, @Luna, @Zi
select @datan = convert(datetime, @An+@Luna+@Zi, 112)
declare @VarstaZile int
declare @VarstaAni int
declare @UltimaAniversareVarchar varchar(max)
declare @UltimaAniversareDatetime datetime
select @VarstaAni = datepart(year, @DataAzi) – @Anul
if month(@DataAzi)+day(@DataAzi) > month(@datan)+day(@datan)
set @VarstaAni = @VarstaAni+1
select @UltimaAniversareVarchar = replace ( convert(varchar(max), @datan, 112), left(convert(varchar(max),@datan,112),4), year(@DataAzi) )
select @UltimaAniversareDatetime = convert (datetime, @UltimaAniversareVarchar)
select @VarstaZile = datediff (dd, @UltimaAniversareDatetime, @DataAzi )
if @VarstaZile < 0 or @VarstaZile > 365
begin
select @UltimaAniversareDatetime = dateadd(yy, -1, @UltimaAniversareDatetime )
–select @UltimaAniversareDatetime = convert (datetime, @UltimaAniversareVarchar)
select @VarstaZile = datediff (dd, @UltimaAniversareDatetime, @DataAzi )
end
select @VarstaChar = CONVERT(VARCHAR(MAX),@VarstaAni)+’ ani si ‘+CONVERT(VARCHAR(MAX),@VarstaZile)+’ zile’
end
return @VarstaChar
end
Leave A Comment?