CODEWITHSUNDEEP
sqlsql-servert-sql
Jim Jawn
Jim Jawn

Reputation: 15

Return Max Dates from Multible Table Join

I have three tables:

Client

cid         name
----------- --------------------
1           Abe
2           Bill
3           Charlie

ClientVisit

vid         cid         service
----------- ----------- --------------------
100         1           Eval
101         1           Eval
102         2           Consult
103         3           Eval

VisitApproval

aid         vid         approved_on
----------- ----------- -----------
90          100         12/01/2014
91          101         01/10/2015
92          102         02/12/2015
93          103         NULL
94          104         03/12/2014

I need to create an output that returns the MAX date for an Eval and NULL if it does not exist:

Results
----------- -------------------- -------------------- ----------
1           Abe                  Eval                 01/10/2015
2           Bill                 NULL                 NULL
3           Charlie              Eval                 NULL

Earlier today I ran into a similar issue, with only two tables and was able to resolve the issue using a query similar to below:

  SELECT    c.cid ,
            c.name ,
            CONVERT(VARCHAR(10), MAX(COALESCE(ce1.period_end, '1999-09-09')), 101) AS Auth_End
  FROM      Clients AS c
            INNER JOIN ClientEpisode ce1 ON c.client_id = ce1.client_id
            LEFT OUTER JOIN ClientEpisode ce2 ON c.client_id = ce2.client_id
                                                 AND ce1.episode_id < ce2.episode_id

Now I have to do the same thing except I'm doing it across three tables rather than two and my mind is going to explode. What's worse, I think I'm going to have to do this three times, once for each service. I'm trying to keep it simple for now, how do I get the max date from a across a many to many join table?

Upvotes: 0

Views: 82

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31889

Try this:

SELECT
    c.cid,
    c.name,
    cv.service,
    max_date = CONVERT(VARCHAR(10), MAX(va.approved_on), 101)
FROM Client c
LEFT JOIN ClientVisit cv
    ON c.cid = cv.cid
    AND cv.service = 'Eval'
LEFT JOIN VisitApproval va
    ON va.vid = cv.vid
GROUP BY
    c.cid, c.name, cv.service

Upvotes: 2

Related Questions