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