>>>>Ok, played around with that a bit and I've decided that I'm not going to try and do it in one SQL, but just try to get what I need with a second one.
>>>>
>>>>Initial result from first SQL:
>>>>
>>>>

>>>>DATEFIELD1  DCONTRIB   DEFFECTIVE     FIELD1     NPREMPAID   NITDSURRAM
>>>>11/02/04    11/02/04   .NULL.          8500.00    8500.00    .NULL.
>>>>11/02/04    11/02/04   .NULL.         10800.00   10800.00    .NULL.
>>>>11/02/04    11/02/04   .NULL.         14100.00   14100.00    .NULL.
>>>>11/02/04    11/02/04   .NULL.         14796.52   14796.52    .NULL.
>>>>11/02/04    11/02/04   02/11/05       14100.00   14100.00    85164.30
>>>>11/02/04    11/02/04   02/14/05       14100.00   14100.00    85164.30
>>>>11/02/04    11/02/04   02/15/05       14100.00   14100.00    85164.30
>>>>

>>>>
>>>>Need a second SQL to get this desired results (this is what I can't get to work right now):
>>>>
>>>>

>>>>DATEFIELD1  DCONTRIB   DEFFECTIVE     FIELD1     NPREMPAID   NITDSURRAM
>>>>11/02/04    11/02/04   02/11/05       14100.00   14100.00    0.00
>>>>02/11/05    11/02/04   02/11/05       85164.30       0.00    85164.30
>>>>

>>>>
>>>>Really what I need is DateField1 and Field1, but I thought it would be easier to show them all.
>>>
>>>
>>>
>>>How you decide which records you want?
>>>From where DateField1 become 02/11/05?
>>
>>Ok, good point. Since the 2nd table is non-normal, I really need a record created from the first occurance where there is a non-zero value in the NITDSURRAM field, or another record if that value changes to a another non-zero value.
>
>But in your desired result first value of NITDSURRAM is zero?

Yes. It is zero until it is applied, then it is that value until it changes.

>Also I don't see any record that begins with DATEFIELD1 = 02/11/05

The 2nd record is using the dEffective date, the 1st record is using the dContrib. Essentially, a new record (transaction) is created when the NITDSURRAM becomes non-zero, or changes after that.