Create 4 Year Schedule

The following portion of my query shows columns that represent the first year of a 4 year report.

I will have four more sets of these columns that will reflect the count dates for the next three years.

How do I correctly calculate  the next three years?  
A parts are counted 2xs per year  - CountFREQ = 180
B parts are counted 1x per year -  countFreq =  365
C parts are counted every two years - countFreq = 730
D parts are counted every 4 years - countFreq = 1460

To get the next count date for the first year I add the count frequency to the last count date and I have a date.  

I'm not sure how to get the next three years of dates to show up correctly.


NEXT_Y1A1_COUNT =
CASE
WHEN P.ABC_CODE = 'A'
    THEN DATEADD(d, CCP.COUNT_FREQ, CCP.LAST_COUNT_DATE) END,

NEXT_Y1A2_COUNT =
CASE
WHEN P.ABC_CODE = 'A'
    THEN DATEADD(d, NEXT_Y3A1_COUNT, CCP.LAST_COUNT_DATE) END,


NEXT_Y1B_COUNT =
CASE
WHEN P.ABC_CODE = 'B'
    THEN DATEADD(d, CCP.COUNT_FREQ, CCP.LAST_COUNT_DATE) END,

NEXT_Y1C_COUNT = 
CASE
WHEN P.ABC_CODE = 'C'
   THEN DATEADD(d,CCP.COUNT_FREQ,CCP.LAST_COUNT_DATE)  END,

NEXT_Y1D_COUNT = 
CASE
WHEN P.ABC_CODE = 'D'
   THEN DATEADD(d,CCP.COUNT_FREQ,CCP.LAST_COUNT_DATE)  END,

Open in new window

Annette Wilson, MSISSr. Programmer AnalystAsked:
Who is Participating?
 
chaauCommented:
Just use a multiplier:
NEXT_Y1C_COUNT = 
CASE
WHEN P.ABC_CODE = 'C'
   THEN DATEADD(d,CCP.COUNT_FREQ*2,CCP.LAST_COUNT_DATE)  END,

NEXT_Y1D_COUNT = 
CASE
WHEN P.ABC_CODE = 'D'
   THEN DATEADD(d,CCP.COUNT_FREQ*4,CCP.LAST_COUNT_DATE)  END,

Open in new window

0
 
Annette Wilson, MSISSr. Programmer AnalystAuthor Commented:
Thank you for responding.   I was also thinking the same but I then began to consider that I may need to check to see if the part was previously counted.

Using a multiplier would be correct because I'm only creating a 4 year schedule and do not need to consider whether the part was previously counted within that time frame.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.