data transformation based upon date range

[times29]

Hi,
How can i do below:

Example 1 Source:

10005822 10/16/2003 11/20/2008 A008 105404--- line 1
10005822 10/16/2003 3/31/2009 A999 105404 --- line 2
10005822 6/23/2008 7/5/2009 A008 124530 --- line 3
10005822 11/21/2008 8/31/2009 A008 105404 --- line 4
10005822 8/31/2009 4/16/2010 A008 105323 --- line 5
10005822 4/19/2010 12/31/9999 A999 131995 --- line 6
10005822 7/5/2010 12/31/9999 A008 131995 --- line 7



Holder:A008

Employee: 105404

Hold the position

10/16/2003 till 11/20/2008(line 1) and then 11/21/2008 till 8/31/2009 (line 4)

Employee:124530

Hold the position

6/23/2008 till 7/5/2009 (line 3)

Employee:105323

Hold the position

8/31/2009 till 4/16/2010 (liine 5)

Employee:131995

Hold the position


7/5/2010 till 12/31/9999 (line 7)


Sub holder: A999

Employee: 105404
Hold the position
10/16/2003 till 3/31/2009 (line 2)

Employee: 131995
Hold the position

4/19/2010 till 12/31/9999 (line 6)


Digram to show above example








2003 2008 2009 2010 9999
1 2 3 4 1 2 3 4 1 2 3 4 12 3 4
Holder:A008 ---------------------------------
Line 1(105404) Line 4(105404)

-----------------
Line 3(124530)
--------------
Line 5(105323)
---------------------------------------------------------
Line 7(131995)






Subholder:A999
------------------------
Line 2 (105404)
-------------------------------------------
Line 6(131995)


1) Hold=Subs for date range 10/16/2003 to 3/31/2009 for 105404 Hold (Match Line 1 ,4 with Line 2)

2) Hold<>Subs for date range 6/23/2008 till 7/5/2009 for 124530 (Match Line 3 and Line 2)
(differnt employee so Hold<>Subs for 6/23/2008 to 3/31/2009 why 3/31/2009 as there is gap or no overlap for subholder from (Line 2) to (Line 6))

3)No Substan again for date range 6/23/2008 till 7/5/2009 for 124530 employee found Hold<>Subs for 6/23/2008 to 3/31/2009 but for 4/1/2009 till
7/5/2009 there is no Sub Holder (Match Line 3 to Line 2 and 6 and there is gap between that period so No Substantive)

4) No Substan again for 1/21/2008 till 8/31/2009 (line 4) for 105404
(Match Line 4 to Line 2 and 6 and there is gap between that period Between 4/1/2009 to 8/31/2009 so No Substantive)


5) 8/31/2009 till 4/16/2010 (liine 5) Employee:105323 there is no sub holder during this time range

(Match Line 5 to Line 2 and 6 and there is gap between that period so No Substantive)

6) For range 4/17/2010 to 4/19/2010 there was no holder and no sub holder

(No match found between hold and sub for 4/17/2010 to 4/19/2010 )

7) For 4/20/2010 to 7/4/2010 there was Sub holder (line 6) and no holder for 31995

(Match Line 7 to Line 6 and there is gap between that period so No Substantive )

8) From 7/5/2010 to 12/31/9999 Hold=Sub as per line 7

(Match Line 7 to Line 6 and period match so Hold=Subs)


POSITION_TCD POSI_HOLDER SUBS_OWNER EMP_ASSI_TYPE START_DT END_DT

10005822 105404 105404 Hold=Subs 10/16/2003 3/31/2009
10005822 124530 105404 Hold<>Subs 6/23/2008 3/31/2009
10005822 124530 No Substantive No Substan 4/1/2009 7/5/2009
10005822 105404 No Substantive No Substan 4/1/2009 8/31/2009
10005822 105323 No Substantive No Substan 8/31/2009 4/16/2010
10005822 No Holder No Substantive No Substan 4/17/2010 4/19/2010
10005822 No Holder 131995 No Substan 4/20/2010 7/4/2010
10005822 131995 131995 Hold=Subs 7/5/2010 12/31/9999

[asorrell]

Not to be ugly about it, but this forum exists to help people with specific technical or logical issues, not to design and/or write an entire job.

Now that you've given us the specifications you are working under, please tell us what particular issues you need assistance with.

[times29]

Hi,
Let me rephrase the question

i have record with range 2003-1-10 to 2012-12-31 from table A

and i have two records from table B

1)2003-1-10 to 2005-1-31
2)2013-1-1 till 9999-12-31

i want to compare table A with B
1) if table B range within 2003-1-10 to 2005-1-31 A range the Arange =Brange
2) if table B range not within 22013-1-1 till 9999-12-31 A range the Arange <> Brange

how can i do this

[ray.wurlod]

I'm not totally clear as to your actual requirement, but will offer these thoughts around the two questions in your most recent post.

1) Wholly within?

Code: Select all

(aRange.lowvalue >= bRange.lowValue) AND (aRange.highValue <= bRange.highValue)

2) Direct comparison

Code: Select all

(bRange.lowValue >= 2013-01-01) AND (bRange.highValue >= 2013-01-01) AND (bRange.lowValue <= 9999-12-31) AND (bRange.highValue <= 9999-12-31)