DECLARE @BegYr INT = 2013, @EndYr INT = 2015DECLARE @CombYr VARCHAR(10) = '';WITH Tally AS (SELECT n = 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4)SELECT @CombYr = @CombYr + '/'+RIGHT(@BegYr + n,1) FROM TallyWHERE @BegYr + n BETWEEN @BegYr AND @EndYrSELECT STUFF(@CombYr,1,1,'')
DECLARE @BegYr INT = 2013 DECLARE @EndYr INT = 2015 DECLARE @E INT DECLARE @A VARCHAR(10) DECLARE @Z VARCHAR(10) SET @E = @BegYr SET @Z = (SELECT SUBSTRING(CAST(@E AS VARCHAR(4)),LEN(CAST(@E AS VARCHAR(4))),LEN(CAST(@E AS VARCHAR(4))))) WHILE @E < @EndYr BEGIN SET @E = @E + 1 SET @A = (SELECT '/' + SUBSTRING(CAST(@E AS VARCHAR(4)),LEN(CAST(@E AS VARCHAR(4))),LEN(CAST(@E AS VARCHAR(4))))) SET @Z = @Z + @A END SELECT @Z Result:3/4/5
DECLARE @BegYr INT = 2013, @EndYr INT = 2015, @OddReq VARCHAR(32);WITH quickTally(n) AS (SELECT TOP(@EndYr - @BegYr + 1) n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))dt(n))select @OddReq = stuff((select '/' + right(cast(@BegYr + n as varchar),1) from quickTally for xml path(''),type).value('.','varchar(32)'),1,1,'');select @OddReq;go
--goal: Output CombYr = 3/4/5--Input: BegYr int, EndYr int--assume inputs are valid. and year is 4-digit format. and EndYr - BegYr <= 10DECLARE @BegYr INT = 2010, @EndYr INT = 2020;DECLARE @YrDiff SMALLINT, @CurrentYr SMALLINT, @CombYr VARCHAR(250)= '';SET @YrDiff = @EndYr - @BegYr; --difference btw start and end yearSET @CurrentYr = CAST(SUBSTRING(CAST(@BegYr AS CHAR(4)),4,1) AS SMALLINT); --last digit of current yearWHILE @YrDiff >= 0 BEGIN IF @YrDiff = 0 SET @CombYr = @CombYr + CAST(@CurrentYr AS VARCHAR(3)); ELSE SET @CombYr = @CombYr + CAST(@CurrentYr AS VARCHAR(3))+ '/'; SET @YrDiff = @YrDiff - 1; SET @CurrentYr = @CurrentYr + 1;END SELECT @CombYr AS CombinedYearOuput; --output result
IF OBJECT_ID('tempdb..#Years') IS NOT NULLDROP TABLE #YearsIF OBJECT_ID('tempdb..#Results') IS NOT NULLDROP TABLE #Results--a table to hold the sample dataCREATE TABLE #Years ( [ID] INT IDENTITY(1,1) NOT NULL, [StartYear] DATE NULL, [EndYear] DATE NULL, PRIMARY KEY (ID)) DECLARE @RandomStartDate DATE ,@RandomEndDate DATE ,@Counter INT ,@NumSampleRows INTSET @Counter = 1 SET @NumSampleRows = 10 --change this to as many sample as you want --populate the sample table with random datesWHILE @Counter < = @NumSampleRows BEGIN SET @RandomStartDate = DATEADD(day,(ABS(CHECKSUM(NEWID()))%65530),0) SET @RandomEndDate = DATEADD(year,(ABS(CAST(NEWID() AS BINARY(6))%10)+1),@RandomStartDate) INSERT INTO #Years VALUES (@RandomStartDate,@RandomEndDate) SET @Counter = @Counter + 1 END--a table for the resultsCREATE TABLE #Results ( [ID] INT NOT NULL, [StartYear] DATE NULL, [EndYear] DATE NULL, [DYears] VARCHAR(255) NULL, PRIMARY KEY (ID)) DECLARE @x INT ,@NumRows INT SET @x = 1SET @NumRows = (SELECT MAX(ID) FROM #Years)--create the string of single digit years for each row in the tableWHILE @x <= @NumRows BEGIN INSERT INTO #Results SELECT ID ,StartYear ,EndYear ,(SELECT STUFF(CAST(t1.N AS CHAR(4)),1,3,'')+'/' FROM ( SELECT ID ,CAST(YEAR(y.StartYear) AS CHAR(4)) AS SYear ,CAST(YEAR(y.EndYear) AS CHAR(4))AS EYear FROM #Years AS y ) r LEFT OUTER JOIN dbo.Tally AS t1 ON t1.N > 0 WHERE t1.N BETWEEN SYear AND EYear AND r.ID = @x FOR XML PATH('') ) AS DYears FROM #Years WHERE ID = @x SET @x = @x + 1 ENDSELECT * FROM #Results
ID StartYear EndYear DYears1 1919-03-28 1927-03-28 9/0/1/2/3/4/5/6/7/2 2028-07-26 2030-07-26 8/9/0/3 1977-04-11 1983-04-11 7/8/9/0/1/2/3/4 1966-05-04 1967-05-04 6/7/5 2018-03-27 2019-03-27 8/9/6 2024-03-28 2026-03-28 4/5/6/7 1966-02-23 1975-02-23 6/7/8/9/0/1/2/3/4/5/8 1988-08-21 1996-08-21 8/9/0/1/2/3/4/5/6/9 2061-02-02 2063-02-02 1/2/3/10 1974-11-01 1981-11-01 4/5/6/7/8/9/0/1/
IF OBJECT_ID('tempdb..#Years') IS NOT NULLDROP TABLE #Years--a table to hold the sample dataCREATE TABLE #Years ( [ID] INT IDENTITY(1,1) NOT NULL, [StartYear] DATE NULL, [EndYear] DATE NULL, PRIMARY KEY (ID)) DECLARE @NumSampleRows INT SET @NumSampleRows = 10;WITH Tally (n) AS ( SELECT TOP (@NumSampleRows) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)INSERT INTO #YearsSELECT [StartYear] ,[EndYear]=DATEADD(day, ABS(CHECKSUM(NEWID())) % 6000, [StartYear])FROM TallyCROSS APPLY (SELECT [StartYear]=DATEADD(day, ABS(CHECKSUM(NEWID())) % 50000, 0)) aSELECT ID, [StartYear], [EndYear], OddStr=( SELECT CASE n WHEN 0 THEN '' ELSE '/' END + CAST(YEAR(DATEADD(year, n, StartYear))%10 AS VARCHAR) FROM ( SELECT 0 UNION ALL SELECT TOP (DATEDIFF(year, [StartYear], [EndYear])) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns) a(n) ORDER BY n FOR XML PATH('')) FROM #YearsGROUP BY ID, [StartYear], [EndYear]IF OBJECT_ID('tempdb..#Years') IS NOT NULLDROP TABLE #Years