Adding Records to SQL Table

I have a table (AGREEMENT) that has details about agreements.  Specifically  Date_RENTED, DUE_DATE, FREQUENCY

I need to create details in another table for each agreement

AGREEMENT   RENTED    DUE     FREQ
1                     1/1/13     2/1/14  M
2                      1/1/12    3/1/14  W

I need records for the above agreement  #1 for 1/1/13, 2/1/13, 3/1/13, etc.  up to the due date.  For Agreement 2, it would be 1/1/12, 1/8/12, 1/15/12, etc.

My problem is that while I can do this in a procedure, I have to create a CURSOR for the AGREEMENT TABLE.  Since I can't write data via a function, I can't use that to get details.

Using a cursor is painfully slow and I need a solution if one is possible w/out a cursor.
I ran on a test set with 7000+ agreements and it took 2 hours to run.  I have a production system with 90000+ agreements.

Anyone have a possible solution to speed up the creation?

Thanks.

Larry
lburnsinmagnoliaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
Here's a test script that may lead you to the solution you need:
DECLARE @Test TABLE
(
    Id INTEGER,
    RentDate DATETIME,
    DueDate DATETIME,
    Method char(1),
    SemiPayOne INTEGER,
    SemiPayTwo INTEGER
)

INSERT INTO @Test
VALUES(1, '1/1/2014', '5/1/2014', 'M', NULL, NULL),
    (2, '1/1/2014', '5/1/2014', 'W', NULL, NULL),
    (3, '1/1/2014', '5/1/2014', 'B', NULL, NULL),
    (4, '1/1/2014', '5/1/2014', 'S', 2, 19),
    (5, '1/1/2014', '5/1/2014', 'S', 6, 22)

;WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2), -- 10*100
e4(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) - 1 FROM e3)
SELECT T.ID, T.RentDate, T.DueDate, T.Method, DATEADD(WEEK, E.n, T.RentDate)
FROM @Test T
    CROSS JOIN e4 E
WHERE DATEADD(WEEK, E.n, T.RentDate) >= t.RentDate AND DATEADD(WEEK, E.n, T.RentDate) <= T.DueDate AND T.Method = 'W'
UNION 
SELECT T.ID, T.RentDate, T.DueDate, T.Method, DATEADD(MONTH, E.n, T.RentDate)
FROM @Test T
    CROSS JOIN e4 E
WHERE DATEADD(MONTH, E.n, T.RentDate) >= t.RentDate AND DATEADD(MONTH, E.n, T.RentDate) <= T.DueDate AND T.Method = 'M'
UNION 
SELECT T.ID, T.RentDate, T.DueDate, T.Method, DATEADD(WEEK, E.n * 2, T.RentDate)
FROM @Test T
    CROSS JOIN e4 E
WHERE DATEADD(WEEK, E.n * 2, T.RentDate) >= t.RentDate AND DATEADD(WEEK, E.n * 2, T.RentDate) <= T.DueDate AND T.Method = 'B'
UNION 
SELECT T.ID, T.RentDate, T.DueDate, T.Method, DATEADD(MONTH, E.n, DATEADD(DAY, T.SemiPayOne - 1, T.RentDate))
FROM @Test T
    CROSS JOIN e4 E
WHERE DATEADD(MONTH, E.n, DATEADD(DAY, T.SemiPayOne - 1, T.RentDate)) >= t.RentDate AND DATEADD(MONTH, E.n, DATEADD(DAY, T.SemiPayOne - 1, T.RentDate)) <= T.DueDate AND T.Method = 'S'
UNION 
SELECT T.ID, T.RentDate, T.DueDate, T.Method, DATEADD(MONTH, E.n, DATEADD(DAY, T.SemiPayTwo - 1, T.RentDate))
FROM @Test T
    CROSS JOIN e4 E
WHERE DATEADD(MONTH, E.n, DATEADD(DAY, T.SemiPayTwo - 1, T.RentDate)) >= t.RentDate AND DATEADD(MONTH, E.n, DATEADD(DAY, T.SemiPayTwo - 1, T.RentDate)) <= T.DueDate AND T.Method = 'S'

Open in new window


The only thing missing is including the date_rented for the semi-monthly payments.
0
 
OMC2000Commented:
you could user insert statement based on select from a join of your AGREEMENT table and a query from some large table where you could define sequence of dates by adding sequential number, id for instance to some start date


