8 Replies Latest reply: Aug 26, 2014 2:09 PM by 2713822 RSS

    Selecting fields based on FiscaL Year and Period

    2713822

      The table I'm using has an amount field, a fiscal year, and a period field (among other fields).  I need to select the amount field twice based on the fiscal year and period fields.  The issue I'm having is how to write the SQL to select the record with fiscal year 2014 and period 10 for the prior amount.  I also need to make sure that it works for 2014 / 01 and the prior period is 2013 / 12.  Thanks for your help......

       

      SELECT

      a.business_unit,

      a.amount as CURRENT_AMOUNT,

      a.amount as PRIOR_AMOUNT,

      a.fiscal_year as FY,

      a.period as AP

       

      FROM Asset_Depreciation a

       

      WHERE

      a.business_unit = 'A0001'

      AND a.fiscal_year = 2014

      AND a.period = 11

       

      RESULTS

      A0001        500.00       (blank)             2014          11

        • 1. Re: Selecting fields based on FiscaL Year and Period
          Frank Kulash

          Hi,

           

          Are you saying that the output column called current_amount will be the amount column from one row of the table, and that the output column called prior_amount will be the same column from a different row?

          One way to do that is a self-join, like this:

          SELECT    c.business_unit

          ,         c.amount          AS current_amount

          ,         p.amount          AS prior_amount

          ,         c.fiscal_year

          ,         c.period

          FROM      asset_depreciation  c

          JOIN      asset_depreciation  p  ON    (12 * p.fiscal_year) + p.period

                                               = (12 * c.fiscal_year) + c.period - 1

                                           AND c.buisiness_unit  = p.business_unit    -- Just guessing

          ;

          An inner join, like the one above, will only display rows where there is a prior row.  If you want to display all rows, whether or not there is a prior row, then use an outer join.  (Prior_amount will be NULL when there is no prior row.)

           

          The analytic LAG function can get the same results.

           

          You might be better off storing a DATE in one column, rather than fiscal_year and period in 2 separate NUMBER columns.  You can derive  the fiscal_year and period from the DATE when you need it, or you could store both, using virtual columns or a trigger.  For example, if your fiscal year starts on July 1, so July 2014 was the first period of Fiscal Year 2015, you could store DATE '2014-07-01' as m, and use TO_CHAR (ADD_MONTHS (m, 6), 'YYYY-MM') to display the fiscal year and month ('2015-01').

           

          If you'd care to post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data, then I could test this.

          • 2. Re: Selecting fields based on FiscaL Year and Period
            Frank Kulash

            Hi,

             

            Here's an example of how you might use LAG in this problem:

             

            SELECT    business_unit

            ,         amount          AS current_amount

            ,         CASE

                          WHEN  LAG ((12 * fiscal_year) + period)

                                    OVER ( PARTITION BY  business_unit

                                         , ORDER BY      fiscal_year, period

                                         ) = (12 * fiscal_year) + period - 1

                          THEN  LAG (amount)

                                    OVER ( PARTITION BY  business_unit

                                         , ORDER BY      fiscal_year, period

                                         )

                      END            AS prior_amount

            ,         fiscal_year

            ,         period

            FROM      asset_depreciation

            ;

            Even though the coding is messier, this might be more efficient than a self-join, especially if you need an outer join.

             

            As before, I assume that there are exactly 12 periods in every fiscal year, but the table won't necessarily have them all.

             

            What role does business_unit play in this problem?  Is the combination [business_unit, fiscal_year, period] unique?  If not, include some examples of duplicates when you post the sample data and desired results.

            • 3. Re: Selecting fields based on FiscaL Year and Period
              Frank Kulash

              Hi,

               

              Here's another analytic solution, slightly simpler than the LAG solution above:

               

              SELECT    business_unit

              ,        amount             AS current_amount

              ,        LAST_VALUE (amount)

                            OVER ( PARTITION BY  business_unit

                                  ORDER BY      (12 * fiscal_year) + period

                                  RANGE BETWEEN 1 PRECEDING

                                        AND    1 PRECEDING

                                )         AS prior_amount

              ,        fiscal_year

              ,        period

              FROM      asset_depreciation

              ORDER BY  fiscal_year

              ,        period

              ;

              This should be more efficient, because it only needs 1 analytic function.

              • 4. Re: Selecting fields based on FiscaL Year and Period
                2713822

                I appreciate all of your suggestions.  Your original assumption is correct.  The output column called current_amount will be the amount column from one row of the table, and that the output column called prior_amount will be the same column from a different row.

                 

                I got sidetracked yesterday and havent had a chance to get back to this until this morning.  I took your latest suggestion and modified it with the actual field names (see below).  I may have done something wrong because it is returning the current amount for period 11 but the prior amount is blank for all rows.  I ran a query to confirm there are amounts for period 10.

                 

                I have added a WHERE clause to this sql.  When this is made available to the end users, the business unit, fiscal year and accounting period will be prompts that the user will select.

                 

                Can you explain what the last value section is doing.  It looks like it is multiply the fiscal year by 12 and adding the accounting_period.  I'm guessing the range between is selecting just the prior period.

                 

                I appreciate your help with this sql.....

                 

                SELECT
                     pdr.business_unit,
                     pdr.deptid,
                     pdr.depr AS current_amount,

                 

                LAST_VALUE (pdr.depr)

                  OVER (PARTITION BY  pdr.business_unit
                    ORDER BY (12 * pdr.fiscal_year) + pdr.accounting_period
                      RANGE BETWEEN 1 PRECEDING
                      AND 1 PRECEDING) AS prior_amount,

                 

                pdr.fiscal_year,
                pdr.accounting_period

                 

                FROM PS_DEPR_RPT pdr

                 

                WHERE
                pdr.business_unit = 'A0465'
                AND pdr.fiscal_year = 2014
                AND pdr.accounting_period = 11

                 

                ORDER BY 
                pdr.fiscal_year,
                pdr.accounting_period

                • 5. Re: Selecting fields based on FiscaL Year and Period
                  2713822

                  Before I read your replies yesterday, I came up with these case statements (on my own based on some things I read.).  They work and provide the the correct current and prior fiscal years and accounting periods based on the WHERE clause..  The part that has me stumped is how to use them to return the prior amount.  I'm used to Visual Basic where I could turn the resutls of the case statement into a vaiable and then usd that to select the correct row for the priro amount.  This is starting to make a little more sense based on working with it.  A couple of weeks ago I would havent been able to come up with those case statements.  Thanks for your help.

                   

                  SELECT
                  pdr.DEPTID as DEPTID,
                  pdr.ASSET_ID as ASSET_NO,
                  pa.descr as DESCRIPTION,
                  pdr.ACCOUNT_AD as AD_ACCT,
                  pdl.ACCOUNT as DE_ACCT,
                  pdr.DEPR as AMT_DEPR,
                  pdl.JOURNAL_ID as JRNL_ID,
                  pdl.JOURNAL_DATE as JRNL_DT,
                  pdr.FISCAL_YEAR as FY,
                  pdr.ACCOUNTING_PERIOD as AP,
                  pdr.DEPR as DEPRECIATION,

                   

                  CASE
                    WHEN (pdr.ACCOUNTING_PERIOD)-1 = 0 THEN pdr.FISCAL_YEAR-1
                    ELSE pdr.FISCAL_YEAR
                  END AS PRIOR_YEAR,

                   

                  CASE
                    WHEN pdr.ACCOUNTING_PERIOD -1 =0 then 12
                    ELSE pdr.ACCOUNTING_PERIOD-1
                  END AS PRIOR_AP

                   

                  FROM PS_DEPR_RPT pdr

                   

                  INNER JOIN PS_DIST_LN pdl
                  ON pdl.BOOK = pdr.BOOK
                  AND pdl.BUSINESS_UNIT = pdr.BUSINESS_UNIT
                  AND pdl.FISCAL_YEAR = pdr.FISCAL_YEAR
                  AND pdl.ACCOUNTING_PERIOD = pdr.ACCOUNTING_PERIOD
                  AND pdl.ASSET_ID = pdr.ASSET_ID
                  AND pdl.CF_SEQNO = pdr.CF_SEQNO

                   

                  INNER JOIN PS_ASSET pa
                  ON pa.ASSET_ID = pdl.ASSET_ID
                  AND pa.BUSINESS_UNIT = pdl.BUSINESS_UNIT

                   

                  WHERE
                  pdr.BUSINESS_UNIT='A0465'
                  AND pdr.BOOK='PERFORM'
                  AND pdr.FISCAL_YEAR= 2014
                  AND pdr.ACCOUNTING_PERIOD =11
                  AND pdl.DISTRIBUTION_TYPE = 'DE'

                  • 6. Re: Selecting fields based on FiscaL Year and Period
                    Frank Kulash

                    Hi,

                    2713822 wrote:

                     

                    ...  I may have done something wrong because it is returning the current amount for period 11 but the prior amount is blank for all rows.  I ran a query to confirm there are amounts for period 10.

                     

                    I have added a WHERE clause to this sql.  When this is made available to the end users, the business unit, fiscal year and accounting period will be prompts that the user will select....

                    Analytic functions (such as LAST_VALUE in this problem) are computed after the WHERE clause is applied, so if you have a condition like

                    AND pdr.accounting_period = 11

                    in the WHERE clause, then LAST_VALUE will be computed only for rows with accounting_period = 11, not 10 or any other value.  For this reason, you often need to compute analytic functions in a sub-query, and use conditions like the one above, later.  If you'd post some sample data and desired results, I could show you exactly how to do it.

                    Can you explain what the last value section is doing.  It looks like it is multiply the fiscal year by 12 and adding the accounting_period.  I'm guessing the range between is selecting just the prior period.

                    Try computing a few values, to see what's going on:

                    If fiscal_year=2014 and period=10, that's (12 * 2014) + 10  =  24168 + 10  = 24178

                    If fiscal_year=2014 and period=11, that's (12 * 2014) + 11  =  24168 + 11  = 24179

                    If fiscal_year=2014 and period=12, that's (12 * 2014) + 12  =  24168 + 12  = 24180

                    If fiscal_year=2015 and period=  1, that's (12 * 2015) +  1  =  24180 +   1  = 24181

                    If fiscal_year=2015 and period=  2, that's (12 * 2015) +  2  =  24180 +   2  = 24182

                    So it's returning an integer that increases by 1 every period, including when the fiscal year changes.  Determining what is the previous period is simply a matter of subtracting 1 from the current period, regardless of where the period is in the fiscal year.

                    • 7. Re: Selecting fields based on FiscaL Year and Period
                      Frank Kulash

                      Hi,

                      2713822 wrote:

                       

                      Before I read your replies yesterday, I came up with these case statements (on my own based on some things I read.).  They work and provide the the correct current and prior fiscal years and accounting periods based on the WHERE clause..

                       

                       

                      Yes, they look like they get the right results.  Instead of

                      pdr.ACCOUNTING_PERIOD -1 =0

                      you could also say:

                      pdr.ACCOUNTING_PERIOD = 1

                      Use the one that you (and the people who have to maintain this code) find easier to read and understand.

                       

                      The part that has me stumped is how to use them to return the prior amount.  I'm used to Visual Basic where I could turn the resutls of the case statement into a vaiable and then usd that to select the correct row for the priro amount.

                      That's how you'd do it in a procedural language.  SQL is not a procedural language, and some things will be quite different.  In SQL, you don't really have variables; you have columns.  You can define new columns, based on CASE expressions like the ones you posted (or any other expressions you want) in a sub-query, and then use those columns in super-queries, as if they were actually in the table.  So, if you wanted to use CASE expressions like that where I userd "(12 * fiscal_year) + period", then you could compute the CASE expressions in a sub-query, and use the returned values in a join condition or in the LAG solution I posted earlier.   (For the LAST_VALUE approach, which I thing is the best, you need a single value, like (12*fiscal_year) + period.)

                      if you'd care to post CREATE TABLE and INSERT statements for some sample data, then I could give you specific examples.

                      • 8. Re: Selecting fields based on FiscaL Year and Period
                        2713822

                        Thank you very much for sharing your knowledge and exprience.  I'm learning a lot from your explanations.  I like the LAST_VALUE code as opposed to the CASE statements I created as it runs faster.  I'll be maintianing this code.  Typically, once these queries are rolled out, the only changes we have to make are to add additional fields for the user.  The difficult part for me (at this time) is to get the report to run and display the appropriate results.  It's getting better thanks to your help.

                         

                        Here is some sample data for 1 asset on the the ASSET_DEPRECIATION table:

                        Business Unit   DeptID     Amount     FY          AP

                        A0001              10052          0.00       2014         5

                        A0001              10052       112.03       2014         6

                        A0001              10052       112.02       2014         7

                        A0001              10052       112.03       2014         8

                        A0001              10052       112.01       2014         9

                        A0001              10052       112.00       2014         10

                        A0001              10052       112.02       2014         11

                        A0001              10052       112.04       2014         12

                        A0001              10052       112.01       2015         1

                         

                        The results i would like to see are as follows for FY = 2014 and AP = 11

                        Business Unit   DeptID     Curr_Amt       Prior_Amt     Diff          FY          AP

                        A0001              10052        112.02            112.00        0.02      2014         11

                         

                        I also need to calculate the difference between the current amount and prior amount.  I have written sql to subtract 1 field from another and display the results, so I didn't think it would be too difficult.  The hard part has been in getting the prior amount to display.  Thanks for your help