SQL Server Function: Cursor or Tally Table; which to use, HELP
-
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 🙂
-
Hi lowell
I've tested the code and it worked beautifully. I need a little help though, I got the logic wrong when I explained the issue originally. There should never be any instance of the pattern not starting with a 1;however, the very first row generated needs to be the very first instance of the start date, not the start date with 7 adds added. I have tried to get around this by slightly altering the case statement to replace the first row date with the actual start date but then the second row date that is generated doesn't use the replace start date from the first row but the original, meaning that it's out by a week.
Any ideas?
Here's the code I've changed
;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)
,
TestCTE(reference,StartDate,Pattern,PatternLength) AS
(
SELECT 'VideoGames-S1',convert(datetime,'01/01/2013 10:45'),'11 111 111111 1111','18' --Course 13NAPUSD-B2
)
SELECT
'StartTime' =
CASE
WHEN Tally.N = 1 THEN [startdate]
WHEN SUBSTRING([pattern],N,1) = '1' THEN DATEADD(wk,Tally.N,[startdate]) ELSE NULL
END,
TestStartDate_DATEADD = DATEADD(wk,Tally.N,[startdate])
,*
, SUBSTRING([pattern],N,1)
FROM
TestCTE testcte
CROSS JOIN Tally tally
WHERE
N <= LEN([pattern])
--AND SUBSTRING([pattern],N,1) = '1'
ORDER BY
reference, N
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply