How to process join date and anniversary date in reports if the requirement is to show the anniversary date fall in this year or next year?

I have used a case in my query to break down two scenarios.

1.       Check adding 1 year to the joined year equals to 1 year add to current year

2.       If equals then Anniversary Date is add one year to Joined Date

3.       Else calculate the no of years from current year to Joined date and add the number of years to Joined year to get the Anniversary Date.

 

SELECT a.jn_dte AS [Jonned Date],

CASE

WHEN DATEPART(year,DATEADD(year,1,a.jn_dte)) = DATEPART(year,dateadd(year,1,GETDATE()))

THEN DATEADD(year,1,a.jn_dte)

ELSE DATEADD(year,DATEPART(year,GETDATE()) - DATEPART(year,a.jn_dte),a.jn_dte)

END AS [Position Anniversary Date]

FROM company a WITH(NOLOCK)

Joined Date	Add 1year to join date	Check	Equals to current year + 1	Results	If No, check the year difference from current year	Equals	Year Difference	Add	Join year	Final result
1991-03-18	1992	=	2015	No	2014-1991	=	23	+	1991	2014-03-18
2014-08-18	2015	=	2015	Yes			1	+	2014	2015-08-05

SQL : Change the Uppercase to Lowercase But Uppercase after hyphen, apostophe or space

Below function change uppercase name to lower case with first letter capital if the name has space, hyphen(-) or apostophe (‘).

 

Ex : FLOWER TULIP  to Flower Tulip

     FLOWER-TULIP to Flower-Tulip

     TULIP O’TULIP to Tulip O’Tulip

     FLOWER BUD-TULIP O’TULIP  to Flower Bud-Tulip O’Tulip

 

CREATE FUNCTION CapToLower(@Name VARCHAR(8000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Position INT

                  
SELECT @Name    = STUFF(LOWER(@Name),1,1,UPPER(LEFT(@Name,1))) COLLATE Latin1_General_Bin,@Position = PATINDEX('%[^A-Za-z][a-z]%',@Name COLLATE Latin1_General_Bin);

WHILE @Position > 0

SELECT @Name   = STUFF(@Name,@Position,2,UPPER(SUBSTRING(@Name,@Position,2))) COLLATE Latin1_General_Bin,@Position = PATINDEX('%[^A-Za-z][a-z]%',@Name COLLATE Latin1_General_Bin);

RETURN @Name;

END
GO