Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Create string using input values Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 10:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:53 PM
Points: 69, Visits: 187
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?
Post #1411851
Posted Friday, January 25, 2013 11:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 1,074, Visits: 6,379
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
Post #1411861
Posted Friday, January 25, 2013 11:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:50 PM
Points: 5,574, Visits: 24,823
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
Post #1411878
Posted Friday, January 25, 2013 12:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:53 PM
Points: 69, Visits: 187
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

Post #1411897
Posted Friday, January 25, 2013 12:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:21 PM
Points: 23,081, Visits: 31,619
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





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)
Post #1411907
Posted Sunday, January 27, 2013 12:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:55 AM
Points: 386, Visits: 363
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.
Post #1412108
Posted Sunday, January 27, 2013 12:34 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:55 AM
Points: 386, Visits: 363
Hi Lynn,

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


--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
Post #1412109
Posted Sunday, January 27, 2013 7:41 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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 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/


Post #1412163
Posted Sunday, January 27, 2013 8:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:45 PM
Points: 3,617, Visits: 5,237
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!
Post #1412170
Posted Monday, January 28, 2013 12:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:19 PM
Points: 36,793, Visits: 31,251
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1412204
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse