SQL group data by financial year

Question

I have a table with year, month, date, project and income columns. Each entry is added on the first of every month.

I have to be able to get the total income for a particular project for every financial year. What I've got so far (which I think kind of works for yearly?) is something like this:

SELECT year, SUM(TotalIncome)
FROM myTable
WHERE ((date Between #1/1/2007# And #31/12/2015#) AND (project='aproject'))
GROUP BY year;

Essentially, rather than grouping the data by year, I would like to group the results by financial year. I have used DatePart('yyyy', date) and the results are the same.

I'll be running the query from excel to a database. I need to be able to select the number of years (e.g. 2009 to 2014, or 2008 to 2010, etc). I'll be taking the years from user input in excel (taking two dates, i.e. startYear, endYear).

The results from the current query give me, each year would be data from 1st January to 31st December of the same year:

Year           |  Income
2009           |  $123.12
2010           |  $321.42
2011           |  $231.31
2012           |  $426.37

I want the results to look something like this, where each financial year would be 1st July to 30th June of the following year:

FinancialYear  |  Income
2009-10        |  $123.12
2010-11        |  $321.42
2011-12        |  $231.31
2012-13        |  $426.37

If possible, I'd also like to be able to do it per quarter.

Also, if it matters, I have read-only access so I can't make any modifications to the database.

what is wrong with your query? any error or doesnt return what you want? — Juan Carlos Oropeza, Oct 24 '15 at 11:49
The current query should be returning it by year, I need it to return an entry for each financial year instead (e.g. 1-Jul-2014 to 30-Jun-2015). — SpiceTrader, Oct 24 '15 at 11:53
But you query work or is just how you imagine should be? I mean is `year` a field or not? — Juan Carlos Oropeza, Oct 24 '15 at 11:55
But each year isn't a financial year, just a normal year. I didn't create the table myself, but I think the year field seems redundant when there's already a date field. — SpiceTrader, Oct 24 '15 at 12:05
Please read [**How to ask**](http://stackoverflow.com/help/how-to-ask) And [**How to create a Minimal, Complete, and Verifiable example.**](http://stackoverflow.com/help/mcve) We need sample data and desire output to find an answer faster, otherwise we will be wasting time guessing what you need. You also could create your schema with data using **http://www.sqlfiddle.com**. — Juan Carlos Oropeza, Oct 24 '15 at 12:06
You should explain what is `myTable` or any schema and field in your table. I use `date` because that is what you use in your query. See how this guessing game can take sometime? — Juan Carlos Oropeza, Oct 24 '15 at 12:09
For grouping by financial year see http://stackoverflow.com/questions/2591554/sql-query-to-retrieve-financial-year-data-grouped-by-the-year — codersl, Oct 24 '15 at 12:10
@JuanCarlosOropeza Yeah, I'll edit my question to make it clearer. — SpiceTrader, Oct 24 '15 at 12:13
@SpiceTrader I think I understand better now. But you didnt include any sample data or schema description. So if go and write another query will get sad if give you another wrong answer — Juan Carlos Oropeza, Oct 24 '15 at 12:33
@codersl that solution doesn't work in Access, can't use CASE WHEN or concat() — SpiceTrader, Oct 24 '15 at 14:11

score 4 · Accepted Answer · edited May 23 '17 at 11:52

This hasn't been tested but the logic is the same as the answer from SQL query to retrieve financial year data grouped by the year.

SELECT fy.FinancialYear, SUM(fy.TotalIncome)
FROM
(
    SELECT
          IIF( MONTH(date) >= 7,
               YEAR(date)   & "-" & YEAR(date)+1,
               YEAR(date)-1 & "-" & YEAR(date) ) AS FinancialYear,
          TotalIncome
    FROM  myTable
    WHERE date BETWEEN #1/1/2007# AND #31/12/2015#
    AND   project = 'aproject'
) AS fy
GROUP BY fy.FinancialYear;

Extending this further you can get per quarter as well:

SELECT fy.FinancialQuarter, SUM(fy.TotalIncome)
FROM
(
    SELECT
          IIF( MONTH(date) >= 10,
               "Q2-" & YEAR(date) & "-" & YEAR(date)+1,
               IIF( MONTH(date) >= 7,
                    "Q1-" & YEAR(date) & "-" & YEAR(date)+1,
                    IIF( MONTH(date) >= 4,
                         "Q4-" & YEAR(date)-1 & "-" & YEAR(date),
                         "Q3-" & YEAR(date)-1 & "-" & YEAR(date)
                       )
                  )
             ) AS FinancialQuarter,
          TotalIncome
    FROM  myTable
    WHERE date BETWEEN #1/1/2007# AND #31/12/2015#
    AND   project = 'aproject'
) AS fy
GROUP BY fy.FinancialQuarter;

This should work fine, I haven't tested the quarterly one yet though. Thanks for your help! — SpiceTrader, Oct 25 '15 at 03:45

Juan Carlos Oropeza · Answer 2 · 2015-10-24T12:42:46.540

You need create a financial_year table

financial_year_id int primary key
period            varchar
startDate         date
endDate           date

with this data

1  |  2009-10  |  #1/1/2009#  | #1/1/2010#
2  |  2010-11  |  #1/1/2010#  | #1/1/2011#
3  |  2011-12  |  #1/1/2011#  | #1/1/2012#
4  |  2012-13  |  #1/1/2012#  | #1/1/2013#

then perfom a join with your original table

 SELECT FY.period, SUM (YT.TotalIncome)
 FROM YourTable  YT
 INNER JOIN financial_year FY
         ON YT.date >= FY.startDate 
        and YT.date <  FY.endDate
 GROUP BY FY.period

For Quarter:

 SELECT FY.period,  DatePart ('q', date) as quarter, SUM (YT.TotalIncome)
 FROM YourTable  YT
 INNER JOIN financial_year FY
         ON YT.date >= FY.startDate 
        and YT.date <  FY.endDate
 GROUP BY FY.period, DatePart ('q', date)

NOTE

I wasnt sure if your date is just date or datetime so I went the safest way

if is just date you could use

1  |  2009-10  |  #1/1/2009#  | #31/12/2009#
2  |  2010-11  |  #1/1/2010#  | #31/12/2010#

AND

ON YT.date BETWEEN  FY.startDate AND  FY.endDate

Using DatePart gives me the same result. For example, I can successfully get the information for the year 2007 (the year being defined as being between 1-1-2007 and 31-12-2007). But I want to get the information for the financial year 2007-2008 (so between 1-7-2007 and 30-6-2008). — SpiceTrader, Oct 24 '15 at 12:30
I don't think you read my last edit, but I have read-only access to the database :( I can't create new tables. — SpiceTrader, Oct 24 '15 at 12:50
sorry didnt read that. I just update it to make it more visible. — Juan Carlos Oropeza, Oct 24 '15 at 12:51

Mladen Oršolić · Answer 3 · 2015-10-25T00:25:28.393

1

Since you can't create new tables (read-only access) you can use with statement before your select to create a financial years "table" usable from your query.

with financial_year as 
(
select '2009' as year,  '1/1/2009' as start_date, '31/12/2009' as end_date
union
select '2010' as year,  '1/1/2010' as start_date, '31/12/2010' as end_date
...
)
SELECT FY.year, SUM (YT.TotalIncome)
 FROM YourTable  YT
 INNER JOIN financial_year FY
         ON YT.date >= FY.start_date 
        and YT.date <=  FY.end_date
 GROUP BY FY.year

This will work (with minor modifications) with both MS SQL server and Oracle, i'm not sure whats your database tho.

edited Oct 25 '15 at 00:25

answered Oct 25 '15 at 00:20

Mladen Oršolić

1,352
3
23
43

This question tag is access and excel – Juan Carlos Oropeza Oct 25 '15 at 03:28
there is also sql tag, any reason you didn't mention that one? – Mladen Oršolić Oct 25 '15 at 23:12
the reason is sql doesnt mean sql server. – Juan Carlos Oropeza Oct 26 '15 at 04:03

score 0 · Answer 4 · answered Oct 24 '15 at 13:01

0

This is very simple using DateAdd to shift by six months:

SELECT 
    Format(DateAdd("m", 6, [Date]), "yyyy") & Format(DateAdd("m", 18, [Date]), "\-yy") As FinancialYear,
    SUM(TotalIncome) As Income
FROM 
    myTable
WHERE 
    ([date] Between #1/1/2007# And #31/12/2015#) AND (project="aproject")
GROUP BY
    DateAdd("m", 6, [Date]), 
    Format(DateAdd("m", 6, [Date]), "yyyy") & Format(DateAdd("m", 18, [Date]), "\-yy")

answered Oct 24 '15 at 13:01

Gustav

53,498
7
29
55

As far as I can see, this works great! Thank you so much! – SpiceTrader Oct 24 '15 at 13:29
Wait, actually this seems to be giving me a strange output. Looking at the same dates `([date] Between #1/1/2007# And #31/12/2015#)` It's giving me 2015-2016 and 2016-2017 as well. There aren't even any entries for those years. – SpiceTrader Oct 24 '15 at 13:37
As you don't specify if the financial year is shifted forwards or backwards relative to the calendar year, I assumed forward. If backwards, use -6 and 6 for the two shift values respectively. – Gustav Oct 24 '15 at 21:24

SQL group data by financial year

4 Answers4