May 4, 2015 at 8:05 am
Hi All
The code below instead of inserting in tblMonths for each record in tblDDAccounts does it the number of records that are in tblDDAccounts for the 1st account only instead of each account. If anyone could tell me where the error in the code is I would very much appreciate it.
Ta Asta
CREATE TABLE [dbo].[tblDDAccounts]([Account] int,[MonthsBetweenRuns] int,[DDCycle] int)
INSERT INTO tblDDAccounts(Account, MonthsBetweenRuns, DDCycle)
VALUES (1, 2, 2);
INSERT INTO tblDDAccounts(Account, MonthsBetweenRuns, DDCycle)
VALUES (2, 3, 1);
INSERT INTO tblDDAccounts(Account, MonthsBetweenRuns, DDCycle)
VALUES (3, 4, 7);
INSERT INTO tblDDAccounts(Account, MonthsBetweenRuns, DDCycle)
VALUES (4, 6, 7);
INSERT INTO tblDDAccounts(Account, MonthsBetweenRuns, DDCycle)
VALUES (5, 6, 6);
----------------------------------------------
CREATE TABLE tblMonths (acc int, month int)
----------------------------------------------
DECLARE @total INT
DECLARE @loop INT
SET @loop = 1
SELECT @total = COUNT(Account) FROM tblDDAccounts
WHILE (@loop <= @total)
BEGIN
------------------------------------------------------
declare rst cursor for SELECT Account, MonthsBetweenRuns, DDCycle FROM tblDDAccounts WHERE DDCycle IS NOT NULL and MonthsBetweenRuns <>1
declare @acc int --Account
declare @months int --MonthBetweenRuns
declare @cycle int --DDCycle
declare @counter int --Loop Counter
declare @times int --number of times Loop should run i.e. 12/MonthBetweenRuns
--CREATE TABLE #months (acc int, month int)
OPEN rst
FETCH NEXT FROM rst INTO @acc, @months, @cycle
SET @times = (12/@months)
SET @counter = 0
WHILE @counter < @times
BEGIN
INSERT INTO tblMonths SELECT @acc, (@months + @months * @counter) AS months
SET @counter = @counter + 1
END
CLOSE rst
DEALLOCATE rst
---------------------------------------------------------------------------------
SET @loop = @loop + 1
END
SELECT * FROM tblMonths
DELETE tblMonths
CLOSE rst
DEALLOCATE rst
----------------------------------------------------------------------------------------------------
SELECT * FROM tblMonths
DELETE tblMonths
May 4, 2015 at 9:39 am
Ouch. Explain what you're trying to accomplish. You can probably do this with a Tally or "numbers" table and a simple query.
May 4, 2015 at 10:00 am
To add to what pietlinden stated and perhaps explain the concern is that looping in t-sql is horrible from a performance perspective. When you have nested cursors you also have an excruciatingly slow process. Most of the time these queries or processes can be redesigned in a set based approach.
BTW, excellent job posting a sample table and data!!!
If you can explain clearly what you expect for output I would be happy to help you find a set based solution to this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 4, 2015 at 10:16 am
Hi
I will try to explain. My starting point is a table with data similar to what I have created in tblDDAccounts. This data is about accounts and when they are due a direct debit
Account is the account number.
MonthsBetweenRuns, is the number of month between each direct debit.
DDCycle is the 1st month in which in the direct debit should take place.
I am trying to dynamically create a table from this data that will list each account and each month that account is due a direct debit
Note if an account has a direct debit due each month i.e. MonthsBetweenRuns is = 1 then this account can be excluded.
All is working the way I would like only its not iterating to account 2 and 3 etc but instead returns the result set for account 1 5 times.
Thank you so much for any help
tblDDAccounts
AccountMonthsBetweenRunsDDCycle
122
231
347
467
566
And my Loop currently returns:
tblMonths
accmonth
12
14
16
18
110
112
12
14
16
18
110
112
12
14
16
18
110
112
12
14
16
18
110
112
12
14
16
18
110
112
What is is suppos to deliver is:
tblMonths
accmonth
12
14
16
18
110
112
21
24
27
210
37
311
33
47
41
56
512
May 4, 2015 at 11:59 am
Thanks for the explanation... Helps a lot! Is this what you were looking for?
SELECT Account
, MonthsBetweenRuns
, DDCycle
, Num
FROM tblDDAccounts A CROSS JOIN Tally T
WHERE Num % MonthsBetweenRuns = 0;
Note, I created a Tally table (or Numbers table)... Jeff Moden has a great article on it, which is here[/url]
You could easily append this to a table somewhere, just add the INSERT INTO... at the top.
HTH,
Pieter
No loops, no cursors <shudder>... (Wait, this sounds like Dwain!)
May 4, 2015 at 12:09 pm
Hi thank for you help, I will look into what you are doing here more. Not sure it will work for me as in reality I will not know what will records will be in tblDDAccounts as they will vary from database to database. I wonder exactly what you have put in the Tally table, maybe this should be obvious to me but afraid not.
Can anyone help me fix the loop I have created to it iterates through the accounts. Would still like to see where I'm going wrong. But will have a look at using this Tally table to see if it might work for me.
All suggestions welcome its always good to see alternative methods.
Asta 🙂
May 4, 2015 at 12:14 pm
The "Tally table" is simply a table with consecutive numbers. It could start at 0 or 1 or any number you need. It could even be a view, a function, a cte or a subquery.
Here's an example, but I'm having a hard time on knowing when will you go to the next year and when won't you.
I've also included the sample data in a consumable format as you're expected to do in future posts. This is something that we ask so we don't have to spend more time preparing a tested solution. This can help you to prepare your posts. 😉 http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
CREATE TABLE tblDDAccounts(
Accountint,
MonthsBetweenRunsint,
DDCycleint
)
INSERT INTO tblDDAccounts VALUES
(1,2,2),
(2,3,1),
(3,4,7),
(4,6,7),
(5,6,6)
SELECT a.*,
ISNULL( NULLIF( (n + DDCycle) % 12, 0), 12) AS month
FROM tblDDAccounts a
JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),
(7),(8),(9),(10),(11),(12))Tally(n)
ON n % MonthsBetweenRuns = 0
AND n + DDCycle < 12 + MonthsBetweenRuns;
May 4, 2015 at 12:26 pm
WOW, this is perfect now that I see it. I guess Tally is a key work not a table. I have not heard of it before so will be using this by the look of it and learning more about you did and seeing if I can I use it for other bits and bobs 🙂
Thanks a million. Very interesting indeed.
Asta
May 4, 2015 at 12:36 pm
Thank you for the feedback. I'm glad it helped.
But please, make sure to understand what the code is doing. If you have any questions, feel free to ask.
Also remember to read the article that Pieter shared about Tally tables: http://www.sqlservercentral.com/articles/T-SQL/62867/
You can also search for more articles on the web.
May 4, 2015 at 1:28 pm
A tally table is just that - a table. Whatever version of it you use (physical table, table-value constructor or inline CTE) it's just a table. It contains a number of rows from 1:N and you use the ones you need to use. The implementation of it is where the fun part comes into play. You can use it to replace loops in a great many situations.
Jeff Moden's article that's already been referenced is a great place to start. I'd highly recommend reading it. If you have to read it more than once, it's still worth the time. Years ago, Gianluca called it a "Swiss Army Knife of SQL" and I think it fits. Once you get used to using it, you see other situations where it can help.
May 4, 2015 at 1:40 pm
Thank again all, I certainly will be looking up these links and studying in full how this work.
Thank for the links and info.
Asta
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply