Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2006
    Posts
    6

    Unanswered: transdt greater than same month of cycdt (was "Complex query")

    Hello,
    I am having trouble in getting the expected results. I have two tables as below. I need Idno,transdt,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.

    table1
    idno,trandt
    12345,04/15/2005
    12345,04/22/2005
    12345,07/03/2005
    12345,09/10/2005
    3421,03/05/2005
    3421,05/06/2005
    3421,07/04/2005
    3421,07/15/2005

    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,12345,04/18/2005,10.10
    12345,04/22/2005,12345,05/24/2005,13.00
    12345,07/03/2005,12345,07/18/2005,12.12
    12345,09/10/2005,12345,09/17/2005,12.87
    3421,05/06/2005,3421,05/10/2005,15.00
    3421,07/04/2005,3421,07/11/2005,14.15
    3421,07/15/2005,3421,08/12/2005,15.54


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

    Thanks

  2. #2
    Join Date
    Feb 2006
    Posts
    6

    oracle to sqlserver

    Hello guys I was able to get make a query in Oracle but I really need it in SQL server equivalent. I am stuck at over() function and I am using SQL server 8.0. Can some one convert this for me.


    SELECT IDNO2,TRANDT,IDNO1,CYCDT,AMT FROM
    (SELECT top 1 IDNO2,TRANDT,IDNO1,CYCDT,AMT,

    COUNT(TRANDT) OVER(PARTITION BY TRANDT ORDER BY 1) CNT

    FROM
    ( SELECT T1.IDNO IDNO2,T1.TRANDT,T2.IDNO IDNO1,T2.CYCDT,T2.AMT
    FROM tab1 T1,tab2 T2 WHERE T1.IDNO=T2.IDNO ) a
    WHERE (month(trandt) = month(cycdt) AND day(trandt) < day(cycdt)) OR
    (month(trandt)+1 = month(cycdt))
    --ORDER BY 1,2,4
    ) b
    WHERE month(trandt) = month(cycdt) ORDER BY 1,2,4
    OR CNT=1 ORDER BY 1,2,4


    Quote Originally Posted by misstryguy
    Hello,
    I am having trouble in getting the expected results. I have two tables as below. I need Idno,transdt,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.

    table1
    idno,trandt
    12345,04/15/2005
    12345,04/22/2005
    12345,07/03/2005
    12345,09/10/2005
    3421,03/05/2005
    3421,05/06/2005
    3421,07/04/2005
    3421,07/15/2005

    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,12345,04/18/2005,10.10
    12345,04/22/2005,12345,05/24/2005,13.00
    12345,07/03/2005,12345,07/18/2005,12.12
    12345,09/10/2005,12345,09/17/2005,12.87
    3421,05/06/2005,3421,05/10/2005,15.00
    3421,07/04/2005,3421,07/11/2005,14.15
    3421,07/15/2005,3421,08/12/2005,15.54


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

    Thanks

  3. #3
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47

    Thumbs up

    select *,
    (select top 1 t2.cycdt from table2 t2 where t2.idno = t1.idno and t1.trandt <= t2.cycdt order by cycdt) as cycdt,
    (select top 1 t2.amt from table2 t2 where t2.idno = t1.idno and t1.trandt <= t2.cycdt order by cycdt) as amt
    from table1 t1
    -----------------
    KH


  4. #4
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    This is on SQL Server
    -----------------
    KH


  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oracle's Over predicate/function has no corresponding function in SQL. The Over function works on the result set, allowing you to partition the result set into groups. This kind of behavior is best implemented in an N-tier server, not on the database.

    There are set based solutions for your problem, but right now I'm too tired to work one out for you... I'd probably miss something, and give you only half a solution.

    If nobody offers a solution first, I'll try to work on it tomorrow if I can.

    -PatP

  6. #6
    Join Date
    Feb 2006
    Posts
    6

    Works great

    This works great. I am going to try this one on a 3 million records table.

    Quote Originally Posted by khtan
    select *,
    (select top 1 t2.cycdt from table2 t2 where t2.idno = t1.idno and t1.trandt <= t2.cycdt order by cycdt) as cycdt,
    (select top 1 t2.amt from table2 t2 where t2.idno = t1.idno and t1.trandt <= t2.cycdt order by cycdt) as amt
    from table1 t1

  7. #7
    Join Date
    Feb 2006
    Posts
    6

    Set based solution

    I am also interested in knowing the set based solution. It will be greate if you can post that too.

    Quote Originally Posted by misstryguy
    This works great. I am going to try this one on a 3 million records table.

  8. #8
    Join Date
    Feb 2006
    Posts
    6

    Problem with query

    I am having problems with the given solution. Its not working in some cases like
    1. if there is no corresponding cycdt for the month of transdt and no next month cycdt for the transdt.

  9. #9
    Join Date
    Feb 2006
    Posts
    6

    Complex Query

    The query given is not working properly. I appreciate if someone can post a solution.

  10. #10
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    can you post some sample data and expected result where the query is not working ?
    -----------------
    KH


  11. #11
    Join Date
    Jun 2004
    Posts
    50
    I've always found that SQL Server user defined functions can solve all sorts of problems.
    Monk
    The person who confesses ignorance shows it once; the person who conceals it shows it many times.

Posting Permissions

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