Solved

Adding Records to SQL Table

Posted on 2014-01-24
14
203 Views
Last Modified: 2014-02-09
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
0
Comment
Question by:lburnsinmagnolia
14 Comments
 
LVL 15

Expert Comment

by:OMC2000
ID: 39806736
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
 
LVL 25

Expert Comment

by:Shaun Kline
ID: 39806853
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
 
LVL 31

Expert Comment

by:awking00
ID: 39806928
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
 

Author Comment

by:lburnsinmagnolia
ID: 39806935
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
 
LVL 25

Expert Comment

by:Shaun Kline
ID: 39807042
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
 

Author Comment

by:lburnsinmagnolia
ID: 39807088
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
 
LVL 25

Expert Comment

by:Shaun Kline
ID: 39807160
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:lburnsinmagnolia
ID: 39807585
The 2 semi-monthly days are actually in the agreement table
0
 
LVL 25

Expert Comment

by:Shaun Kline
ID: 39807640
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
 

Author Comment

by:lburnsinmagnolia
ID: 39807659
So you think I should add both dates on each row for all possible dates in the date work table?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39807941
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
 

Author Comment

by:lburnsinmagnolia
ID: 39811975
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
 
LVL 25

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 39813196
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
 

Author Comment

by:lburnsinmagnolia
ID: 39845378
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now