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


SQL Server Function: Cursor or Tally Table; which to use, HELP


SQL Server Function: Cursor or Tally Table; which to use, HELP

Author
Message
Knives85
Knives85
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 257
Hello

I have been given a task which I’m having some trouble which getting my head around the logic for it. I’m unsure if I can use a cursor or a tally table to get the results I need.
Basically I need to create a table function that will take three parameters; Reference, Start Date and, Pattern.
The data currently looks like this


reference startdate pattern
TestRef-AA 09/09/2013 10:45 1111111 1111111 111 111 111111 1111



And need to look like this


reference startdate pattern
TestRef-AA 09/09/2013 10:45 1111111 1111111 111 111 111111 1111
TestRef-AA 16/09/2013 10:45 1111111 1111111 111 111 111111 1111
TestRef-AA 23/09/2013 10:45 1111111 1111111 111 111 111111 1111



This data is on a single row, I need to create multiple rows from this one row. The function needs to loop through the pattern column and create a new line with 7 days added to the previous line start date whenever it find a “1” in the pattern column. If there is a gap in the pattern it doesn’t need to create a new row.

I would do this in an array in VB; however, I don’t believe you can create an array in SQL Server so I think I’ll need a cursor or a tally table? Can anyone help with this, even if it’s pointing me in the right direction? I don’t have a clue where to start


Thanks a lot
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29481 Visits: 39986
yep , a tally table, combined with a dateadd seems to be what you are after.

I just put together an example, but you'd use your own tally table, and extend out and limit the results as required.


;WITH MyCTE([reference],[startdate],[pattern])
AS
(
SELECT 'TestRef-AA',convert(datetime,'09/09/2013 10:45'),'1111111 1111111 111 111 111111 1111'
)
SELECT DATEADD(wk,Tally.N,[startdate]) As CalcDate,*
FROM MyCTE
CROSS JOIN (SELECT 0 As N UNION ALL SELECT 1 UNION ALL SELECT 2) Tally



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13185 Visits: 8566
Lowell, you are a better man than I this morning (or perhaps my caffeine hasn't fully kicked in yet)! Without sample output I can't make heads or tails of what the OP actually wants here. Hehe

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29481 Visits: 39986
well i've only done one piece of it...adding the date;

i have a simple macro i use to dump typical copy/paste data and turn it into a CTE, so coming up with SOMETHING feels better than skipping the question altogether. i try not to pad my post count asking for DDL and sample data too often :-P


the second part i glossed over:
there's a requirement for " whenever it find a “1” in the pattern column. If there is a gap in the pattern it doesn’t need to create a new row."
can be done, but need more than one sample row, and need to know the length of string, whether the preceeding whitespace was relevant, and some more details, but this might get the ball rolling.



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29481 Visits: 39986
took me a bit for this one; this seems to do the whole shebang

;WITH
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM THOUSANDS)
,
MyCTE ([reference],[startdate],[pattern])
AS (SELECT 'TestRef-AA',convert(datetime,'09/09/2013 10:45'),'1111111 1111111 111 111 111111 1111' )

SELECT CASE WHEN SUBSTRING([pattern],N,1) = '1'
THEN DATEADD(wk,Tally.N,[startdate])
ELSE NULL
END As CalcDate,*,
SUBSTRING([pattern],N,1)
FROM MyCTE
CROSS JOIN Tally
WHERE N <= LEN([pattern])
--AND SUBSTRING([pattern],N,1) = '1'





Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2155 Visits: 10394
Lowell (2/11/2014)
well i've only done one piece of it...adding the date;

i have a simple macro i use to dump typical copy/paste data and turn it into a CTE, so coming up with SOMETHING feels better than skipping the question altogether. i try not to pad my post count asking for DDL and sample data too often :-P


the second part i glossed over:
there's a requirement for " whenever it find a “1” in the pattern column. If there is a gap in the pattern it doesn’t need to create a new row."
can be done, but need more than one sample row, and need to know the length of string, whether the preceeding whitespace was relevant, and some more details, but this might get the ball rolling.


Brilliant! Thanks for posting this, Lowell. I can see it becoming famous ;-)


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
Ed Wagner
Ed Wagner
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17374 Visits: 10099
ChrisM@home (2/11/2014)

Brilliant! Thanks for posting this, Lowell. I can see it becoming famous ;-)

Agreed. That is truly beautiful. :-D


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Knives85
Knives85
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 257
Lowell, JamesM said it, that's a brilliant solution, now that I've seen it, I don't know why I didn't think of it :-D lol

The code you posted I think will do it, but there is one thing I don't think it will do. Its my fault I didn't put the requirement properly. Although you hit the point on the head when asking about white space.

The pattern column does have proceeding white space in it, basically the start date is the first date the event is running, the week pattern then holds the remaining weeks that it runs. The first 3 entries in the pattern column can be white space but the fourth can be a 1, for instance. The code would then need to add a 4 weeks to the last rows not 1.

Any ideas?

Let me know if you need any more information :-)

I really appreciate the help, thanks
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29481 Visits: 39986
Knives85 (2/11/2014)
Lowell, JamesM said it, that's a brilliant solution, now that I've seen it, I don't know why I didn't think of it :-D lol

The code you posted I think will do it, but there is one thing I don't think it will do. Its my fault I didn't put the requirement properly. Although you hit the point on the head when asking about white space.

The pattern column does have proceeding white space in it, basically the start date is the first date the event is running, the week pattern then holds the remaining weeks that it runs. The first 3 entries in the pattern column can be white space but the fourth can be a 1, for instance. The code would then need to add a 4 weeks to the last rows not 1.

Any ideas?

Let me know if you need any more information :-)

I really appreciate the help, thanks


can you modify the example i posted with more data, including the preceeding spaces, then?
if you run it without any other changes than the data modification, can you say what is wrong (well what needs to be changed; i wouldn't post wrong code! :-D )

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Knives85
Knives85
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 257
I didn't mean the code was wrong, just that I didnt post the requirements correctly :-D lol

I have altered the example data I originally posted. The first one has 3 prceeding white space then a 1.


Ref Date pattern PatternLength
TestRef-AA 1/1/2013 1111111 1111111 20
TestRef-AB 10/11/2013 1 111 1 9
TestRef-AC 3/4/2014 111 111 111 21
TestRef-AD 9/9/2013 11 11 1 19



The correct output would look like this for the example would be


Ref Date pattern PatternLength
TestRef-AA 1/1/2013 1111111 1111111 20
TestRef-AA 29/01/2013 1111111 1111111 20
TestRef-AA 5/2/2013 1111111 1111111 20
TestRef-AA 12/2/2014 1111111 1111111 20
TestRef-AA 19/02/2013 1111111 1111111 20



Let me know if that's correct or enough :-)

Thanks again
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