Need solution for complex query

Hello,
I am having trouble in getting the expected results. I have two tables as below. I need Idno,transdt,cd,cycdt,amt from joining two tables.
The criteria is that if the transdt greater than same month of cycdt then we need get the next month cycdt and corresponding amount for that,
if it is less than or equal to same months cycdt then get the same months cycdt and amt. Cd is dummy field which can be anything. I am using foxpro 6.0

idno,trandt,cd
12345,04/15/2005,cd1
12345,04/15/2005,cd2
12345,04/22/2005,cd3
12345,07/03/2005,cd4
12345,09/10/2005,cd5
3421,03/05/2005,cd6
3421,05/06/2005,cd7
3421,07/04/2005,cd8
3421,07/15/2005,cd9
3421,09/15/2005,cd10

idno,cycdt,amt
12345,02/10/2005,15.43
12345,03/13/2005,40.84
12345,04/18/2005,10.10
12345,05/24/2005,13.00
12345,06/16/2005,20.89
12345,07/18/2005,12.12
12345,08/17/2005,10.89
12345,09/17/2005,12.87
12345,10/16/2005,13.89
3421,05/10/2005,15.00
3421,06/11/2005,20.00
3421,07/11/2005,14.15
3421,08/12/2005,15.54

Expected result.
12345,04/15/2005,cd1,04/18/2005,10.10
12345,04/15/2005,cd2,04/18/2005,10.10
12345,04/22/2005,cd3,05/24/2005,13.00
12345,07/03/2005,cd4,07/18/2005,12.12
12345,09/10/2005,cd5,09/17/2005,12.87
3421,05/06/2005,cd7,05/10/2005,15.00
3421,07/04/2005,cd8,07/11/2005,14.15
3421,07/15/2005,cd9,08/12/2005,15.54

I really appreciate if someone can give solution for this using a query (SQL server,Access,Foxpro) is fine.

Doing multiple steps is fine.

Answer this question

Need solution for complex query

MattontheNet
Two queries will work:

select table1.idno, trandt, cd, cycdt, amt from table1, table2 where cycdt = (select min(cycdt) from table2 where table1.idno = table2.idno and trandt <= cycdt) into cursor temp order by cd

select * from temp where (month(cycdt)-month(trandt))<=1 into cursor result

result is what you wanted:

12345,04/15/2005,"cd1",04/18/2005,10.10
12345,04/15/2005,"cd2",04/18/2005,10.10
12345,04/22/2005,"cd3",05/24/2005,13.00
12345,07/03/2005,"cd4",07/18/2005,12.12
12345,09/10/2005,"cd5",09/17/2005,12.87
3421,05/06/2005,"cd7",05/10/2005,15.00
3421,07/04/2005,"cd8",07/11/2005,14.15
3421,07/15/2005,"cd9",08/12/2005,15.54

The only issue I can see is:
12345,04/22/2005,"cd3",05/24/2005,13.00
This answer has a gap of over 1 month so I'm not sure if that is ok or not. But you did list it as a correct answer.
butterflylion
This works but gives one extra result where the gap is more than a month:

select table1.idno, trandt, cd, cycdt, amt from table1, table2 where cycdt = (select min(cycdt) from table2 where table1.idno = table2.idno and trandt <= cycdt) into cursor temp order by cd

gives the following result:

12345,04/15/2005,"cd1",04/18/2005,10.10
12345,04/15/2005,"cd2",04/18/2005,10.10
12345,04/22/2005,"cd3",05/24/2005,13.00
12345,07/03/2005,"cd4",07/18/2005,12.12
12345,09/10/2005,"cd5",09/17/2005,12.87
3421,03/05/2005,"cd6",05/10/2005,15.00
3421,05/06/2005,"cd7",05/10/2005,15.00
3421,07/04/2005,"cd8",07/11/2005,14.15
3421,07/15/2005,"cd9",08/12/2005,15.54
jocrews

>>Doing multiple steps is fine.

Whether it's fine or not you have no choice if you are using anything earlier than VFP 9 because what thisreally needs is a correlated sub-query and support for such queries was only introduced in V9. Here is the full solution for VFP 6:

I have assumed that table "idno,trandt,cd" is named "TRANDATA" and that the other table " idno,cycdt,amt" is named "CYCDATA"

*** Part 1 - get the records where the month/dates are straightforward
SELECT TD.idno, TD.cd, CD.cycdt, TD.trandt, CD.amt, ;
       TRANSFORM( TD.idno ) + TD.cd AS key ;
FROM cycdata CD, trandata TD ;
WHERE TD.idno = CD.idno ;
   AND MONTH( CD.cycdt ) = MONTH( TD.trandt );
   AND DAY( CD.cycdt ) >= DAY( TD.trandt ) ;
INTO CURSOR step1
*** Make this cursor writable
USE DBF( 'step1' ) AGAIN IN 0 ALIAS step3

*** Part 2 - Get those where we need the next month amount
*** an Uncorrelated Sub-Query IS supported in VFP 6.0 but
*** we can't UNION this because it contains a sub-query
*** relies on Part 1 (i.e. It's correlated)
SELECT TD.idno, TD.cd, CD.cycdt, TD.trandt, CD.amt, ;
      TRANSFORM( TD.idno ) + TD.cd AS key ;
FROM cycdata CD, trandata RD ;
WHERE TD.idno = CD.idno ;
   AND ( MONTH( CD.cycdt ) - MONTH( TD.trandt ) ) = 1 ;
   AND TRANSFORM( TD.idno ) + TD.cd NOT IN (SELECT key FROM Step1 ) ;
INTO CURSOR step2

*** Now merge the results
SELECT step3
APPEND FROM DBF( 'step2' )

*** And finally get the required result set
SELECT idno, trandt, cd, cycdt, amt ;
FROM step3 ;
ORDER BY cd ;
INTO CURSOR cur_result

*** Clean up
USE IN SELECT( 'step1' )
USE IN SELECT( 'step2' )
USE IN SELECT( 'step3' )
SELECT cur_result
BROWSE NORMAL
ArekBlonski

Thanks a lot guys. You made my day.
Jaldert

Unfortunately Kris is using an old version of VFP (Version 6.0) which does not support Correlated Sub-Queries. So your solution, while undoubtedly correct, is no use to him unless he can upgrade to Version 9.0 which does support them.
Need solution for complex query

Answer this question