SQL queries for fiscal years and periods in Dynamics GP


Often when creating reports we’re asked to show the current year vs. last year or select the current fiscal year or period as a date range for values returned. Sometimes dates can be hard-coded, especially when the fiscal year is the calendar year. But some reports can be made more dynamic by using the fiscal periods set up in Dynamics GP. It can also help simply to see what is set up in GP at times, so that you know how to best structure the report. Below are two queries that can help with this.

All fiscal years:

SELECT
   YEAR1 [Year],
   FSTFSCDY First_Day,
   LSTFSCDY Last_Day,
   NUMOFPER Number_of_Periods,
   CASE HISTORYR
     WHEN 0 THEN 'Open Year'
     WHEN 1 THEN 'Historical Year'
     END Year_Status
FROM SY40101
ORDER BY YEAR1

Sample results:

Fiscal periods for the current year:

SELECT
   D.PERIODID Period_Number,
   D.PERNAME Period_Name,
   D.PERIODDT Starting_Date,
   D.PERDENDT Ending_Date,
   D.YEAR1 Fiscal_Year
FROM SY40100 D
INNER JOIN
   SY40101 H
   ON H.YEAR1 = D.YEAR1
WHERE
   D.FORIGIN = 1 AND D.PERIODID <> 0
   and GETDATE() between H.FSTFSCDY and H.LSTFSCDY
ORDER BY D.PERIODID

You can change the line in blue above if you need to see a different year. Sample results:

9 Responses to “SQL queries for fiscal years and periods in Dynamics GP”

  1. Hello Victoria,

    I’ve been busting my head trying to figure out how to join the fiscal year and periods query to the SQL view for Payables GL distributions, so that I can show the calendar year and the calendar month of the payables. All my researching efforts always end up in this particular post. Can you enlighten me please?

    Thank you,
    Carlos

    Like

  2. Victoria,

    Sorry to ask, but you mention above you can change the line in blue to see a different year. What is the code for a different year?

    Mark

    Like

    • Mark,

      If you want to specify the year, you could use the following:

      and D.YEAR1 = 2010

      -Victoria

      Like

      • Hello Victoria,

        How would one join the Fiscal Year or Period query to the SalesTransactions view in order to get sales by Fiscal Period?

        The end result is a report in SSRS.

        Like

        • Here is an example using the SUBTOTAL from SOP30200 for the sales. That may not be the field you actually use, I am just showing how it can be done using the exact code from this blog post:

          select 
          f.Fiscal_Year, f.Period_Name,
          sum(case s.SOPTYPE when 3 then s.SUBTOTAL else -1*s.SUBTOTAL end) Sales
          from SOP30200 s
          inner join 
          (SELECT
             D.PERIODID Period_Number,
             D.PERNAME Period_Name,
             D.PERIODDT Starting_Date,
             D.PERDENDT Ending_Date,
             D.YEAR1 Fiscal_Year
          FROM SY40100 D
          INNER JOIN
             SY40101 H
             ON H.YEAR1 = D.YEAR1
          WHERE
             D.FORIGIN = 1 AND D.PERIODID <> 0) f
          on s.DOCDATE between  f.Starting_Date and f.Ending_Date
          where s.SOPTYPE in (3,4) and s.VOIDSTTS = 0
          group by f.Fiscal_Year, f.Period_Name
          order by f.Fiscal_Year, f.Period_Name
          

          -Victoria

          Like

Trackbacks/Pingbacks

  1. Calculating the number of fiscal periods between two dates - Dynamics GP Land - GP Technical Blogs - Microsoft Dynamics Community - October 10, 2012

    […] of "dynamics gp fiscal period query" sent me right to her blog. (Thank you Victoria!)https://victoriayudin.com/2010/11/24/sql-queries-for-fiscal-years-and-periods-in-dynamics-gp/I borrowed Victoria's second query and adjusted it to only return info about the current fiscal […]

    Like

  2. DynamicAccounting.net - November 29, 2010

    SQL queries for fiscal years and periods in Dynamics GP…

    Victoria Yudin has a new query available for fiscal years and periods in Dynamics GP…

    Like

  3. SQL queries for fiscal years and periods in Dynamics GP - Interesting Findings & Knowledge Sharing - November 24, 2010

    […] here to see the original: SQL queries for fiscal years and periods in Dynamics GP Tagged with: best-structure  calendar  dynamics  Dynamics […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: