SQL command

I am using a SQL Command in a Crystal report. I need to pull only records with the max date. This code works to do that. My only issue is:I have some that have 2 records for the same date. In that case I need only the higher numbered PHST_POS_ID if Pct_Dist = 100. any suggestions on how to code that?

DECLARE @BeginDate DATETIME
 --Set @BeginDate = {?Begindate}
Set @BeginDate =  {ts '2014-01-01 00:00:00'}


DECLARE @EndDate DATETIME
-- Set @EndDate =  {?EndDate}
Set @EndDate =  {ts '2014-12-31 00:00:00'}


SELECT distinct
 t1.PHST_CHG_DATE as PHST_CHG_DATE
 , t1.PHST_TITLE as PHST_TITLE
 , t1.PHST_EMP_ID
 , t1.PHST_APPOINT_ID
 , t1.PHST_POS_NO as PHST_POS_NO
 , t1.PHST_POS_ID as PHST_POS_ID
 ,Pct_Dist = isnull(
                    (select top 1 z.PCT_DIST
                    from ESYFIXCD z
                    where  z.POS_ID = t1.PHST_POS_ID)
                 ,100)      
FROM  
 ESHPOSHD
   as t1
   
  INNER JOIN
 (SELECT
 Max(ESHPOSHD.PHST_CHG_DATE) as Max_DATE
 , ESHPOSHD.PHST_EMP_ID
      FROM ESHPOSHD
        GROUP By PHST_EMP_ID
 ) as t2
 on (t1.PHST_EMP_ID = t2.PHST_EMP_ID  
 and  t1.PHST_POS_ID <> 0
 and t1.PHST_CHG_DATE = t2.Max_Date
 and t1.PHST_CHG_DATE <= @EndDate)  
 order by t1.PHST_EMP_ID
qbjgqbjgConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
You need to use WITH TIES and also make order by deterministic. more on this shortly. Here is an example.

SELECT TOP (3) WITH TIES orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC;
orderid    orderdate                     custid     empid
----------- -----------------------   -----------   -----------
11077     2008-05-06 00:00:00.000  65         1
11076     2008-05-06 00:00:00.000  9           4
11075     2008-05-06 00:00:00.000  68         8
11074     2008-05-06 00:00:00.000  73         7

Open in new window


Later I will look to see how this could be applied to your project.

Please note that despite TOP(3), this query shows 4 records because they have 3rd and 4rh records have the same orderdate but...  

now, the other option to guarantee determinism is to break the ties by adding a tiebreaker that makes the ordering unique. For example, suppose you wanted the row with the greater order ID to “win” (in your case that will be  PHST_POS_ID). To do so, add orderid DESC to your ORDER BY clause, as follows...

SELECT TOP (3) WITH TIES orderid, orderdate, custid, empid 
FROM Sales.Orders 
ORDER BY orderdate DESC, orderid DESC;

orderid    orderdate                     custid     empid
----------- -----------------------   -----------   -----------
11077     2008-05-06 00:00:00.000  65         1
11076     2008-05-06 00:00:00.000  9           4
11075     2008-05-06 00:00:00.000  68         8

Open in new window

0
Scott PletcherSenior DBACommented:
Please try this re-write, adjusting the WHERE conditions, etc., as needed, of course:

SELECT
    t1.*
    ,Pct_Dist = isnull(
                     (select top (1) z.PCT_DIST
                     from ESYFIXCD z
                     where z.POS_ID = PHST_POS_ID)
                  ,100)
FROM (
    SELECT
    PHST_CHG_DATE as PHST_CHG_DATE
    , PHST_TITLE as PHST_TITLE
    , PHST_EMP_ID
    , PHST_APPOINT_ID
    , PHST_POS_NO as PHST_POS_NO
    , PHST_POS_ID as PHST_POS_ID
    , ROW_NUMBER() OVER(PARTITION BY PHST_EMP_ID ORDER BY PHST_CHG_DATE DESC) AS row_num
    FROM ESHPOSHD
) AS t1
WHERE
  t1.row_num = 1
  and t1.PHST_POS_ID <> 0
  and t1.PHST_CHG_DATE <= @EndDate
ORDER BY
  PHST_EMP_ID
0
qbjgqbjgConsultantAuthor Commented:
Thanks I will try it tomorrow.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

qbjgqbjgConsultantAuthor Commented:
I tried it. 2 issues, I need PHST_POS_ID to be the higher numbered one. If I get that then this will work for all those that have 100 in PCT_DIST. But for those that don't I need both position records.
0
Scott PletcherSenior DBACommented:
OK, try this:


SELECT
    t1.*
    ,ISNULL(z1.Pct_Dist, 100) AS Pct_Dist
FROM (
    SELECT
    PHST_CHG_DATE as PHST_CHG_DATE
    , PHST_TITLE as PHST_TITLE
    , PHST_EMP_ID
    , PHST_APPOINT_ID
    , PHST_POS_NO as PHST_POS_NO
    , PHST_POS_ID as PHST_POS_ID
    , ROW_NUMBER() OVER(PARTITION BY PHST_EMP_ID ORDER BY PHST_POS_ID DESC) AS row_num
    FROM ESHPOSHD
) AS t1
OUTER APPLY (
    SELECT TOP (1) z.PCT_DIST
    FROM ESYFIXCD z
    WHERE z.POS_ID = t1.PHST_POS_ID
) AS z1
WHERE
  (1 = CASE
      WHEN z1.PCT_DIST = 100 OR z1.PCT_DIST IS NULL THEN CASE WHEN t1.row_num = 1 THEN 1 ELSE 0 END
      ELSE 1 --if Pct_Dist is not 100, then include all rows
      END)
  and t1.PHST_POS_ID <> 0
  and t1.PHST_CHG_DATE <= @EndDate
ORDER BY
  PHST_EMP_ID,
  PHST_POS_ID
0
qbjgqbjgConsultantAuthor Commented:
Almost. For the less than 100 PCT_DIST I need the only the max date records with both positions. They will be the same date.
0
Scott PletcherSenior DBACommented:
OK, let's try this:

SELECT
     t1.*
     ,ISNULL(z1.Pct_Dist, 100) AS Pct_Dist
 FROM (
     SELECT
     PHST_CHG_DATE as PHST_CHG_DATE
     , PHST_TITLE as PHST_TITLE
     , PHST_EMP_ID
     , PHST_APPOINT_ID
     , PHST_POS_NO as PHST_POS_NO
     , PHST_POS_ID as PHST_POS_ID
     , ROW_NUMBER() OVER(PARTITION BY PHST_EMP_ID ORDER BY PHST_POS_ID DESC) AS row_num
     FROM ESHPOSHD
 ) AS t1
 OUTER APPLY (
     SELECT TOP (1) z.PCT_DIST
     FROM ESYFIXCD z
     WHERE z.POS_ID = t1.PHST_POS_ID
 ) AS z1
 WHERE
   (((z1.PCT_DIST = 100 OR z1.PCT_DIST IS NULL) AND t1.row_num = 1) OR
    (z1.PCT_DIST <> 100 AND t1.row_num BETWEEN 1 AND 2))
   and t1.PHST_POS_ID <> 0
   and t1.PHST_CHG_DATE <= @EndDate
 ORDER BY
   PHST_EMP_ID,
   PHST_POS_ID
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qbjgqbjgConsultantAuthor Commented:
Thanks for all your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.