InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 02:01:24 GMT20http://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspx[quote][b]tnk7200 (2/3/2013)[/b][hr][quote][b]Jeff Moden (1/28/2013)[/b][hr]C'mon, folks! Just look at the mess of code that a While loop makes for this! No one should be writing a While Loop for this![/quote]Hi Jeff,I'm inspired by your anti-RBAR philosophy, I went back and come up with a 2nd solution with no loop. : )Anyway, the reason I used the while loop because I assumed there's no "sequence" table. [code="sql"]--generate a sequence of digit from 0 to nCREATE TABLE DBO.NUMBERS(i INT NOT NULL PRIMARY KEY);goINSERT INTO dbo.numbers(i)VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);goINSERT INTO dbo.numbers(i)SELECT numfrom ( SELECT (n4.i * 1000 + n3.i * 100 + n2.i * 10 + n1.i) AS num FROM dbo.numbers n1 CROSS JOIN dbo.numbers n2 CROSS JOIN dbo.numbers n3 CROSS JOIN dbo.numbers n4 ) xWHERE num > 9ORDER BY num;--goal: Output CombYr = 3/4/5/--Input: @startYr int, @endYr int--assume inputs are valid. and year is 4-digit format. and EndYr - BegYr <= 10DECLARE @startYr INT = 2011 , @endYr INT = 2013;DECLARE @outputStr NVARCHAR(100) = ''; SELECT @outputStr = @outputStr + RIGHT(CAST(n.i AS VARCHAR(4)), 1) + '/' FROM dbo.numbers nWHERE n.i BETWEEN @startYr AND @endYr;SELECT @outputStr;[/code][/quote]Good start! Now, make it run for a whole table of Start and End dates and you'll really be on your way to avoiding RBAR. The use of any Scalar Function or Multi-Line TVF will be a disqualifier because those are a hidden form of RBAR. Truth is, no function is actually required here. Some of the scripts with the correct answer in this thread look a bit complicted because they decided to build the Tally structure on the fly but go have a look at those.Sun, 03 Feb 2013 15:20:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspx[quote][b]Jeff Moden (1/28/2013)[/b][hr]C'mon, folks! Just look at the mess of code that a While loop makes for this! No one should be writing a While Loop for this![/quote]Hi Jeff,I'm inspired by your anti-RBAR philosophy, I went back and come up with a 2nd solution with no loop. : )Anyway, the reason I used the while loop because I assumed there's no "sequence" table. [code="sql"]--generate a sequence of digit from 0 to nCREATE TABLE DBO.NUMBERS(i INT NOT NULL PRIMARY KEY);goINSERT INTO dbo.numbers(i)VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);goINSERT INTO dbo.numbers(i)SELECT numfrom ( SELECT (n4.i * 1000 + n3.i * 100 + n2.i * 10 + n1.i) AS num FROM dbo.numbers n1 CROSS JOIN dbo.numbers n2 CROSS JOIN dbo.numbers n3 CROSS JOIN dbo.numbers n4 ) xWHERE num > 9ORDER BY num;--goal: Output CombYr = 3/4/5/--Input: @startYr int, @endYr int--assume inputs are valid. and year is 4-digit format. and EndYr - BegYr <= 10DECLARE @startYr INT = 2011 , @endYr INT = 2013;DECLARE @outputStr NVARCHAR(100) = ''; SELECT @outputStr = @outputStr + RIGHT(CAST(n.i AS VARCHAR(4)), 1) + '/' FROM dbo.numbers nWHERE n.i BETWEEN @startYr AND @endYr;SELECT @outputStr;[/code]Sun, 03 Feb 2013 14:38:47 GMTdb4breakfasthttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspx[quote][b]dwain.c (1/28/2013)[/b][hr][quote][b]Jeff Moden (1/28/2013)[/b][hr]Just say "NO" to the mind drug known as "loops" in T-SQL. ;-)[/quote]I do!! :-D[/quote]:-DMon, 28 Jan 2013 19:51:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspx[quote][b]Jeff Moden (1/28/2013)[/b][hr]Just say "NO" to the mind drug known as "loops" in T-SQL. ;-)[/quote]I do!! :-DMon, 28 Jan 2013 18:54:38 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspx[quote][b]Steven Willis (1/28/2013)[/b][hr]OK, OK, I surrender. But for creating sample data I see no reason not to use a loop--it's quick even if dirty. I was playing around and spent enough time as it was so just went with a loop for building the strings. Sorry. (Backs away with tail between legs...) :blush: [/quote]BWAA-HAAA!!!! Understood! The only reasons I can offer you to not use a loop to build sample data is that you're NOT practicing using set based code when you write the loop and because it'll take a relatively painful amount of time to run if you ever need to test with a substantial number of rows.On the main problem, I was (and still am) totally amazed and even a little disgusted that, even after Lynn posted a set based solution, people were still posting While loop solutions. A couple even justified it with the ol' "it's slow but it works" excuse.Just say "NO" to the mind drug known as "loops" in T-SQL. ;-)Mon, 28 Jan 2013 17:03:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspxOK, OK, I surrender. But for creating sample data I see no reason not to use a loop--it's quick even if dirty. I was playing around and spent enough time as it was so just went with a loop for building the strings. Sorry. (Backs away with tail between legs...) :blush: Mon, 28 Jan 2013 09:07:57 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspx[quote][b]Sql Student-446896 (1/25/2013)[/b][hr]Hi,I have a beginning Year and a End year and i have to compute/create a string based on the given years.Example:Input: BegYr = 2013 and EndYr = 2015Output: CombYr = 3/4/5How do i do this as script? [/quote]Just double checking... What do you want for a start year of 2009 and and end year of 2021? Read this carefully.Mon, 28 Jan 2013 00:18:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspxC'mon, folks! Just look at the mess of code that a While loop makes for this! No one should be writing a While Loop for this!Mon, 28 Jan 2013 00:16:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspxSteven,Not sure why you used all of those yukky loops (reference my mantra):[code="sql"]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[/code][b]Edit:[/b] Fixed a minor compatibility issue with SQL 2005.Sun, 27 Jan 2013 20:46:11 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspxOK, just having fun. I create random start and end dates over a maximum 10 year interval for as many rows as you want to test. The main query is similar to Lynn's but loops through all the date rows to build the string. If someone can replace that final loop by using the Tally table or even a CTE knock yourself out. The main query itself does require a Tally table. The code for creating that is easy enough to look up if you don't have one already.[code="sql"]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[/code]Output (will be different every time because the dates are randomly generated)[code="xml"]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/[/code]Sun, 27 Jan 2013 19:41:53 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspxHi Lynn,I like the solution you provided. Didn't thought of that.Sun, 27 Jan 2013 00:34:30 GMTdb4breakfasthttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspxHi,This solution is similar like other posts. this one is not very efficient but it's enough to solve the problem.[code="sql"]--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[/code]Sun, 27 Jan 2013 00:30:10 GMTdb4breakfasthttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspx[quote][b]bitbucket-25253 (1/25/2013)[/b][hr]Here is another method .... not efficient .... not recommended ... but it does what you seem to need to do.[code="sql"] 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[/code]If you would/could explain why you have this requirement, some may be able to assist you with a more efficient solution.[/quote]Like this one Ron?[code="sql"]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[/code]Fri, 25 Jan 2013 12:58:42 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspxThank you Mr.Ron. It worked like a champ. I just had it tweeked a little bit to suit my requirement. Yes. It is indeed an odd requirement to store a comboYear String. DECLARE @BegYr INT = 2013 DECLARE @EndYr INT = 2015 DECLARE @Diff INT SELECT @diff = @EndYr - @BegYr DECLARE @E INT DECLARE @A VARCHAR(10) DECLARE @Z VARCHAR(10) SET @E = @BegYrIF @Diff = 3BEGIN 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 ENDELSEBEGIN SET @A = (SELECT SUBSTRING(CAST(@BegYR AS VARCHAR(4)), 4, 1) + '/' + SUBSTRING(CAST(@EndYR AS VARCHAR(4)), 4, 1)) Select @AENDFri, 25 Jan 2013 12:24:16 GMTSql Student-446896http://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspxHere is another method .... not efficient .... not recommended ... but it does what you seem to need to do.[code="sql"] 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[/code]If you would/could explain why you have this requirement, some may be able to assist you with a more efficient solution.Fri, 25 Jan 2013 11:51:39 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspx[quote][b]Sql Student-446896 (1/25/2013)[/b][hr]Hi,I have a beginning Year and a End year and i have to compute/create a string based on the given years.Example:Input: BegYr = 2013 and EndYr = 2015Output: CombYr = 3/4/5How do i do this as script? [/quote]Here's one simple kludgy way:[code="sql"]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,'')[/code]Look up Tally tables and FOR XML PATH to see how thiscan get interesting.Fri, 25 Jan 2013 11:16:52 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1411851-145-1.aspxHi,I have a beginning Year and a End year and i have to compute/create a string based on the given years.Example:Input: BegYr = 2013 and EndYr = 2015Output: CombYr = 3/4/5How do i do this as script? Fri, 25 Jan 2013 10:58:41 GMTSql Student-446896