asked on

Mssql - Sql Query Issue

Hi,

SELECT
CNT.EMPLID, CNT.ONTRACT_NUM,CNT.START_DATE, CNT.END_DATE FROM
PS_CONTRACT CNT
INNER JOIN PS_CAATNTFY_TMP TMP ON CNT.EMPLID = TMP.EMPLID
AND CNT.START_DATE = TMP.START_DATE

-- This yields results like :
1000926 118329 2013-05-01 00:00:00.000 2013-08-30 00:00:00.000
1000926 118330 2013-05-01 00:00:00.000 2014-04-01 00:00:00.000

I want only the second row where the maximum end-date is 2014-04-01. Without having to do a second pass of the data in another query, how would I do this as part of the above query.

Any help would be greatly appreciated,

Nigluc

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DcpKing

So I gather that you are looking for the record where the end date is the maximum available end date.
Try this:

select  cnt.EMPLID, cnt.ONTRACT_NUM, cnt.START_DATE, cnt.END_DATE
from PS_CONTRACT cnt
inner join PS_CAATNTFY_TMP tmp 
on cnt.EMPLID = tmp.EMPLID
  and cnt.START_DATE = tmp.START_DATE
where cnt.END_DATE = (select max(c.END_DATE) 
                                      from PS_CONTRACT c
                                      inner join PS_CAATNTFY_TMP t
                                      on c.EMPLID = t.EMPLID 
                                         and c.START_DATE = t.START_DATE)

Open in new window

What I've added is a condition demanding that the result rows have an END_DATE equal to the largest END_DATE in the result set.

hth

Mike

PortletPaul

I'd recommend row_number() if it's available (as appari has done), alternatively I'd go with a joined subquery

SELECT
        CNT.EMPLID
      , CNT.ONTRACT_NUM
      , CNT.START_DATE
      , CNT.END_DATE
FROM PS_CONTRACT CNT
        INNER JOIN (
                    SELECT
                          CNT.EMPLID AS EID, MAX(CNT.END_DATE) as MAX_END_DATE
                    FROM PS_CONTRACT
                   ) AS MX ON CNT.EMPLID = mx.EID AND CNT.END_DATE = MX.MAX_END_DATE
        INNER JOIN PS_CAATNTFY_TMP TMP ON CNT.EMPLID = TMP.EMPLID
                        AND CNT.START_DATE = TMP.START_DATE
;

Open in new window

the disadvantage of any solution based on MAX() is that you might (very rarely) have more than one record matching to that date, but a row_number() solution can only grant the number 1 once within a partition and may also use other fields in the order by as tiebreakers to overcome that should it ever be needed.

chaau

Alternatively you can use TOP 1:

SELECT TOP 1
  CNT.EMPLID, CNT.ONTRACT_NUM,CNT.START_DATE, CNT.END_DATE FROM
  PS_CONTRACT CNT
  INNER  JOIN PS_CAATNTFY_TMP TMP ON CNT.EMPLID = TMP.EMPLID
  AND CNT.START_DATE = TMP.START_DATE
ORDER BY CNT.END_DATE DESC

Open in new window

PortletPaul

:) yes that too, if only one record is the total required

Brock

ASKER

As I begin my work day, I was so happy to find a solution to my problem.

Thank you to all,

I really appreciate it,

Nigluc