insert into my_table (
select
x.AGREEMENT , z.b from
(select AGREEMENT  ,  RENTED  ,   DUE   ,  FREQ from AGREEMENT ) x inner join
(select convert(datetime,'01.01.2000') + some_id b from some_big_table) z
on x.RENTED <= z.b and x.DUE  >= z.b )
0
 
Shaun KlineLead Software EngineerCommented:
You can create a number table and use it to generate dates.

;WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2) -- 10*100
  SELECT dateadd(week, (ROW_NUMBER() OVER (ORDER BY n)), '1/1/2000')
   FROM e3 ORDER BY n;

Open in new window


Full example here: http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
awking00Commented:
I'm afraid anything you try in this regard is going to be painfully slow. Given the two sample agreements would require 126 rows (13 months for the monthly frequency and 113 weeks for the weekly frequency). If this ratio would apply to the other 89,998 agreements, it would require about 11.34 million rows. Good luck.
0
 
lburnsinmagnoliaAuthor Commented:
Shaun,

That suggestions is almost what I need.  The exception (isn't there always one?) is that some accounts are semi-monthly frequency with the days of the month set to 2 different days (i.e.  the 1st and 16th).  So the date has to be generated for those on the fly.

;WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2) -- 10*100
  SELECT dateadd(week, (ROW_NUMBER() OVER (ORDER BY n)), '1/1/2000') as WeekDate
  , dateadd(day, (ROW_NUMBER() OVER (ORDER BY n)  * 14 ), '1/1/2000') as BiWeekDate
  , dateadd(month, (ROW_NUMBER() OVER (ORDER BY n)), '1/1/2000') as MonthDate
   FROM e3 ORDER BY n;

The semi-monthly dates are a real problem.

What about those?
0
 
Shaun KlineLead Software EngineerCommented:
If the bi-monthly will always be the 1st or 16th, use the dateadd for a month, but change the date from 1/1/2000 to 1/16/2000. You would just need to use two insert statements, one for the 1st and one for the 16th.

It may be better to just create the number table (the SQL provided but using just the ROWNUMBER portion without the DATEADD) as temp table, and use it in you insert statements with the DATEADD to calculate the dates you need.
0
 
lburnsinmagnoliaAuthor Commented:
Yeah,

That's the problem.  The dates are not always the 1st and 16th.  They vary all the way up to the 14th and 28th.  The only thing we don't allow is for a semi-monthly account to have a second day due after the 28th.  Other than that, it can vary widely.  I guess I can exclude the semi-monthly accounts and use the UNION query and then use a cursor for the semi-monthly accounts.  Here is the breakdown from a slightly older backup of the counts.  Over 3000 semi-monthly accounts

normal_method      AgrCount
W                              13
S                              3234
M                             11878
B                             48611
0
 
Shaun KlineLead Software EngineerCommented:
What is the determining factor for what days are used for the semi-monthly dates? Could you use a CASE statement to set the two semi-monthly days?
0
 
lburnsinmagnoliaAuthor Commented:
The 2 semi-monthly days are actually in the agreement table
0
 
Shaun KlineLead Software EngineerCommented:
So could you build the initial date for both? Say something like '1/' + first semi-month day (as a string) + '/2000' and use that date in the CTE query?
0
 
lburnsinmagnoliaAuthor Commented:
So you think I should add both dates on each row for all possible dates in the date work table?
0
 
SharathData EngineerCommented:
What is your SQL Server version? And it would be great if you can provide few more sample records for S and B with expected result.
0
 
lburnsinmagnoliaAuthor Commented:
I have versions 2005 thru 2012, but my customer has 2005.  So all scripting must be 2005 compatible.

Here is a sample of semi-monthly agreements.  I have the 'B' types covered with the dateadd routine.

agreementid      date_rented      due_date         Method      S     emiPayDay1      SemiPayDay2
2060139              2013-07-08       2013-09-02       S                      2                    19
2060275              2013-07-08       2013-08-06       S                      6                    22

so agreement 2060139 would need transactions starting on 7/08/13, 7/19/13, 8/2/13, 8/19/13, 9/2/13.  You have to ignore the first semipayday1 or semipayday2 on the first transaction because of the date-rented.
0
 
lburnsinmagnoliaAuthor Commented:
Sorry it took so long getting back here.  Been busy with more pressing issues.  I have marked the answer that helped the most.

Thanks!
0
All Courses

From novice to tech pro — start learning today.