Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Running script on fiscal date or calendar date, whichever is later

    I am working on creating a new report that has to get its date from several different tables. Some of these tables update at the beginning of the fiscal month, others at the beginning of the calendar month (). My goal is to develop a stored proc that will run once a month, get all the data from the various sources, and insert into a history table. An SSRS report will generate off of the data in this history table so users can see a running history of the data. I am having trouble creating a way to determine this though. Fiscal months always start on a Sunday. We have a calendar table that translates each date into the fiscal day, fiscal month, etc. Changing the legacy scripts to run on fiscal instead of calendar is not an option.

    Idea 1:
    Wrap the script in an IF statement that will determine the date of last day of the previous calendar month, and the date of the last day of the previous fiscal month, and if current date is greater than both of those, then run the script. But this runs into issues because the current date could be in a different calendar month than fiscal month causing the idea of "previous month" to be incongruent.

    Idea 2:
    Find the current day of year, then if this is greater than last day of the previous fiscal and calendar months, run script. Issue this this is in january, fiscal day might be 2 but calendar day might be 364, so it will never run.

    An other ideas?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please define precisely what you mean by a business month. In other words if I wanted to print a century worth of your business months, how would my algorithm determine the beginning and end of each of your fiscal months?

    This is actually crucially important to answering your question. I don't know how to help you without understanding the definition of your "fiscal calendar".

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As a side note, if your calendar is arbitrary there is a simple brute force answer.

    Modify your calendar table to include columns for each day showing four additional columns: the previous calendar month, previous calendar year, previous fiscal month, previous fiscal year. This would allow you to process data with complete flexibility including making the current fiscal month also be the previous fiscal month. This is quite ugly, but I've seen this done when there were different numbers of months in the calendar year versus the fiscal year. One example was an Israeli company that used the Hebrew calendar with thirteen months for its calendar year, and the Gregorian calendar with twelve months for its fiscal year.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Please define precisely what you mean by a business month. In other words if I wanted to print a century worth of your business months, how would my algorithm determine the beginning and end of each of your fiscal months?

    -PatP
    The calendar table is at the date level. Each record contains (among other things) the date (aka FiscalDt), FiscalDayKey (unique identifier, int) FiscalMth, FiscalYr, FiscalYrMth, FiscalDay (day of the current fiscal year, 1-365), and FiscalMthEnd (which is "Y" on the last day of the fiscal month, null otherwise).

    Normally to find the beginning and end of a fiscal month, I would do the following:
    Code:
    SELECT fiscalyrmth,min(fiscaldt) AS MonthStart, max(FiscalDt) MonthEnd
    FROM PRODDW.dbo.VWDFiscalDay
    GROUP BY fiscalyrmth

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How would I get the information about each of your calendar and fiscal months for a 400 year period (one full Gregorian cycle)?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •