DSXchange

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

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.

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

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? 2) Direct comparison