SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create string using input values


Create string using input values

Author
Message
Sql Student-446896
Sql Student-446896
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 201
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?
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2032 Visits: 10354
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.


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
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7787 Visits: 25280
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.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Sql Student-446896
Sql Student-446896
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 201
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39071 Visits: 38518
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




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
db4breakfast
db4breakfast
SSC-Addicted
SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)

Group: General Forum Members
Points: 469 Visits: 389
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.
db4breakfast
db4breakfast
SSC-Addicted
SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)

Group: General Forum Members
Points: 469 Visits: 389
Hi Lynn,

I like the solution you provided. Didn't thought of that.

--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 1721
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)



ID StartYear EndYear DYears
1 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/



dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7229 Visits: 6431
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 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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85284 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search