asked on

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

Mike Eghtebas

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

Scott Pletcher

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

qbjgqbjg

ASKER

Thanks I will try it tomorrow.

qbjgqbjg

ASKER

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.

Scott Pletcher

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

qbjgqbjg

ASKER

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.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

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

Start Free Trial

qbjgqbjg

ASKER

Thanks for all your help.