SQL Server Function: Cursor or Tally Table; which to use, HELP
-
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:451111111 1111111 111 111 111111 1111
TestRef-AA 16/09/2013 10:451111111 1111111 111 111 111111 1111
TestRef-AA 23/09/2013 10:451111111 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
-
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! -
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 -
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 😛
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! -
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! -
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 😛
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 😉
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
ChrisM@home (2/11/2014)
Brilliant! Thanks for posting this, Lowell. I can see it becoming famous 😉Agreed. That is truly beautiful. 😀
-
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 😀 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
-
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 😀 lolThe 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! 😀 )
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! -
I didn't mean the code was wrong, just that I didnt post the requirements correctly 😀 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-AA1/1/2013 1111111 1111111 20
TestRef-AB10/11/20131 111 1 9
TestRef-AC3/4/2014 111 111 111 21
TestRef-AD9/9/2013 11 11 1 19
The correct output would look like this for the example would be
Ref Date pattern PatternLength
TestRef-AA1/1/2013 1111111 1111111 20
TestRef-AA29/01/2013 1111111 1111111 20
TestRef-AA5/2/2013 1111111 1111111 20
TestRef-AA12/2/2014 1111111 1111111 20
TestRef-AA19/02/2013 1111111 1111111 20
Let me know if that's correct or enough 🙂
Thanks again
-
can you convert that into a CTE or insert statemetns, the way i did?
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! -
LOVE the pic Lowell!! Your code rocks too, btw.
Oh, and I would like to request that the OP makes whomever designed that system to drop and give me 20!!! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Like this ?
WITH CTE(reference,StartDate,Pattern,PatternLength) AS
(
SELECT 'TestRef-AA',convert(datetime,'01/01/2013 10:45'),' 1111 111 11','15'
SELECT 'TestRef-AA',convert(datetime,'10/11/2013 09:00'),'11 11 111 ','12'
SELECT 'TestRef-AA',convert(datetime,'09/09/2013 13:00'),'11111 11 ','9'
SELECT 'TestRef-AA',convert(datetime,'02/08/2013 12:00'),'1 11 1 ','11'
)
🙂
-
good start Knives;
so here's my same query, with your updated data in it;
so if you run this and look at the data, does it produce the desired results?
code]
;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,PatternLength) AS
(
SELECT 'TestRef-AA',convert(datetime,'01/01/2013 10:45'),' 1111 111 11','15' UNION ALL
SELECT 'TestRef-BB',convert(datetime,'10/11/2013 09:00'),'11 11 111 ','12' UNION ALL
SELECT 'TestRef-CC',convert(datetime,'09/09/2013 13:00'),'11111 11 ','9' UNION ALL
SELECT 'TestRef-DD',convert(datetime,'02/08/2013 12:00'),'1 11 1 ','11'
)
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'
order by reference, N
[/code]
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! -
Thanks Lowell, I'll have a look first thing.The more I think about it, the more I think it'll work. Thanks, your a legend 🙂
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply