Post reply

Comparing and extracting data in one table

  • December 9, 2009 at 3:08 am

    #385910

    Good day all

    I can not understand why i just cant get this to work... maybe its the sleep deprivation! Here is my problem

    I have a table called ebtcollections that consists of the following columns

    ---ClientNo, ContractNo, InstallmentNo, Amount, EBTDate , Status---

    Now i need to extract the entries where the clientno , Contractno and ebtdate are the same but the installmentno differs. Basically its a client with a loan with multiple installments that if the ebtdate is the same for any 2 or more installments of the same contract, i need to display them.

    Please can someone help!

  • December 9, 2009 at 3:32 am

    #1089718

    SELECT ClientNo, ContractNo, InstallmentNo, Amount, EBTDate, [Status]

    FROM MyTable m

    INNER JOIN (

    SELECT clientno, Contractno, ebtdate

    FROM MyTable

    GROUP BY clientno, Contractno, ebtdate

    HAVING COUNT(*) > 1) d

    ON d.clientno = m.clientno

    AND d.ContractNo = m.ContractNo

    AND d.ebtdate = m.ebtdate

    You could also do this using the windowing function ROW_NUMBER()

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • December 9, 2009 at 4:05 am

    #1089735

    Thanks a lot Chris! Really should have gotten this on first try but as i say... sleep deprivation!!! My Brain is fried but now i am done for now and going to bed!! Thanks again!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply