Get Material Transactions for Previous Year

unknown

I am trying to add a derived property that will SUM certain types of material transactions for the previous year based on the current year.  If I just put the year '2020' in, I get the correct results, but I want this to be programmatic so that next year (2022) it will look at the previous year (2021).  Can anyone tell me what I am doing wrong here?

(SELECT SUM(IsNull(mat.qty, 0)) FROM matltran mat WHERE mat.item = Item AND mat.trans_type='R' AND mat.ref_type='P' AND DATEPART(YYYY,mat.trans_date) = DATEADD(year,-1,GETDATE()))

Ryan Pukanski

I think you need to do a date part on the DATEADD section of the SQL script, otherwise it returns a full date.

(SELECT SUM(IsNull(mat.qty, 0)) FROM matltran mat WHERE mat.item = Item AND mat.trans_type='R' AND mat.ref_type='P' AND DATEPART(YYYY,mat.trans_date) = DATEPART(YYYY,DATEADD(year,-1,GETDATE())))