Solved

Mssql - Sql Query Issue

Posted on 2013-10-24
6
389 Views
Last Modified: 2013-10-25
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
0
Comment
Question by:Lucia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 39599440
try like this, this works in sqlserver 2005 or above,

Select   EMPLID, ONTRACT_NUM, START_DATE, END_DATE from (
SELECT
  CNT.EMPLID, CNT.ONTRACT_NUM,CNT.START_DATE, CNT.END_DATE ,
Row_number() over(partition by EMPLID order by END_DATE  desc ) RowID FROM
  PS_CONTRACT CNT
  INNER  JOIN PS_CAATNTFY_TMP TMP ON CNT.EMPLID = TMP.EMPLID
  AND CNT.START_DATE = TMP.START_DATE) A where A.RowID = 1
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39599443
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
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39599467
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:chaau
ID: 39599478
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

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39599525
:) yes that too, if only one record is the total required
0
 

Author Closing Comment

by:Lucia
ID: 39600219
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
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question