Calculate two internships

,

Calculates total length of services for a employee. Calculates( addition or subtraction) two internships in form of yy.mm.dd.

The script works by SQL Server 2005+ versions

SELECT

 [dbo].CalcTwoInternShips('3.11.10', '17.4.12', 0, 1)

--21.3.22

SELECT

 [dbo].CalcTwoInternShips('21.3.22', '17.4.12', 0, 0)

--3.11.10

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID('[dbo].[CalcTwoInternShips]') IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[CalcTwoInternShips];
END;
GO
/*********************************************************************************************
Calculates total length of services for a employee. Calculates( addition or subtraction) two internships in form of yy.mm.dd.
The script works by SQL Server 2005+ versions
2016 Darko Martinović

Feedback: mailto:darko.martinovic@outlook.com

License: Free
Examples :
--Addition of two internships
SELECT
[dbo].[CalcTwoInternShips]('3.11.10', '17.4.12',0,1)
--Return 21.3.22

--subtraction of two internships
SELECT [dbo].[CalcTwoInternShips]('21.3.22', '17.4.12', 0, 0)
--Return 3.11.10
*********************************************************************************************/CREATE FUNCTION [dbo].[CalcTwoInternShips]
( 
@dwstart nvarchar(8), --first intership in form of yy.mm.dd
@dwend nvarchar(8), --second intership in form of yy.mm.dd
@treat11m30dAsWholeYear bit, --0 or 1
@addition AS bit -- 1-for addition 0-for subtraction
)
RETURNS nvarchar(8)
BEGIN
--Check if arguments are passed correctly
DECLARE @firstDay AS int;
DECLARE @maxWorkgingYears AS int;

DECLARE @firstMonth AS int;
DECLARE @firstYear AS int;
 
--
DECLARE @secondDay AS int;
DECLARE @secondMonth AS int;
DECLARE @secondYear AS int;


DECLARE @secondCopyDay AS int;
DECLARE @secondCopyMonth AS int;
DECLARE @secondCopyYear AS int;
 


--Return values
DECLARE @yy AS int;
DECLARE @mm AS int;
DECLARE @dd AS int;

DECLARE @maxDatesPerMonth AS int;
SET @yy = 0;
SET @mm = 0;
SET @dd = 0;
SET @maxDatesPerMonth = CASE
WHEN @treat11m30dAsWholeYear = 1 THEN 29
ELSE 30
END;
--You can limit maximum working years on your own
SET @maxWorkgingYears = 40; 

SET @firstDay = PARSENAME(@dwStart, 1);
SET @firstMonth = PARSENAME(@dwStart, 2);
SET @firstYear = PARSENAME(@dwStart, 3);
--
SET @secondDay = PARSENAME(@dwEnd, 1);
SET @secondMonth = PARSENAME(@dwEnd, 2);
SET @secondYear = PARSENAME(@dwEnd, 3);


IF ISNUMERIC(@firstDay) + ISNUMERIC(@firstMonth) + ISNUMERIC(@firstYear) + ISNUMERIC(@secondDay) + ISNUMERIC(@secondMonth) + ISNUMERIC(@secondYear) != 6
BEGIN
RETURN NULL
END;
--Check if year is correctly formatted
IF @firstYear NOT BETWEEN 0 AND @maxWorkgingYears OR 
   @secondYear NOT BETWEEN 0 AND @maxWorkgingYears
BEGIN
RETURN NULL
END;
--Check if month is correctly formatted
IF @firstMonth NOT BETWEEN 0 AND 11 OR 
   @secondMonth NOT BETWEEN 0 AND 11
BEGIN
RETURN NULL
END;
--Check if day is correctly formatted
IF @firstDay NOT BETWEEN 0 AND @maxDatesPerMonth OR 
   @secondDay NOT BETWEEN 0 AND @maxDatesPerMonth
BEGIN
RETURN NULL
END;

--Check addition or subtraction
SET @secondCopyDay = ( CASE
   WHEN @addition = 1 THEN 1
   ELSE-1
   END ) * @secondDay;
SET @secondCopyMonth = ( CASE
 WHEN @addition = 1 THEN 1
 ELSE-1
 END ) * @secondMonth;
SET @secondCopyYear = ( CASE
WHEN @addition = 1 THEN 1
ELSE-1
END ) * @secondYear;

SET @dd = @firstDay + @secondCopyDay;
IF @dd > @maxDatesPerMonth
BEGIN
SET @dd = @dd - @maxDatesPerMonth;
SET @secondCopyMonth = @secondCopyMonth + 1;
IF @secondCopyMonth > 12
BEGIN
SET @secondCopyMonth = @secondCopyMonth - 12;
SET @secondCopyYear = @secondCopyYear + 1;
END;

END;

IF @dd < 0
BEGIN
SET @dd = @maxDatesPerMonth + @dd;
SET @secondCopyMonth = @secondCopyMonth - 1;
IF @secondCopyMonth < 0
BEGIN
SET @secondCopyMonth = @secondCopyMonth + 12;
SET @secondCopyYear = @secondCopyYear - 1;
END;

END;
SET @mm = @firstMonth + @secondCopyMonth;
IF @mm > 11
BEGIN
SET @mm = @mm - 12;
SET @secondCopyYear = @secondCopyYear + 1;
END;

IF @mm < 0
BEGIN
SET @mm = 12 + @mm;
SET @yy = @yy - 1;
END;
SET @yy = @yy + @firstYear + @secondCopyYear;


RETURN CAST(@yy AS nvarchar(2))+'.'+CAST(@mm AS nvarchar(2))+'.'+CAST(@dd AS nvarchar(2));

END;

Rate

4.43 (7)

Share

Share

Rate

4.43 (7)