I am trying to get a list of new products.
For that, I should look at the date they had movement. If the movement was in 2022, I consider it as new, but if the movement is before 2022, it is no longer new.
If i'm set as a condition that the date of the movement be greater than 2022, could also be returning a product that also had a movement in 2021 without knowing it.
select mstid as 'ID Movement', mstfec as 'Date Movement', prdid as 'Product'
from skmov
where prdid='*N0118'
and year(mstfec) = YEAR(GETDATE())
For example:
ID Movement Date Movement Product
49080774 2022-01-18 PH5404
49081126 2022-01-18 PH5404
49081153 2022-01-18 PH5404
49081194 2022-01-18 PH5404
49081220 2022-01-18 PH5404
49081234 2022-01-18 PH5404
49081261 2022-01-18 PH5404
49081294 2022-01-18 PH5404
49081343 2022-01-18 PH5404
49081353 2022-01-18 PH5404
49081393 2022-01-18 PH5404
49081399 2022-01-18 PH5404
ID Movement Date Movement Product
45024446 2020-11-11 *N0118
46273899 2021-03-31 *N0118
48881401 2021-12-23 *N0118
49011332 2022-01-10 *N0118
49100818 2022-01-20 *N0118
PH5404 would be considered a new product because its movements are from the current year.
*N0118 would not be a new product, although it had movements in the current year, it already had other movements in previous years.
What would be the best way to obtain only with the products that have been moving in 2022?
CodePudding user response:
You would need an inner query with a not exists clause:
select mstid as 'ID Movement', mstfec as 'Date Movement', prdid as 'Product'
from skmov s
where prdid='*N0118'
and year(mstfec) = YEAR(GETDATE())
and not exists (select 1 from skmov sI where sI.prdid = s.prdId and year(sI.mstfec) < year(getdate()))
This way you will fetch the products that has moved this year, but has no movements in an earlier year.