MAX RCV_DATE issue

Transact-SQL
1

On the last line, before the "from" statement, if sd.QTY_ORDERED does not equal sd.QTY_SHIP, I want to query the STOCK table and pull in the cost from the line with the MAX(most recent) RCV_DATE. Currently, I'm just pulling in the MAX cost per part number. (I'm not that great at sql yet). "cmp_pnm_auto_key_subp" is the part number key.

select sh.SO_NUMBER
, sh.DUE_DATE
, sh.OPEN_FLAG
, sh.SYSUR_NAME
, sh.cmp_cmp_auto_key
, sh.cmp_soh_auto_key
, sh.COMPLETE_SHIP
, sd.ITEM_NUMBER
, sd.COND
, sd.QTY_ORDERED
, sd.QTY_SHIP
, sd.UNIT_COST
, case when sh.cmp_cmp_auto_key = 'AP:702' then 0
when sh.cmp_cmp_auto_key = 'AP:6070' then 0
when sh.cmp_cmp_auto_key = 'AP:5095' then 0
else sd.UNIT_PRICE end as [UNIT_PRICE]
, sd.EXT_PRICE
, sd.ENTRY_DATE
, isnull(sd.NEXT_SHIP_DATE, sd.entry_date) as [Next Ship Date]
, sd.QTY_DELIVERED
, sd.QTY_INVOICED
, sd.QTY_RESERVED
, sd.SHIP_DATE
, sd.ROUTE_DESC
, sd.cmp_sod_auto_key
, sd.cmp_pnm_auto_key_subp
, sd.PN
, sh.SHIP_NAME
, CASE when sd.QTY_ORDERED = sd.QTY_SHIP then sd.UNIT_COST else L.Max_Cost end as [Cost]

from SO_HEADER sh
join SO_DETAIL sd on (sh.cmp_soh_auto_key = sd.cmp_soh_auto_key)
OUTER APPLY
> (
> SELECT MAX(COST) AS Max_Cost
> FROM STOCK L
> WHERE L.cmp_pnm_auto_key_subp = sd.cmp_pnm_auto_key_subp
**> **
**> **
> ) AS L

where sh.ENTRY_DATE > DATEADD(yy,-2,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))
and sh.cmp = 'AP'
and sd.route_desc = 'Part Sale'

Changing the OUTER APPLY to this only gives me null values

OUTER APPLY
(
SELECT max(RCV_DATE) as [RCV Date]
, COST AS Max_Cost
FROM STOCK L
WHERE L.cmp_pnm_auto_key_subp = sd.cmp_pnm_auto_key_subp
and Rcv_Date =(SELECT MAX(rcv_date) FROM STOCK WHERE CMP = 'ap')
group by Cost

) AS L

2

the outer apply join will run that select for every row and is not efficient. You first need to get the max date for each company and then get the Cost and cmp_pnm_auto_key_subp. Without DDL and SampleData, I can give general approach

You can do left outer join to this and then use case statement to select l.MaxCost where needed.

Left join ( select cmp, RCV_Date, Cost, cmp_pnm_auto_key_subp
from (
select CMP, RCV_Date, Cost, cmp_pnm_auto_key_subp, Row_number() over (partition by CMP order by RCV_Date desc) as rownum
from Stock) s
where RowNum = 1) l
on l.cmp_pnm_auto_key_subp = s.cmp_pnm_auto_key_subp

3

Thanks for your help. I'm getting this error. Sorry as I'm not great in sql yet.

Capture
Capture1148×461 62.6 KB

4

I used s.cmp_pnm_auto_key_subp. It looks like it should be sd.cmp_pnm_auto_key_subp. It's just the aliases. How to join from 1 table to the other

1 Like
5

I would still use the OUTER APPLY - with a TOP 1.

SELECT ...
  FROM ...
 OUTER APPLY (SELECT TOP 1
                     *  -- list the actual columns
                FROM dbo.STOCK s
               WHERE s.cmp_pnm_auto_key_subp = sd.cmp_pnm_auto_key_subp
               ORDER BY
                     s.RCV_Date desc
             ) As l
 WHERE ...
1 Like
6

Perfect, this worked. Mike brought up the fact that with outer apply, it is not efficient because it runs on every row. Since my data is will always be less than 100,000 rows, I'm not too concerned about the delay. Thank you

7

Thank you for your time and help!

8

The way your OUTER APPLY was constructed is the problem - not the fact that it was an outer apply. Your original query pulled the MAX received date and also included a GROUP that was not necessary.

Using a TOP 1 will be a bit more efficient - even though it is executed for each row, SQL Server can optimize the query since it knows there will only be a single row returned.

1 Like