0

I'm querying contract data where clients have contracts that get termed and reinstated. Each row on the table has the Client ID, Effective Date, and Term date of each contract so clients can show up multiple times if they have reinstated their contract. I only want to pull clients that show up with a term date and don't have a new row with an active contract.

What I'm doing right now is: selecting all clients with a term date - saving into a spreadsheet selecting all clients showing active - saving into a spreadsheet

v-lookup removing termed clients who also show up in the active spreadsheet. I know there has got to be a better way to do this.

3 Answers 3

0

I think you want something like this:

select                        -- show 
    c.Client_ID, 
    c.Effective_Date, 
    c.Term_Date
from                          -- all contracts
    contracts as c
where                         -- from clients
    not exists                -- without
    ( select 1 
      from contracts as ac    -- active contracts
      where ac.Term_Date is null
        and ac.Client_ID = c.Client_ID
    ) ;
0

It may not be an elegant solution, but you could use two selects here and a ROW_NUMBER() function in each partitioned by the client, ordered by the action date. One query would be reinstating, the other terming. Then join the two queries on client = client and ROW_NUMBER() = ROW_NUMBER(). Where you have nulls in the second query would be your currently active clients.

Edit: Reverse my thought here. Sorry, misread your need.

0

I recommend this schema design pattern. I call it History + Current.

The History table contains many rows per client; one per contract. The Current table contains only "current" info about each client -- one row per client. Your SELECT would probably need only this table.

Your design is "proper" because the current contract is not redundantly stored in two places. My design requires changing both tables when it changes. This is a small extra cost when adding the next contract, but a big gain for SELECT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.