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)