CODEWITHSUNDEEP
sqlexcelms-accessfinancial
SpiceTrader
SpiceTrader

Reputation: 43

SQL group data by financial year

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.

Upvotes: 4

Views: 4907

Answers (4)

Mladen Oršolić
Mladen Oršolić

Reputation: 1422

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.

Upvotes: 1

codersl
codersl

Reputation: 2332

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;

Upvotes: 5

Gustav
Gustav

Reputation: 55816

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")

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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

Upvotes: 4

Related Questions