Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Create string using input values Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, January 25, 2013 10:58 AM
 Valued Member Group: General Forum Members Last Login: Tuesday, December 24, 2013 8:35 AM Points: 69, Visits: 178
 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?
Post #1411851
 Posted Friday, January 25, 2013 11:16 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 12:46 AM Points: 1,053, Visits: 5,548
 Sql Student-446896 (1/25/2013)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? Here's one simple kludgy way:`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,'')`Look up Tally tables and FOR XML PATH to see how thiscan get interesting. Low-hanging fruit picker and defender of the moggies For better assistance in answering your questions, please read this. Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1411861
 Posted Friday, January 25, 2013 11:51 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 7:34 PM Points: 5,432, Visits: 23,063
 Here is another method .... not efficient .... not recommended ... but it does what you seem to need to do.` 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`If you would/could explain why you have this requirement, some may be able to assist you with a more efficient solution. If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read
Post #1411878
 Posted Friday, January 25, 2013 12:24 PM
 Valued Member Group: General Forum Members Last Login: Tuesday, December 24, 2013 8:35 AM Points: 69, Visits: 178
 Thank 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 @AEND
Post #1411897
 Posted Friday, January 25, 2013 12:58 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 8:43 PM Points: 22,243, Visits: 29,586
 bitbucket-25253 (1/25/2013)Here is another method .... not efficient .... not recommended ... but it does what you seem to need to do.` 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`If you would/could explain why you have this requirement, some may be able to assist you with a more efficient solution.Like this one Ron?`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`
Post #1411907
 Posted Sunday, January 27, 2013 12:30 AM
 Old Hand Group: General Forum Members Last Login: Monday, January 06, 2014 6:51 PM Points: 374, Visits: 329
 Hi,This solution is similar like other posts. this one is not very efficient but it's enough to solve the problem.`--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` --------------------------------------------------------------------------------------Hai TonMy DB4BREAKFAST blog: http://db4breakfast.blogspot.com
Post #1412108
 Posted Sunday, January 27, 2013 12:34 AM
 Old Hand Group: General Forum Members Last Login: Monday, January 06, 2014 6:51 PM Points: 374, Visits: 329
 Hi Lynn,I like the solution you provided. Didn't thought of that. --------------------------------------------------------------------------------------Hai TonMy DB4BREAKFAST blog: http://db4breakfast.blogspot.com
Post #1412109
 Posted Sunday, January 27, 2013 7:41 PM
 SSC-Addicted Group: General Forum Members Last Login: Sunday, September 29, 2013 1:24 AM Points: 429, Visits: 1,721
 OK, 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.`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`Output (will be different every time because the dates are randomly generated)`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/`
Post #1412163
 Posted Sunday, January 27, 2013 8:46 PM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 11:27 PM Points: 3,529, Visits: 4,956
 Steven,Not sure why you used all of those yukky loops (reference my mantra):`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`Edit: Fixed a minor compatibility issue with SQL 2005. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!
Post #1412170
 Posted Monday, January 28, 2013 12:16 AM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 11:18 PM Points: 35,591, Visits: 29,834
 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! --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1412204

 Permissions

 Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.