I am working on an sql report that should show the scheduled count date for each part.
1. This gives me the next count date in days: DATEDIFF(day, C.LAST_COUNT_DATE, GETDATE()) - C.COUNT_FREQ
2. I have another table that will give me the actual date that the part was counted for the current year.
3. If a part is scheduled to be counted every four years, I need to be able to display the dates that it was counted then the next three scheduled count dates.
The schedule should look like:
Part_ID ABC_CODE Count Date Next Count Date NextCountDate NextCountDate
888*FRP*66*U D 3/20/2015 3/20/2019 3/20/2023 3/20/2027
C X X X
777*568*22 A 1/2/2015 6/2/2015 1/2/2016 6/2/2016
C X X X
The 'X' and 'C' stand for Counted and not yet counted. If the item has been counted according to the appropriate count frequency, I want to show a 'C'.