Fiscal Year Case Statement?

• Justin Randolph

  Old Hand

  Points: 311

  More actions

  May 10, 2018 at 8:08 am

  #403330

  Good morning everyone! I need some assistance with the following. If you have the time and if you feel like helping a fellow out I would greatly appreciate it.  

  I need some help with resolving a GETDATE() to provide FISCAL_YEAR.Example today’s date would fall into FY2018. A customer has requested her query to be scheduled to run nightly, but itcurrently has a prompt for fiscal year which would need to be replaced withyour mathematical solution to resolving the GETDATE(). 

  There is a CASE statement that you can use to derive the fiscal yearfrom GETDATE(), but I cant seem to figure it out.
  The dataset returned is from the record Ledger_KK from PeopleSoft Financials. Here is a sample data set returned and my current SQL.

  SELECT A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, A.DEPTID, A.ACCOUNT, SUM( A.POSTED_TOTAL_AMT)
  FROM PS_LEDGER_KK A
  WHERE ( A.FISCAL_YEAR = :1
     AND A.LEDGER = 'ORG_EX')
  GROUP BY  A.FISCAL_YEAR,  A.ACCOUNTING_PERIOD,  A.DEPTID,  A.ACCOUNT
  HAVING (  SUM( A.POSTED_TOTAL_AMT) <> 0)
  ORDER BY 1, 2, 3, 4

  Sample dataset currently returned.
  

  Thank you!

• Thom A

  SSC Guru

  Points: 99134

  More actions

  May 10, 2018 at 8:19 am

  #1990051

  Sounds like you need a Calendar table: http://www.sqlservercentral.com/articles/calendar/145206/

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Justin Randolph

  Old Hand

  Points: 311

  More actions

  May 10, 2018 at 8:49 am

  #1990056

  Guru, we ended up using the PS_INSTALLATION TBL for our reference for GETDATE ()) as it will always stay the same. 

   SELECT A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, A.DEPTID, A.ACCOUNT, SUM( A.POSTED_TOTAL_AMT)
FROM PS_LEDGER_KK A
WHERE ( A.FISCAL_YEAR = (SELECT CASE WHEN DatePart(Month, GETDATE()) >= 10
    THEN DatePart(Year, GETDATE()) + 1
    ELSE DatePart(Year, GETDATE())
    END
FROM PS_INSTALLATION B)
  AND A.LEDGER = 'ORG_EX')
GROUP BY A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, A.DEPTID, A.ACCOUNT
HAVING ( SUM( A.POSTED_TOTAL_AMT) <> 0)
ORDER BY 1, 2, 3, 4


• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 10, 2018 at 8:50 am

  #1990057

  Justin Randolph - Thursday, May 10, 2018 8:08 AM

  Good morning everyone! I need some assistance with the following. If you have the time and if you feel like helping a fellow out I would greatly appreciate it.  

  I need some help with resolving a GETDATE() to provide FISCAL_YEAR.Example today’s date would fall into FY2018. A customer has requested her query to be scheduled to run nightly, but itcurrently has a prompt for fiscal year which would need to be replaced withyour mathematical solution to resolving the GETDATE(). 

  There is a CASE statement that you can use to derive the fiscal yearfrom GETDATE(), but I cant seem to figure it out.
  The dataset returned is from the record Ledger_KK from PeopleSoft Financials. Here is a sample data set returned and my current SQL.

  SELECT A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, A.DEPTID, A.ACCOUNT, SUM( A.POSTED_TOTAL_AMT)
  FROM PS_LEDGER_KK A
  WHERE ( A.FISCAL_YEAR = :1
     AND A.LEDGER = 'ORG_EX')
  GROUP BY  A.FISCAL_YEAR,  A.ACCOUNTING_PERIOD,  A.DEPTID,  A.ACCOUNT
  HAVING (  SUM( A.POSTED_TOTAL_AMT) <> 0)
  ORDER BY 1, 2, 3, 4

  Sample dataset currently returned.
  

  Thank you!

  This would be easy with a calendar table, but could also be easily done without one if you provide the start and end dates of the fiscal year and what the fiscal year is for a specific range.  The calculated method works best if the fiscal year matches a "calendar" year, starts on the first of a given month and ends on the end of a month.