Home » SQL & PL/SQL » SQL & PL/SQL » Algorithm for date events comparision (merged topics)
Algorithm for date events comparision (merged topics) [message #238398] Thu, 17 May 2007 16:05 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
I have 2 tables with different date spans. Need to compare them and split them into multiple date spans ? Ideas (any algorithm ).

e.g. 
TABLE_SOURCE 
RECORD_KEY 
FROM_DT 
TO_DT 
RECORD_VALUE 

TABLE_DESTINATION 
RECORD_KEY 
FROM_DT 
TO_DT 
RECORD_VALUE 


The primary key is RECORD_KEY, FROM_DT for both the tables.

Also once an entry is already made for the TABLE_DESTINATION for a given FROM_DT it cannot be removed. Thats the business part of the table.

The algorithm should read the table_source and compare with table_destination and create multiple date spans in a o/p file. The data value in source table is assumed to be the correct value. Dates can never overlap each other in final o/p.

Example:
TABLE_DESTINATION  (current values)
RECORD_KEY    FROM_DT     TO_DT         RECORD_VALUE 
1000          1/1/2007    12/31/2007        $50
1000          1/1/2008    12/31/2008        $100
1000          1/1/2009    12/31/2009        $35

TABLE_SOURCE 
RECORD_KEY    FROM_DT     TO_DT         RECORD_VALUE 
1000          6/1/2006    6/31/2007        $120
1000          6/1/2009    6/31/2010        $80

Result of comparision should look like below:(table or file)
RECORD_KEY    FROM_DT     TO_DT         RECORD_VALUE 
1000          6/1/2006    12/31/2006       $120 
1000          1/1/2007    06/31/2007       $120 
1000          7/1/2007    12/31/2007       $50 
1000          1/1/2008    12/31/2008       $100
1000          1/1/2009    06/31/2009       $35 
1000          1/1/2009    05/31/2009       $35 
1000          6/1/2009    6/31/2010        $80 



The assumption is the $ value is source is accurate and must be used, the FROM_DT record in destination table cannot be removed (business reason) and changed (its Primary key).

So the date ranges are altered and the $ from source is used for the date spans that applies.

Thank you
Yog Smile
Re: Algorithm for date events comparision [message #238434 is a reply to message #238398] Fri, 18 May 2007 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why this line?
1000          1/1/2009    05/31/2009       $35 

Regards
Michel
Re: Algorithm for date events comparision [message #238509 is a reply to message #238434] Fri, 18 May 2007 06:47 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
Thank you Michel.

DATE FROM TRUSTED SOURCE
1000          6/1/2009    6/31/2010        $80

ALREADY IN DESTINATION TABLE
1000          1/1/2009    12/31/2009        $35


1/2/2009 - 05/31/2009 - $20 => Use existing value already there in table
6/1/2009 - 12/312009 - $80 => Use new value that comes from trusted source.

So the data is splitted into multiple date ranges based on accuracy of data which is received Smile.

Thank you,
Yog
Re: Algorithm for date events comparision [message #238511 is a reply to message #238398] Fri, 18 May 2007 06:50 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
sorry that was an error.

DATE FROM TRUSTED SOURCE
1000          6/1/2009    6/31/2010        $80

ALREADY IN DESTINATION TABLE
1000          1/1/2009    12/31/2009        $35


1/1/2009 - 05/31/2009 - $35 Use existing value already there in table
6/1/2009 - 06/31/2009 - $80 Use new value that comes from trusted source.
7/1/2009 - 12/31/2009 - $80 Use existing value already there in table
Re: Algorithm for date events comparision [message #238811 is a reply to message #238511] Sun, 20 May 2007 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now why this one?
1000          7/1/2007    12/31/2007       $50 

I still don't understand the algorithm.
Please post for each line of the result why it is in the result.

Regards
Michel

[Updated on: Sun, 20 May 2007 02:52]

Report message to a moderator

Re: Algorithm for date events comparision [message #238854 is a reply to message #238398] Sun, 20 May 2007 10:15 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
thank you michel.

Example:

TABLE_SOURCE (received from another system and has updated values)
RECORD_KEY    FROM_DT     TO_DT         RECORD_VALUE 
1000          6/1/2006    6/31/2007        $120
1000          6/1/2009    6/31/2010        $80


TABLE_DESTINATION  (current values in table)
RECORD_KEY    FROM_DT     TO_DT         RECORD_VALUE 
1000          1/1/2007    12/31/2007        $50
1000          1/1/2008    12/31/2008        $100
1000          1/1/2009    12/31/2009        $35


Primary Key (RECORD_KEY, FROM_DT)
Once a record entry is already there in TABLE_DESTINATION, it
cannot be deleted. FROM_DT is part of primary key and cannot
be changed. TO_DT can be updated.

Algorithm:
Compare TABLE_SOURCE and TABLE_DESTINATION and create IN (insert)
and update (UP) events to update in TABLE_DESTINATION)

Result of comparision should look like below:(table or file)
This is run through a process that inserts or updates the
TABLE_DESTINATION.

RESULTS:
RECORD_KEY    FROM_DT     TO_DT         RECORD_VALUE 	EVENT 
1000          6/1/2006    12/31/2006       $120 	 IN
1000          1/1/2007    06/31/2007       $120 	 UP
1000          7/1/2007    12/31/2007       $50 		 IN 
1000          1/1/2008    12/31/2008       $100		 UP 
1000          1/1/2009    05/31/2009       $35 		 UP
1000          6/1/2009    06/31/2009       $80		 IN  
1000          7/1/2009    12/31/2009       $80 		 IN


Description:
TABLE_SOURCE is the data received from another system and is
considered the trusted source. TABLE_DESTINATION is the current
system and it already can have data in it.

The primary key is
RECORD_KEY and FROM_DT and once an entry is already there in the system it cannot be deleted.
It can only be voided but that is not part of current algorithm.

What this algorithm needs to acheive is compare the values from the old system to the new system and create update or insert events.
This is done because an entry in the new system cannot be deleted.

Lets compare the first record for example.
Trusted source says the value is $120 from
6/1/2006 - 6/31/2007. In the new system there
is already an entry for 1/1/2007 - 12/31/2007
for a value of $50 and the comparision leads in an
overlap. In this case it should be split into 3 records
and entered into the new system

1000          6/1/2006    12/31/2006       $120 	 IN
1000          1/1/2007    06/31/2007       $120 	 UP 


(note this is because the record with 1/1/2007 cannot be deleted)
1000          7/1/2007    12/31/2007       $50 		 IN 

(an old value of $50 is passed since the $$ amount remains the
same for this period).

Thank you!!
Compare dates [message #239204 is a reply to message #238398] Mon, 21 May 2007 14:01 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
How do we compare dates as in example shown below:

Example1:
DESTINATION_TABLE(current values)
KEY EFF_DT TERM_DT RECORD_VALUE
1000 1/1/2007 12/31/2007 $50
1000 1/1/2008 12/31/2008 $100
1000 1/1/2009 12/31/2009 $35

SOURCE_TABLE
KEY EFF_DT TERM_DT RECORD_VALUE
1000 6/1/2006 6/31/2007 $120

O/P Generated
KEY EFF_DT TERM_DT RECORD_VALUE
1000 6/1/2006 12/31/2006 $120
1000 1/1/2007 06/31/2007 $120
1000 7/1/2007 12/31/2007 $50
1000 1/1/2008 12/31/2008 $100
1000 1/1/2009 12/31/2009 $35

Need to Compare the EFF_DT from the SOURCE_TABLE to the date ranges (EFF_DT - TERM_DT) in the DESTINATION_TABLE and come up with the O/P as shown above.

i.e. If there is an overlap, need to break down the dates so its clean.

As we can see from the example since June 2006 - June 2007 overlaps it is split as
Jun 2006 - Dec 2006
Jan 2007 - Jun 2007
Jun 2007 - Dec 2007

The records are splitted this way because once an entry is already there in the table, it cannot be deleted.

Thank you!
Yog
Re: Compare dates [message #239205 is a reply to message #239204] Mon, 21 May 2007 14:03 Go to previous message
yog_23
Messages: 79
Registered: March 2007
Member
formatted data:

Example1:
DESTINATION_TABLE (current values)
KEY           EFF_DT     TERM_DT         RECORD_VALUE 
1000          1/1/2007    12/31/2007        $50
1000          1/1/2008    12/31/2008        $100
1000          1/1/2009    12/31/2009        $35

SOURCE_TABLE 
KEY          EFF_DT     TERM_DT         RECORD_VALUE 
1000          6/1/2006    6/31/2007        $120

O/P Generated 
KEY           EFF_DT     TERM_DT      RECORD_VALUE 
1000          6/1/2006    12/31/2006       $120 
1000          1/1/2007    06/31/2007       $120 
1000          7/1/2007    12/31/2007       $50 
1000          1/1/2008    12/31/2008       $100
1000          1/1/2009    12/31/2009       $35 
Previous Topic: View
Next Topic: SQL Notes
Goto Forum:
  


Current Time: Wed Dec 07 10:55:14 CST 2016

Total time taken to generate the page: 0.07273 seconds