0

I am trying to update (using Inner joins for three tables) item stats STAT for table IM_ITEM by highlighting items that sold less than 12 as "D" (Discontinue).

The 2nd table PS_TKT_HIST_LIN has the Quantity sold column QTY_SOLD for each item on each day and the date column BUS_DAT.

I also need a third table IM_INV to filter the data, I need to say the last received date LST_RECV_DAT for these items is earlier than "2019-01-01" and last sales date LST_SAL_DAT is after "2019-01-01". I used the following code

 UPDATE M
    SET M.STAT = 'D'
FROM
    dbo.IM_ITEM AS M
INNER JOIN
    IM_INV AS N
ON
    M.ITEM_NO = N.ITEM_NO
INNER JOIN
    dbo.PS_TKT_HIST_LIN S`
ON
    M.ITEM_NO  = S.ITEM_NO
WHERE
    CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, N.LST_RECV_DAT))) <= '2019-01-01'
AND CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, N.LST_SAL_DAT))) >= '2019-01-01'
AND M.STAT = 'A' 
AND SUM(case when DATEPART(YYYY, (BUS_DAT)) = DATEPART(YYYY, DATEADD(YYYY, -1, getdate()))
        AND DATEPART(yyyy, (BUS_DAT)) = DATEPART(yyyy, DATEADD(YYYY, -1, getdate()))
        then qty_sold else 0)<12

It comes with an error Any advise please

Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56

2 Answers2

1

You should use HAVING clause instead of Sum in where.

You can use CTE to achieve the value, then update accordingly.

;with cte as(
  select ITEM_NO, ..
  from ..
  group by ITEM_NO
  having  .. < 12
)

update M
set SET M.STAT = 'D'
from dbo.IM_ITEM AS M
inner join cte on M.ITEM_NO = cte.ITEM_NO
Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
  • @Bahaa Gerges Does this answer your question? Let me know if you need any help – Nguyễn Văn Phong Feb 25 '20 at 07:20
  • Thank you for your reply Phong, it still show error message in the parentheses for calculating the quantitly sold over last year. Any better recomenedation for this formula please: SUM(case when DATEPART(YYYY, (BUS_DAT)) = DATEPART(YYYY, DATEADD(YYYY, -1, getdate())) AND DATEPART(yyyy, (BUS_DAT)) = DATEPART(yyyy, DATEADD(YYYY, -1, getdate())) then qty_sold else 0)<12 – Bahaa Gerges Feb 25 '20 at 21:30
  • You should give us your sample data, expected result and your code – Nguyễn Văn Phong Feb 25 '20 at 22:12
  • I only want to calculate the quantity sold during last year for each item and if the quantity is < 12 will change the status of these items to D if it is >= 12 the status will be A. The table contains the quanity sold called "PS_TKT_HIST" and quantity sold is "QTY_SOLD" The table to update called "IM_ITEM" and status filed called "STAT". The inner join will be IM_ITEM.ITEM_NO = PS_TKT>HIST.ITEM_NO – Bahaa Gerges Feb 28 '20 at 05:11
0

You can't use an aggregate function in where clause unless defined under subquery.

Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
  • Thank you Prashant. Could you give more details? it will help if you re-write the code in the correct way you think – Bahaa Gerges Feb 25 '20 at 22:05