• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 992
  • Last Modified:

Crosstab Query across multiple fiscal (not calendar) years by month

I am attempting to create a crosstab query where I need to accomplish two objectives:

1.  Match the report to the fiscal year that begins Dec 1 and end Nov 30
2.  String two fiscal years worth of data by month across the columns, beginning with Dec 1, 2004 and ending with Nov 30, 2006

The table (TBL_TRANS) columns used and sample data are:
TRANS_DATE           PAYEE                         TRANS_AMT
06/02/2006              ABC                            1000.00
05/06/2006              ABC                            2000.00
05/10/2005              CDE                            3000.00

Using the wizard, I get the following code that generates the crosstab within the year:

TRANSFORM Sum(TBL_TRANS.TRANS_AMT) AS SumOfTRANS_AMT
SELECT TBL_TRANS.PAYEE, Sum(TBL_TRANS.TRANS_AMT) AS [Total Of TRANS_AMT]
FROM TBL_TRANS
GROUP BY TBL_TRANS.PAYEE
PIVOT Format([TRANS_DATE],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

What I want for the end result of the query is:

PAYEE         Dec 04   Jan 05  Feb 05   Mar 05  etc...  Nov 05 Total FY 05   Dec 05 etc...Nov 06  Total FY 06

Thanks,
Tom
0
thelton_us
Asked:
thelton_us
  • 8
  • 4
  • 3
  • +1
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Usually in these instances I have a table named tbl_months (or something like that), with a field that contains the fiscal month in YYYYMM format

Example:  March 2006 would be 200603

This way, if you use that field in your queries you are guaranteed that the months will be sorted chronologically, as the numerical sort is the same as chronological, where alphabetical sorts of Jan, Feb, Mar, etc. fail.

Hope this helps.
-Jim

0
 
GRayLCommented:
Try this.  If it gives you what you want, then we can work out how we title the pivot columns


TRANSFORM Sum(TBL_TRANS.TRANS_AMT) AS SumOfTRANS_AMT
SELECT TBL_TRANS.PAYEE, Year(TRANS_DATE) + Iif(TRANS_DATE,<DateSerial(Year(TRANS_DATE,12,1) AS FY,
Sum(TBL_TRANS.TRANS_AMT) AS [Total Of TRANS_AMT]
FROM TBL_TRANS
Where TRANS_DATE BETWEEN #2004-12-01# and #2006-11-30#
GROUP BY TBL_TRANS.PAYEE, Year(TRANS_DATE) + Iif(TRANS_DATE,<DateSerial(Year(TRANS_DATE,12,1)
PIVOT Format([TRANS_DATE],"yymm");
0
 
thelton_usAuthor Commented:
Hi GRayL,

I do see where you are going, but I am having trouble getting the statement to compile.  It is in the selection of "Year(TRANS_DATE) + Iif(TRANS_DATE,<DateSerial(Year(TRANS_DATE,12,1) AS FY".  The Iif statement appears to have the comma misplaced after TRANS_DATE and is missing the closing parenthesis, is it not?  Also, is the expression intending to derive the year of the Trans_date (for example, 2006) and mathematically add to the result of the Iif statement?  Or is it to be concatenated?

Thanks,
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
harfangCommented:
Hello thelton_us

There is a mixup here. There are two distinct problems: 1) working with fiscal years and 2) creating a multi-year monthly crosstab showing 24 months.


1) jimhorn's solution of course works. The conversion to a fiscal year (basically one month off in your case) could also be obtained by:

    FiscalYearMonth: Format(DateAdd('m',1,TRANS_DATE),'yymm')
    FiscalYear: Year(DateAdd('m',1,TRANSDATE))

The first is a string, e.g. '0501' for Dec.04, being the first fiscal month of 2005.

However, you do not wish that. You want to display Dec.05, and not '0501'. In other words, this is not a question of fiscal years and months after all, unless it's only about filtering. To find all values for the fiscal year 'Y', you could use this as criteria ('n' being the number of months to offset):

    TRANS_DATE Between DateSerial(Y,1-n,1) And DateSerial(Y,13-n,0)

This is only useful if 'Y' and 'n' are not hard coded, naturally, but are parameters.


2) To create a multi-year monthly cross-tab, all you really need to do is to add the year information to the PIVOT section to avoid merging months of different years.

However, the GROUP BY clause is used only for the management of the rows, not of the columns. So Ray's query would in fact attempt to produce the two years one below the other and not next to one-another.

So, your query needs a WHERE clause in most cases (taken from Ray's query)

WHERE TRANS_DATE BETWEEN #2004-12-01# and #2006-11-30#
GROUP BY TBL_TRANS.PAYEE
PIVOT Format(TRANS_DATE,'yymm')

If you prefer text:

WHERE TRANS_DATE BETWEEN #2004-12-01# and #2006-11-30#
GROUP BY TBL_TRANS.PAYEE
PIVOT Format(TRANS_DATE,'mmm.yy')

But then the colums would be in alphabetical order. This is not necessarily a problem, as you can reorder them in datasheet view, or format them as you wish on a form or report.

If you want to have fixed column titles, like in the wizard's cross-tab, you can specify the list of desired columns, which also serves as WHERE clause indirectly, but is slower. Therefore, you might want to leave the WHERE clause nontheless.

PIVOT Format([TRANS_DATE],"mmm yy") In ('Dec 04', 'Jan 05', 'Feb 05' .... 'Nov 06')

Hope this helps
(°v°)
0
 
GRayLCommented:
Sorry, my mistake:

TRANSFORM Sum(TBL_TRANS.TRANS_AMT) AS SumOfTRANS_AMT
SELECT TBL_TRANS.PAYEE, Year(TRANS_DATE) + Iif(TRANS_DATE<DateSerial(Year(TRANS_DATE,12,1),0,1) AS FY,
Sum(TBL_TRANS.TRANS_AMT) AS [Total Of TRANS_AMT]
FROM TBL_TRANS
Where TRANS_DATE BETWEEN #2004-12-01# and #2006-11-30#
GROUP BY TBL_TRANS.PAYEE, Year(TRANS_DATE) + Iif(TRANS_DATE,<DateSerial(Year(TRANS_DATE,12,1)
PIVOT Format([TRANS_DATE],"yymm");
0
 
thelton_usAuthor Commented:

Thanks GRayL and harfang,

With a few additional double quotes and parenthesis here and there, here is the code that I got to work from GRayL, but as harfang pointed out, it groups by FY down with the appropriate FY months across.

TRANSFORM Sum(TBL_TRANS.TRANS_AMT) AS SumOfTRANS_AMT
SELECT TBL_TRANS.PAYEE, Year(TRANS_DATE) + Iif(TRANS_DATE<DateSerial(Year(TRANS_DATE),"12","1"),0,1) AS FY, Sum(TBL_TRANS.TRANS_AMT) AS [Total Of TRANS_AMT]
FROM TBL_TRANS
WHERE TRANS_DATE BETWEEN #2004-12-01# and #2006-11-30#
GROUP BY TBL_TRANS.PAYEE, Year(TRANS_DATE) + Iif(TRANS_DATE<DateSerial(Year(TRANS_DATE),"12","1"),0,1)
PIVOT Format([TRANS_DATE],"yymm");

The code illustrated below from harfang does align across each payee for the two year time period, but I still need to have a total column for each fiscal year.

TRANSFORM Sum(TBL_TRANS.TRANS_AMT) AS SumOfTRANS_AMT
SELECT TBL_TRANS.PAYEE, Sum(TBL_TRANS.TRANS_AMT) AS [Total Of TRANS_AMT]
FROM TBL_TRANS
WHERE (((TBL_TRANS.TRANS_DATE) Between #12/1/2004# And #11/30/2006#))
GROUP BY TBL_TRANS.PAYEE
PIVOT Format([TRANS_DATE],"yymm");

How best can that be accomplished?

Thanks,
Tom
0
 
GRayLCommented:
Tom:  Nearyly there but I've got to go sailboat racing.  I'll check in later.

Ray
0
 
harfangCommented:
thelton_us

I had not seen the yearly totals. What's the best way? Certainly not an Access cross-tab query. You basically want hierarchical column headers, i.e. the fiscal years and the months that fall within. This is quite easy in Excel, because Excel supports true pivot tables. If you prepare a simple query like this:

SELECT
    PAYEE,
    Year(DateAdd('m',1,TRANS_DATE)) AS FYEAR,
    TRANS_DATE-Day(TRANS_DATE)+1 AS RMONTH,
     Sum(TRANS_AMT) AS AMT
FROM TBL_TRANS
GROUP BY
    PAYEE,
    Year(DateAdd('m',1,TRANS_DATE)),
    TRANS_DATE-Day(TRANS_DATE)+1;

Or even just the raw ungrouped amounts:

SELECT
    PAYEE,
    Year(DateAdd('m',1,TRANS_DATE)) AS FYEAR,
    TRANS_DATE-Day(TRANS_DATE)+1 AS RMONTH,
     TRANS_AMT
FROM TBL_TRANS;

You can then simply use that as source for an Excel pivot table, with easy formatting options and true pivoting capabilities. You can even very quickly produce pivot charts, if that is what you are after.

To do this in Access requires several queries, which will be hard to maintain. It would be somewhat easier to do this in a report, perhaps, but the fact is that a database engine is not the perfect tool for this type of output.

The following query would give you the desired output. Look at it as a kind of exotic curiosity, I don't think you would really want to go to this level of complexity each year... Each pair of square brackets delimits a subquery, which could in fact be saved as such, and then referenced by name: one total query for both years and two cross-tab queries, one for each fiscal year.

SELECT
    TOTS.*,
    A_04_12, A_05_01, A_05_02, A_05_03,
    A_05_04, A_05_05, A_05_06, A_05_07,
    A_05_08, A_05_09, A_05_10, A_05_11,
    FY_2005,
    A_05_12, A_06_01, A_06_02, A_06_03,
    A_06_04, A_06_05, A_06_06, A_06_07,
    A_06_08, A_06_09, A_06_10, A_06_11,
    FY_2006
FROM ( [
        SELECT PAYEE, Sum(TRANS_AMT) AS TOTAL
        FROM TBL_TRANS
        WHERE TRANS_DATE Between #2004-12-1# And #2006-11-30#
        GROUP BY PAYEE
    ]. AS TOTS
    LEFT JOIN [
        TRANSFORM Sum(TRANS_AMT)
        SELECT PAYEE, Sum(TRANS_AMT) AS FY_2005
        FROM TBL_TRANS
        WHERE TRANS_DATE Between #2004-12-1# And #2005-11-30#
        GROUP BY TBL_TRANS.PAYEE
        PIVOT 'A_'+Format(TRANS_DATE,'yy_mm') IN (
            'A_04_12', 'A_05_01', 'A_05_02', 'A_05_03',
            'A_05_04', 'A_05_05', 'A_05_06', 'A_05_07',
            'A_05_08', 'A_05_09', 'A_05_10', 'A_05_11' )
    ]. AS TAB05
    ON TOTS.PAYEE = TAB05.PAYEE )
    LEFT JOIN [
        TRANSFORM Sum(TRANS_AMT)
        SELECT PAYEE, Sum(TRANS_AMT) AS FY_2006
        FROM TBL_TRANS
        WHERE TRANS_DATE Between #2005-12-1# And #2006-11-30#
        GROUP BY TBL_TRANS.PAYEE
        PIVOT 'A_'+Format(TRANS_DATE,'yy_mm') IN (
            'A_05_12', 'A_06_01', 'A_06_02', 'A_06_03',
            'A_06_04', 'A_06_05', 'A_06_06', 'A_06_07',
            'A_06_08', 'A_06_09', 'A_06_10', 'A_06_11' )
    ]. AS TAB06
    ON TOTS.PAYEE = TAB06.PAYEE
ORDER BY
    TOTS.PAYEE;

Cheers!
(°v°)
0
 
GRayLCommented:
For the record, does the table contain only two consecutive FY data, or is that how it now is and it will grow?  Then from that you pick a date() and generate a report which get the current and previous FY data.



0
 
GRayLCommented:
Given you wind up with a multiyear table from which you want to select two consecutive FY's,  how about running a crosstab twice, the first is for an entered FY via a parameter, and the second is for a year later.  Then a query of the two queries would give you all the numbers you want, except that the payee would appear twice in each line, once at the beginning of the first year's data, then the total for that year, then the monthly data, then the repitition of the payee, the next year's total, and then the monthly data.
0
 
GRayLCommented:
Save this as qxtab1:

TRANSFORM Sum(a.TRANS_AMT) AS SumOfTRANS_AMT
SELECT a.PAYEE, [EnterFirstFY - nnnn] as FY
Sum(Iif(Year(a.TRANS_DATE) + Iif(a.TRANS_DATE<DateSerial(Year(a.TRANS_DATE,12,1),0,1)=[EnterFirstFY - nnnn],a.AMT,0) AS FirstFYSum
FROM TBL_TRANS AS a
Where a.TRANS_DATE BETWEEN #2004-12-01# and #2006-11-30#
GROUP BY a.PAYEE, [EnterFirstFY - nnnn]
PIVOT Format([a.TRANS_DATE],"yymm");

Save this as qxtab2:

TRANSFORM Sum(b.TRANS_AMT) AS SumOfTRANS_AMT
SELECT a.PAYEE, [EnterFirstFY - nnnn] + 1 as FY
Sum(Iif(Year(b.TRANS_DATE) + Iif(b.TRANS_DATE<DateSerial(Year(b.TRANS_DATE,12,1),0,1)=[EnterFirstFY - nnnn]+1,b.AMT,0) AS SecondFYSum
FROM TBL_TRANS AS b
Where b.TRANS_DATE BETWEEN #2004-12-01# and #2006-11-30#
GROUP BY b.PAYEE, [EnterFirstFY - nnnn]
PIVOT Format([b.TRANS_DATE],"yymm");

Now run this query:

SELECT qxtab1.*, qxtab2.* FROM qxtab1 INNER JOIN qxtab2 ON qxtab1.PAYEE=qxtab2.Payee

I think this will work provided there are the same payees in both FY's.
0
 
thelton_usAuthor Commented:
Thanks for all the input.  I have not yet had a time to review and comment, but will do so Friday, June 15.

Tom
0
 
GRayLCommented:
Tom: Fri is June 16 in my part of the world.  Maybe that's what happens on the other side of the dateline?  ;-)
0
 
thelton_usAuthor Commented:
The comments re: using Excel or a Report for summarizing makes sense...certainly the path of least resistance.  All-in-all, both of you (harfang and GrayL) have well achieved the core objective.

I will upgrade the points to 100 and split.

Thanks,
Tom
0
 
GRayLCommented:
Thanks, glad I could help.
0
 
harfangCommented:
That was kind of you, thanks!
Success with your project!
(°v°)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now