Solved

Adding Records to SQL Table

Posted on 2014-01-24
14
206 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 26

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 32

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 26

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 26

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 VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

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

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 26

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

937 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

1 Experts available now in Live!

Get 1:1 Help Now