>>I have two tables. MyTransactions has transactional data by date (TranDate). The other, MyValues, is non-normalized with a daily valuation by date (ValDate). The MyValues table is not transactional in the sense that a value is applied each of the days, but rather the value is displayed on each day after it is initially applied (see 05/01/2006). The basic layout is like this:
>>
>>
>>
>>MyTransactions
>>
>>AAA 01/01/2006 100
>>BBB 03/15/2006 200
>>CCC 05/23/2006 300
>>DDD 06/01/2006 400
>>EEE 06/26/2006 500
>>
>>MyValues
>>
>>FFF 01/01/2006 000
>>GGG 02/01/2006 000
>>HHH 03/01/2006 000
>>III 04/01/2006 000
>>JJJ 05/01/2006 555
>>KKK 06/01/2006 555
>>LLL 07/01/2006 555
>>
>>MyDesiredResults (added 05/01 record)
>>
>>AAA 01/01/2006 100 000
>>BBB 03/15/2006 200 000
>>JJJ 05/01/2006 000 555
>>CCC 05/23/2006 300 000
>>DDD 06/01/2006 400 000
>>EEE 06/26/2006 500 000
>>
>>Or if the MyValues table had an entry for 05/23, then I would want one record in the results with both column values like this:
>>
>>MyDesiredResults (added 05/01 record)
>>
>>AAA 01/01/2006 100 000
>>BBB 03/15/2006 200 000
>>CCC 05/23/2006 300 555
>>DDD 06/01/2006 400 000
>>EEE 06/26/2006 500 000
>>
>>I've tried various ways, but just can't seem to get the right combination of JOINS, NULLS, etc.
>>
>>
>
>Try:
>
>SELECT NVL(MyTransactions.Field1, MyValues.Field1) AS Field1,;
> NVL(MyTransactions.DateField, MyValues.DateField) AS DateField,;
> NVL(MyTransactions.Field3, 000) AS TranField3,;
> NVL(MyValues.Field3, 000) AS ValueField3;
>FROM MyTransactions;
>FULL JOIN MyValues ON MyTransactions.DateField = MyValues.DateField;
>INTO CURSOR MyDesiredResults
>
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.
