Obtain the list of new products

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?

Answer

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.