August 29, 2008 at 11:12 am
This problem I am writing about is being done on SQL Server 2000.
I have been given a project to load some data that has lots of gaps in the dates. My job is to add records to the table that fills in all of the missing gaps and carry forward the previous data until either, one, a new record exists from the initial load, or two, I get to the current month.
The idea is that the calling program does not want to have to find the last entry but instead just enter a date and find the current data for that ID or customer on that date.
This is a summary table. All of the dates are effective dates, meaning they are always the first of the month without any times.
CREATE TABLE mydata(
my_idvarchar(15),
my_datedatetime,
my_customerint,
my_cust_ordersint)
INSERT INTO mydata
VALUES('10000123A', '3/1/07', 2573, 8)
INSERT INTO mydata
VALUES('10000123A', '6/1/07', 2573, 9)
INSERT INTO mydata
VALUES('10000123A', '9/1/07', 1376, 1)
INSERT INTO mydata
VALUES('10000123A', '3/1/07', 1685, 10)
INSERT INTO mydata
VALUES('10000123A', '7/1/08', 1685, 11)
After the initial data is loaded it will look like the following:
my_idmy_datemy_customermy_cust_orders
10000123A3/1/07168510
10000123A3/1/0725738
10000123A6/1/0725739
10000123A9/1/0713761
10000123A7/1/08168511
After I get done, the data should look like the following:
(the entries with an asterisk at the end are the original data)
my_idmy_datemy_customermy_cust_orders
10000123A3/1/07168510*
10000123A3/1/0725738*
10000123A4/1/07168510
10000123A4/1/0725738
10000123A5/1/07168510
10000123A5/1/0725738
10000123A6/1/07168510
10000123A6/1/0725739*
10000123A7/1/07168510
10000123A7/1/0725739
10000123A8/1/07168510
10000123A8/1/0725739
10000123A9/1/0713761*
10000123A9/1/07168510
10000123A9/1/0725739
10000123A10/1/0713761
10000123A10/1/07168510
10000123A10/1/0725739
10000123A11/1/0713761
10000123A11/1/07168510
10000123A11/1/0725739
10000123A12/1/0713761
10000123A12/1/07168510
10000123A12/1/0725739
10000123A1/1/0813761
10000123A1/1/08168510
10000123A1/1/0825739
10000123A2/1/0813761
10000123A2/1/08168510
10000123A2/1/0825739
10000123A3/1/0813761
10000123A3/1/08168510
10000123A3/1/0825739
10000123A4/1/0813761
10000123A4/1/08168510
10000123A4/1/0825739
10000123A5/1/0813761
10000123A5/1/08168510
10000123A5/1/0825739
10000123A6/1/0813761
10000123A6/1/08168510
10000123A6/1/0825739
10000123A7/1/0813761
10000123A7/1/08168511*
10000123A7/1/0825739
10000123A8/1/0813761
10000123A8/1/08168511*
10000123A8/1/0825739
This is a very small sample but, hopefully, it shows you what I am trying to do. I need to find the
quickest way to load all of these missing (gap) records. The final record count is going to be in the
millions.
The person requesting it wants to run this twice a day, at noon and midnight, so I need it as fast as possible.
All suggestions are greatly appreciated.
Thanks in advance!
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
August 29, 2008 at 1:15 pm
You probably considered this already, but can you change the query that retrieves the data by getting the max(my_date) where my_date <= InputDate for given id and customer? That way, you don't have to fill the table with what's essentially redundant data.
August 29, 2008 at 1:18 pm
Yes, I agree, that is the way the query should be done but it is out of my hands at this point. They want the redundant data.
We're actually going to have another talk about the real reason for this and I'm hoping I can persuade them to use the logic you are talking about. This would make everything much cleaner.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
August 29, 2008 at 1:58 pm
Good luck. Hopefully they will see the light. Let me know how it turns out.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply