Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2006
    Posts
    5

    Unanswered: Time Spans, Tricky SQL, My head hurts

    Data apx (5 million rows):

    Span example:
    -------------M---------------
    ___________-------------Rx-------------

    Needs to b converted to this (ignore the underscore, used for spacing):

    ---M-------|-------M & Rx----|---Rx--

    The time spans can slide either way.

    Data example:

    MemberID Eff_Date Term_Date Med_COB Rx_COB
    1 20050101 20050912 Y N
    1 20050310 20051120 N Y
    1 20060101 <null> Y N
    1 20060101 <null> N Y

    Resulting Records need to be in this format:

    MemberID Eff_Date Term_Date Med_COB Rx_COB
    1 20050101 20050310 Y N
    1 20050311 20050912 Y Y
    1 20050913 20051120 N Y
    1 20060101 <null> Y Y

    Any help with this problem would be greatly appreciated. We are running SQL2K. I like most people,would like to stay away from cursors and loops if possible.

    Thanks,
    nemesis01
    Last edited by nemesis01; 04-08-06 at 13:14. Reason: not lining up

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are going to need to explain a little better...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2006
    Posts
    5

    Elaboration

    Thanks for responding.

    These are medical coverage records. Basically a user is stored in our mainframe with an effective and term date for each coverage they have. In the project I am working on, we are only dealing with medical and pharmacy (Rx). So one user could have 2 rows, indicating medical on one and Rx on another. Like so:

    MemberID Effective_DT Term_DT Med_COB Rx_COB
    1 20050101 20051231 Y N
    1 20050101 20051231 N Y

    That is how I get the records, I need to combine intersecting time spans. Like so:

    MemberID Effective_DT Term_DT Med_COB Rx_COB
    1 20050101 20051231 Y Y

    Where it gets tricky is if the spans only intersect at certain points. For example a member could have medical coverage that termed, but they continue to Rx outside the medical coverage span.

    ------------- Medical -------------
    -------------- Rx --------------------

    Which would look like this when I get the records:

    MemberID Eff_Date Term_Date Med_COB Rx_COB
    1 20050101 20050912 Y N
    1 20050310 20051120 N Y
    1 20060101 <null> Y N
    1 20060101 <null> N Y

    Now I need to combine any intersecting time spans:

    MemberID Eff_Date Term_Date Med_COB Rx_COB
    1 20050101 20050310 Y N
    1 20050311 20050912 Y Y
    1 20050913 20051120 N Y
    1 20060101 <null> Y Y

    Thanks,
    nemesis01

  4. #4
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42

    Lightbulb Just Might Work

    Just out of curiosity do you plan to name you kids nemesis02,nemesis03,.....
    ( just kidding)

    Well i tried to solve you problem and this is what i managed come up with:

    1>Create temporary table for real data
    create table rx
    (id smallint,
    sdate datetime,
    edate datetime,
    m bit,
    p bit)

    2>Entered your real data
    insert into rx values (1,'20050101','20050912',1,0)
    insert into rx values (1,'20050310','20051120',0,1)
    insert into rx values (1,'20060101',null,1,0)
    insert into rx values (1,'20060101',null,0,1)
    select * from rx

    3>Created another table to hold new data
    Can be done on same table just delete old rows by id, insert new row
    create table trx
    (id smallint,
    sdate datetime,
    edate datetime,
    m bit,
    p bit)

    4>Run next ~40 lines as batch
    DECLARE @date1 datetime,@date2 datetime -- temporary date variables
    DECLARE @m bit,@p bit --temporary medical and pharmacy bit
    DECLARE cc cursor FOR SELECT sdate FROM rx UNION SELECT edate FROM rx
    OPEN cc --cursor is not necessary but it makes the job easier
    fetch next from cc into @date1 --initial null value(not needed)
    fetch next from cc into @date1 --first start date
    SET @m=0 --set to zero
    SET @p=0 --set to zero
    IF exists (SELECT m from rx where sdate<=@date1 and m=1)
    set @m=1 -- medical check from start date
    IF exists (SELECT p from rx where sdate<=@date1 and p=1)
    set @p=1 -- pharmacy check from start date
    fetch next from cc into @date2 --getting date2
    while @@fetch_status=0 -- loop begins
    begin
    IF(@p=1 or @m=1)--insert into able only if medical or pharmacy is set
    INSERT into trx values (1,@date1,@date2,@m,@p)
    SET @m=0 --clearing both values
    SET @p=0 --check for medical and pharmacy in between dates
    IF exists (SELECT m from rx where sdate>=@date1 and edate>=@date2 and m=1)
    SET @m=1
    IF exists (SELECT p from rx where sdate>=@date1 and edate>=@date2 and p=1)
    SET @p=1
    SET @date1=@date2 --end date is start date for next try
    fetch next from cc into @date2 --fetch new end date
    end --end of loop
    SET @m=0
    SET @p=0 -- end date onwards
    IF exists (SELECT m,p from rx where sdate>=@date2 and m=1)
    SET @m=1
    IF exists (SELECt m,p from rx where sdate>=@date2 and p=1)
    SET @p=1
    IF(@p=1 or @m=1)-- only if medical or pharmarcy record exists
    INSERT into trx values(1,@date1,null,@m,@p)
    close cc
    deallocate cc

    5> Best part
    select * from trx

    Good luck with the data apx (5 million rows), would like to hear how long it took or if there are any flaws in code or anything .

  5. #5
    Join Date
    Apr 2006
    Posts
    5

    Wow nice

    I just tucked nemesis02 in bed... lol

    That is pretty impressive. I will give it a try on Monday. About 70% of the data is exact matches of the spans. I'll let ya know the run time and exact volume.

    Thanks Again,
    Nemesis01

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wow... This triggers 10,000 questions in my mind.

    First of all, please start here. The FAQ shows how to post a question with basic information we need to give you quick, correct answers.

    You show your dates as integers. I hope that isn't how they are stored in your tables.

    I suspect that your result table is "close, but no banana" based on your posted data. My guess would be:
    Code:
    MemberID Eff_Date Term_Date Med_COB Rx_COB
    1        20050101 20050309  Y       N
    1        20050310 20050912  Y       Y
    1        20050913 20051120  N       Y
    1        20060101 NULL      Y       Y
    Only you can confirm if this guess is correct or not.

    I assume that the Y and N data is provided via some kind of download from another source. Can there be overlaps in medical/RX coverage (in other words can a user have medical coverage under one plan from 2004-01-01 through 2004-05-15 and a different plan from 2004-05-01 through 2004-12-31, giving actual medical coverage of 2004-01-01 through 2004-12-31)? Can they have "Y Y" coverage, meaning both medical and RX from a single coverage? Is there any case where an N (or any other value) will override a Y?

    These kind of questions make good challenges, but there is so much information missing from your specification that I don't know where/how to even start. Give me some breadcrumbs, and I'll be happy to take a shot at it!

    -PatP

  7. #7
    Join Date
    Apr 2006
    Posts
    5

    Elaboration

    The full monty.

    Code:
    CREATE TABLE [dbo].[Member] (
    	[BHI_Home_Plan_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Home_Plan_Product_ID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Member_ID] [char] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Consistent_Member_ID] [char] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Traceability_Field] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Member_Date_of_Birth] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Members_Current_Primary_Zip_Code] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Members_Current_Country] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Members_Current_County_Plan_Specific] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Member_Gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Member_Confidentiality_Code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Account] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Group] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Subgroup] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Coverage_Begin_Date] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Coverage_End_Date] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Member_Relationship] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Home_Plan_Product_ID_Subscriber] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Subscriber_ID] [char] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Enrollment_Eligibility_Status] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Member_Medical_COB_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Member_Pharmacy_COB_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Deductible_Category] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[MH_CD_Enrollment_Benefit] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Pharmacy_Benefit_Indicator] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[MH_CD_Benefit_Indicator] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Medical_Benefit_Indicator] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Hospital_Benefit_Indicator] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[BHI_Network_Category_Facility] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[BHI_Network_Category_Professional] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    	[Plan_Network_Category_Facility] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Plan_Network_Category_Professional] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[IncSend] [bit] NULL 
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[ODS_BLK_Member] (
    	[BHI_HOME_PLAN_ID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[HOME_PLAN_PRODUCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[CONS_MEM_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[TRACEABILITY_FIELD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MEM_DOB] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MEM_ZIP] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MEM_COUNTRY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MEM_COUNTY] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MEM_GENDER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MEM_CONFIDENTIALITY_CDE] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[ACCOUNT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[GROUP] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[SUBGROUP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[COV_BEGIN_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[COV_END_DT] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MEM_RTI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[HOME_PLAN_ID_SUB] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[SUB_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[ENRL_ELIG_ST] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MEM_MED_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MEM_PHRM_COB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[DEDUCT_CAT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MH_CD_BEN] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[PHRM_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MH_CD_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[MED_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[HOSP_BEN_IND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[BHI_CAT_FAC] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[BHI_CAT_PRF] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[PLN_CAT_FAC] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[PLN_CAT_PRF] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[FILE_IDENTIFIER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[Member] WITH NOCHECK ADD 
    	CONSTRAINT [PKC_Member] PRIMARY KEY  CLUSTERED 
    	(
    		[BHI_Home_Plan_ID],
    		[Home_Plan_Product_ID],
    		[Member_ID],
    		[Account],
    		[Group],
    		[Subgroup],
    		[Coverage_Begin_Date]
    	)  ON [PRIMARY] 
    GO
    
     CREATE  CLUSTERED  INDEX [IXC_ODS_BLK_Member] ON [dbo].[ODS_BLK_Member]([BHI_HOME_PLAN_ID], [HOME_PLAN_PRODUCT_ID], [MEM_ID], [ACCOUNT], [GROUP], [SUBGROUP], [COV_BEGIN_DT]) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[Member] ADD 
    	CONSTRAINT [DF_Member_Incremental] DEFAULT (1) FOR [IncSend]
    GO
    
     CREATE  INDEX [IX_ODS_BLK_Member_SG] ON [dbo].[ODS_BLK_Member]([SUBGROUP]) ON [PRIMARY]
    GO
    Here is the problem:

    We are dealing with 2 tables. Member and ODS_BLK_Member. Member is the final destination of the records. ODS_BLK_Member is where the data is bulk inserted in to. I can modify the ODS_BLK_Member table indexes and data types. I can not modify destination data types of Member, but I can index however. Here on in I am really only going to refer to the ODS_BLK_Member table.

    Each member gets a span of Pharmacy and a span of medical. If the spans are identical, then only one row is produced in the file I recieve. If the spans for each coverage is not identical, a row for each span is created. This span only contains a 'Y' to indicate which span it is. Single medical and single pharmacy rows are the only ones that overlap. You will never get a single pharmacy row that overlaps another single pharmacy row per member. This is the same for Medical.

    Indicator columns have values of 'Y' or 'N' and are as follows:
    PHRM_BEN_IND, MH_CD_IND (Same value all rows per member), MED_BEN_IND, HOSP_BEN_IND ('Y' Always)

    So your only changing values from row to row per member are PHRM_BEN_IND and MED_BEN_IND.

    For example (trimmed up):

    Code:
    MEM_ID   COV_BEGIN_DT COV_END_DT  PHRM_BEN_IND MH_CD_IND MED_BEN_IND HOSP_BEN_IND
    1        20050101     20050912    N            Y         Y           Y
    1        20050310     20051120    Y            Y         N           Y
    1        20060101     99991231    Y            Y         Y           Y
    2        20050101     99991231    Y            Y         Y           Y
    
    You can also look at it like so, a little more visual:
    
            ------------ Medical ------------
                   ------------- Pharmacy ----------------
    
    Needs to be represented like so:
    
            ---M---
                   -------M & P--------------
                                             -------P-------
    
    Also Possible:
    
            ------------ Medical ------------     ---------- Medical ----------
                   ------------- Pharmacy ----------------
    
    Needs to be represented like so:
    
            ---M---
                   --------M & P-------------
                                             --P--
                                                  --M & P-
                                                          ---------M-----------
    My next post will contain insert statements for the ODS_BLK_Member table with various examples. Thanks again for everyones help.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No cursors no cursors no cursors.
    Code:
    declare	@BreakDates table
    	(MemberID int,
    	BreakDate datetime)
    
    declare	@NewRecords table
    	(MemberID int,
    	Eff_Date char(8),
    	Term_Date char(8),
    	Med_COB char(1),
    	Rx_Cob char(1))
    
    insert into @BreakDates
    (select	MemberID, Eff_Date as BreakDate from [YourTable]
    UNION
    select	MemberID, coalesce(Term_Date, '99999999') as BreakDate from [YourTable])
    
    insert into @NewRecords
    	(MemberID,
    	Eff_Date,
    	Term_Date,
    	Med_COB,
    	Rx_COB)
    select	BreakDates1.MemberID,
    	BreakDates1.BreakDate as Eff_Date
    	min(BreakDates2.Breakdate) as Term_Date,
    	'N',
    	'N'
    from	@BreakDates BreakDates1
    	inner join @BreakDates BreakDates2
    		on BreakDates1.MemberID = BreakDates2.MemberID
    		and BreakDates1.BreakDate <BreakDates2.BreakDate
    group by BreakDates1.MemberID,
    	BreakDates1.BreakDate
    
    update	NewRecords
    set	Med_COB = 'Y'
    from	@NewRecords NewRecords
    	inner join [YourTable]
    		on NewRecords.MemberID = [YourTable].MemberID
    		and (NewRecords.Eff_Date >= [YourTable].Eff_Date
    			and NewRecords.Term_Date <= Coalesce([YourTable].Term_Date, '99999999'))
    where	[YourTable].Med_COB = 'Y'
    
    update	NewRecords
    set	Rx_COB = 'Y'
    from	@NewRecords NewRecords
    	inner join [YourTable]
    		on NewRecords.MemberID = [YourTable].MemberID
    		and (NewRecords.Eff_Date >= [YourTable].Eff_Date
    			and NewRecords.Term_Date <= Coalesce([YourTable].Term_Date, '99999999'))
    where	[YourTable].Rx_COB = 'Y'
    
    select	*
    from	@NewRecords
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2006
    Posts
    5

    Test Data

    Base Data:

    Code:
    INSERT INTO ODS_BLK_Member (BHI_HOME_PLAN_ID, HOME_PLAN_PRODUCT_ID, MEM_ID, CONS_MEM_ID, TRACEABILITY_FIELD, MEM_DOB, MEM_ZIP, MEM_COUNTRY, MEM_COUNTY, MEM_GENDER, MEM_CONFIDENTIALITY_CDE, ACCOUNT, GROUP, SUBGROUP, COV_BEGIN_DT, COV_END_DT, MEM_RTI, HOME_PLAN_ID_SUB, SUB_ID, ENRL_ELIG_ST, MEM_MED_COB, MEM_PHRM_COB, DEDUCT_CAT, MH_CD_BEN, PHRM_BEN_IND, MH_CD_IND, MED_BEN_IND, HOSP_BEN_IND, BHI_CAT_FAC, BHI_CAT_PRF, PLN_CAT_FAC, PLN_CAT_PRF, FILE_IDENTIFIER)
    SELECT '123','012001','1','1','ODS','19820706','19348','US','FIL','F','NON','1','1','5','20060306','99991231','1','1','1','A','B','B','1','B','Y','Y','Y','Y','2001','2001','12','12','M'
    UNION
    SELECT '123','012001','2','2','ODS','19861223','19348','US','FIL','M','NON','1','1','5','20050101','20050912','1','1','2','A','B','B','1','B','N','Y','Y','Y','2001','2001','12','12','M'
    UNION
    SELECT '123','012001','2','2','ODS','19861223','19348','US','FIL','M','NON','1','1','5','20050310','20051120','1','1','2','A','B','B','1','B','Y','Y','N','Y','2001','2001','12','12','M'
    UNION
    SELECT '123','012001','27','27','ODS','19471110','79935','US','FIL','F','NON','3','3','7','20050101','20050412','1','1','27','A','B','B','1','B','N','Y','Y','Y','2001','2001','12','12','M'
    UNION
    SELECT '123','012001','27','27','ODS','19471110','79935','US','FIL','F','NON','3','3','7','20050201','20050813','1','1','27','A','B','B','1','B','Y','Y','N','Y','2001','2001','12','12','M'
    UNION
    SELECT '123','012001','27','27','ODS','19471110','79935','US','FIL','F','NON','3','3','7','20050603','99991231','1','1','27','A','B','B','1','B','N','Y','Y','Y','2001','2001','12','12','M'
    UNION
    SELECT '123','012001','4','4','ODS','19740314','19103','US','FIL','F','NON','2','2','6','20060101','99991231','1','1','4','A','B','B','1','B','N','Y','Y','Y','2001','2001','12','12','M'
    UNION
    SELECT '123','012001','4','4','ODS','19740314','19103','US','FIL','F','NON','2','2','6','20060301','99991231','1','1','4','A','B','B','1','B','Y','Y','N','Y','2001','2001','12','12','M'
    Data after processing spans:

    Code:
    SELECT MEM_ID, COV_BEGIN_DT, COV_END_DT, MED_BEN_IND, PHRM_BEN_IND
    FROM [YourResultTable]
    
    
    MEM_ID                 COV_BEGIN_DT COV_END_DT MED_BEN_IND PHRM_BEN_IND 
    ---------------------- ------------ ---------- ----------- ------------ 
    1                      20060306     99991231   Y           Y
    2                      20050101     20050309   Y           N
    2                      20050310     20050912   Y           Y
    2                      20050913     20051120   N           Y
    4                      20060101     20060228   Y           N
    4                      20060301     99991231   Y           Y
    27                     20050101     20050131   Y           N
    27                     20050201     20050412   Y           Y
    27                     20050413     20050602   N           Y
    27                     20050603     20050813   Y           Y
    27                     20050814     99991231   Y           N
    I think if the sample data comes out like this you have totally solved it.

    blindman, thank you. I'll try yours out Monday. Did my updated post with more information change some of the rules?
    Last edited by nemesis01; 04-10-06 at 09:58.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •