January 25, 2013 at 10:58 am
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 = 2015
Output: CombYr = 3/4/5
How do i do this as script?
January 25, 2013 at 11:16 am
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 = 2015
Output: CombYr = 3/4/5
How do i do this as script?
Here's one simple kludgy way:
DECLARE @BegYr INT = 2013, @EndYr INT = 2015
DECLARE @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 Tally
WHERE @BegYr + n BETWEEN @BegYr AND @EndYr
SELECT STUFF(@CombYr,1,1,'')
Look up Tally tables and FOR XML PATH to see how thiscan get interesting.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 25, 2013 at 11:51 am
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.
January 25, 2013 at 12:24 pm
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 = @BegYr
IF @Diff = 3
BEGIN
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
END
ELSE
BEGIN
SET @a = (SELECT SUBSTRING(CAST(@BegYR AS VARCHAR(4)), 4, 1) + '/' + SUBSTRING(CAST(@EndYR AS VARCHAR(4)), 4, 1))
Select @a
END
January 25, 2013 at 12:58 pm
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
January 27, 2013 at 12:30 am
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 <= 10
DECLARE @BegYr INT = 2010, @EndYr INT = 2020;
DECLARE @YrDiff SMALLINT, @CurrentYr SMALLINT, @CombYr VARCHAR(250)= '';
SET @YrDiff = @EndYr - @BegYr; --difference btw start and end year
SET @CurrentYr = CAST(SUBSTRING(CAST(@BegYr AS CHAR(4)),4,1) AS SMALLINT); --last digit of current year
WHILE @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 Ton
My Db4Breakfast blog.
January 27, 2013 at 12:34 am
Hi Lynn,
I like the solution you provided. Didn't thought of that.
--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
January 27, 2013 at 7:41 pm
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 NULL
DROP TABLE #Years
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
DROP TABLE #Results
--a table to hold the sample data
CREATE 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 INT
SET @Counter = 1
SET @NumSampleRows = 10 --change this to as many sample as you want
--populate the sample table with random dates
WHILE @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 results
CREATE 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 = 1
SET @NumRows = (SELECT MAX(ID) FROM #Years)
--create the string of single digit years for each row in the table
WHILE @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
END
SELECT * FROM #Results
Output (will be different every time because the dates are randomly generated)
IDStartYearEndYearDYears
11919-03-281927-03-289/0/1/2/3/4/5/6/7/
22028-07-262030-07-268/9/0/
31977-04-111983-04-117/8/9/0/1/2/3/
41966-05-041967-05-046/7/
52018-03-272019-03-278/9/
62024-03-282026-03-284/5/6/
71966-02-231975-02-236/7/8/9/0/1/2/3/4/5/
81988-08-211996-08-218/9/0/1/2/3/4/5/6/
92061-02-022063-02-021/2/3/
101974-11-011981-11-014/5/6/7/8/9/0/1/
January 27, 2013 at 8:46 pm
Steven,
Not sure why you used all of those yukky loops (reference my mantra):
IF OBJECT_ID('tempdb..#Years') IS NOT NULL
DROP TABLE #Years
--a table to hold the sample data
CREATE 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 #Years
SELECT [StartYear]
,[EndYear]=DATEADD(day, ABS(CHECKSUM(NEWID())) % 6000, [StartYear])
FROM Tally
CROSS APPLY (SELECT [StartYear]=DATEADD(day, ABS(CHECKSUM(NEWID())) % 50000, 0)) a
SELECT 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 #Years
GROUP BY ID, [StartYear], [EndYear]
IF OBJECT_ID('tempdb..#Years') IS NOT NULL
DROP TABLE #Years
Edit: Fixed a minor compatibility issue with SQL 2005.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 28, 2013 at 12:16 am
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
Change is inevitable... Change for the better is not.
January 28, 2013 at 12:18 am
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 = 2015
Output: CombYr = 3/4/5
How do i do this as script?
Just double checking... What do you want for a start year of 2009 and and end year of 2021? Read this carefully.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2013 at 9:07 am
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:
January 28, 2013 at 5:03 pm
Steven Willis (1/28/2013)
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:
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. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2013 at 6:54 pm
Jeff Moden (1/28/2013)
Just say "NO" to the mind drug known as "loops" in T-SQL. 😉
I do!! 😀
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 28, 2013 at 7:51 pm
dwain.c (1/28/2013)
Jeff Moden (1/28/2013)
Just say "NO" to the mind drug known as "loops" in T-SQL. 😉I do!! 😀
😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply