4 Replies Latest reply: Dec 14, 2014 5:21 AM by wrvs fsbv RSS

    Determining fiscal quarter of a given date (two input files)

      ISSUE:

      how to determine which fiscal year and quarter a date falls within based on two input files

       

      SCENARIO:

      I have two input (CSV) files:

      1) timecards

      2) fiscal periods

       

      The 1st (timecard) file has a Week ID column that I covert to a date in the data load

      (i.e., Date(Date#([Week Id],'DD-MMM-YY'),'MM/DD/YYYY') AS [Week Start],)

       

      The second file, contains the following columns

      period type (e.g., "1st Quarter 2014")

      period start date (e.g., 02-01-2014

      period end date (e.g., 05-30-2014)

       

      I need to determine which fiscal year and fiscal quarter a time card Week ID falls within.

       

      Example

      If the Week ID from the timecard file = 03-04-2014

      Then the fiscal period (based on input file 2) is

      Fiscal year = 2014

      Fiscal year-quarter = 1st Quarter 2014

       

      QUESTION

      I'd prefer to determine the fiscal periods during the data load.

      How can I do this?

      NOTE: remember that I transform the string Week ID into a date during the data load also.

       

      Thanks in advance!