SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY emplid,payplan ORDER BY datepayend DESC,ContractPeriod DESC) AS Seq,
COUNT(CASE WHEN contractperiod IN ('UA','UB') THEN 1 ELSE NULL END) OVER (PARTITION BY emplid,payplan) AS Occ,
*
FROM Table
)t
WHERE Seq=1
OR Occ<=1
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
how to select longest and active row
Posted - 08/07/2012 : 12:07:51
