asked on

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

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Annette Wilson, MSIS

ASKER

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.