Date Split-Up Based on Fiscal Year

Date split-up based on Fiscal Year

I actually favor Andomar's solution (with the addition of a processes that automatically fills the Periods table), but for fun here's a solution that doesn't require it.

CREATE TABLE your_table (start_date date, end_date date);
INSERT INTO your_table VALUES ('Jan-17-2008', 'May-20-2009');

SELECT
    GREATEST(start_date, ('04-01-'||series.year)::date) AS year_start,
    LEAST(end_date, ('03-31-'||series.year + 1)::date) AS year_end
FROM
    (SELECT
        start_date,
        end_date,
        generate_series(
            date_part('year', your_table.start_date - INTERVAL '3 months')::int,
            date_part('year', your_table.end_date - INTERVAL '3 months')::int)
    FROM your_table) AS series(start_date, end_date, year)
ORDER BY
    start_date;

Splitting a date range into different fiscal years

You can try the following by creating a fiscalYear table. The logic breaks if the quantity start date and end date span more than two fiscal years.

fiscalYear(Year, StartDate, EndDate)

insert fiscalYear values
(2011, "2011-04-01", "2012-03-31"),
(2012, "2012-04-01", "2013-03-31"),
(2013. "2013-04-01", "2014-03-31")

select t1.Year, sum(t2.Quantity)
from fiscalYear t1
inner join myTable t2 on (convert(datetime, t2.StartDate) >= convert(datetime, t1.StartDate)
and convert(datetime, t2.StartDate) <= convert(datetime, t1.EndDate) )
or
(convert(datetime, t2.EndDate) >= convert(datetime, t1.StartDate)
and convert(datetime, t2.EndDate) <= convert(datetime, t1.EndDate) )
group by t1.Year

Postgresql split date range by year parts (financial year)

Actually another post helped me resolve this: Date split-up based on Fiscal Year

DROP TABLE your_table;
CREATE TABLE your_table (id int, start_date date, end_date date);
INSERT INTO your_table VALUES (1, '2020-01-01', '2020-05-01');
INSERT INTO your_table VALUES (2, '2020-03-01', '2021-04-02');

SELECT
    id,
    GREATEST(start_date, ('01-04-'||series.year)::date) AS year_start,
    LEAST(end_date, ('31-03-'||series.year + 1)::date) AS year_end
FROM
    (SELECT
        id,
        start_date,
        end_date,
        generate_series(
            date_part('year', your_table.start_date - INTERVAL '3 months')::int,
            date_part('year', your_table.end_date - INTERVAL '3 months')::int)
    FROM your_table) AS series(id, start_date, end_date, year)
ORDER BY
    start_date;

Result:

"id","year_start","year_end"
  1,"2020-01-01","2020-03-31"
  1,"2020-04-01","2020-05-01"
  2,"2020-03-01","2020-03-31"
  2,"2020-04-01","2021-03-31"
  2,"2021-04-01","2021-04-02"

Split report into Quarters of a Fiscal Year

create 4 formula fields for quarter clinic

create 4 formula fields for quarter state

-- per quarter formula
if Month (date({Command.mydate})) in 1 to 3 then    --qt1
    <number>
else 0

Calculate fiscal year in SQL Server

I suggest you use a User-Defined Function based on the Fiscal year of your application.

CREATE FUNCTION dbo.fnc_FiscalYear(
    @AsOf           DATETIME
)
RETURNS INT
AS
BEGIN

    DECLARE @Answer     INT

    -- You define what you want here (September being your changeover month)
    IF ( MONTH(@AsOf) < 9 )
        SET @Answer = YEAR(@AsOf) - 1
    ELSE
        SET @Answer = YEAR(@AsOf)

    RETURN @Answer

END

GO

Use it like this:

SELECT dbo.fnc_FiscalYear('9/1/2009')

SELECT dbo.fnc_FiscalYear('8/31/2009')

Excel formula to split an amount per year depending on expenditure days within a date range

Here single function(♣) that will create a series of pro-rata multipliers (of appropriate length) for any given start/end date:

EDIT: see end of soln for extended version per OP comment to original soln...

---

SINGLE FUNCTION

=J11*(LET(dates,EDATE(DATE(YEAR($K11),1,1),12*(SEQUENCE(1,YEAR($L11)-YEAR($K11)+2,1))),IF(dates<K11,K11,IF(dates<L11,dates,L11)))-LET(dates,EDATE(DATE(YEAR($K11)-1,1,1),12*(SEQUENCE(1,YEAR($L11)-YEAR($K11)+2,1))),IF(dates<K11,K11,IF(dates<L11,dates,L11))))/(L11-K11)

It may appear somewhat unwieldy in length, but it is far more robust (and concise) compared to the combination of steps/series you have created. What's more, it returns the precise answer RE: pro-rata payments and is guarenteed to never over/under-run RE: total payment (by design).

---

BREAK-DOWN

Comprises 3 distinct parts (some of which are similar in pattern/formation):

1] First part - create a series (array) of years spanning start-end dates:

=LET(dates,EDATE(DATE(YEAR($K5)-1,1,1),12*(SEQUENCE(1,YEAR($L5)-YEAR($K5)+2,1))),IF(dates<K5,K5,IF(dates<L5,dates,L5)))

Thanks to the lovely Spill functionality the new Office 365 variant Excel boasts, you never have to worry about how many years are required -- so long as you have the space to the right of this workbook (would be unusual otherwise - assuming you start in column O and clear any content to the right of this, you'd need an end date beyond the year 2557 (26th century) to run out of columns! ☺

---

2] Second part is merely a replica of the firs series, albeit shifted to the right 'once' (so starts with the 2nd element in the 1st series):

=LET(dates,EDATE(DATE(YEAR($K5),1,1),12*(SEQUENCE(1,YEAR($L5)-YEAR($K5)+2,1))),IF(dates<K5,K5,IF(dates<L5,dates,L5)))

---

3] Third part - you have the basic ingredients from parts 1 and 2 to complete the required task easily: simply deduct series 2 from 1 (giving days between successive dates in series 1 - i.e. days for each year between start and end dates), divide by total days (to yield pro-rata multipliers), and then multiply these by the total £amount and voila - you have your series!

=J5*(O6#-O5#)/(M5)

---

♣ Caveat(s) - assuming you have Office 365 compatible version of Excel (which is quite common nowadays)

---

---

*EDIT - EXTENDED VERSION

Given above, the following extends this to align monetary results (1st table, o11:w21) within respective calendar period columns (spanning the entire period in question).

This soln:

1. Determines header row based upon the number of columns & corresponding calender periods (financial yrs commencing 1/1) as an array function (i.e. dynamic range)
2. Utilises a modified version of the eq. provided for dates arrage (refer: "First Part", original soln)

Comment - same caveats as before - i.e. Office 365 etc.

Screenshot(s)/here refers:

---

DATES (HEADER) - Y10 (array)

=LET(y_,MIN(K11:K21),x_,MAX(L11:L21),EDATE(DATE(YEAR(y_)-1,1,1),12*(SEQUENCE(1,YEAR(x_)-YEAR(y_)+2,1))))

Comment - enter once within single cell Y10 - i.e. as an array function with Spill to right

---

ALIGNED/SHIFTED FINANCIALS - Y11:Y21 (each cell in col is an array)

=IFERROR(IF(Y$10#<EDATE(K11,-12),"",IF(Y$10#>EDATE(L11,12),"",INDEX(O11#,1,MATCH(Y$10#,EDATE(DATE(YEAR($K11)-1,1,1),12*(SEQUENCE(1,YEAR($L11)-YEAR($K11)+2,1))),0)))),"")

Comment - enter this as an array fn. (#SPILL! to the right) in each cell within column Y (can drag this function down Y11:Y21 as required)

---

SQL: Split a row into multiple rows based on Fiscal Year

And you can deal with NULL end dates using COALESCE

SELECT f.name, t.name FROM t
INNER JOIN f
ON f.start BETWEEN t.start AND COALESCE(t.end,CURRENT_DATE)
OR f.end BETWEEN t.start AND COALESCE(t.end,CURRENT_DATE)

In PostgreSQL, select fiscal year month format as a date column

This looks horrible, but it seems to work so far...

select

case when extract('month' from (to_date(<your column>,'yy-mon'))) > 4
  then (to_date(<your column>,'yy-mon')) + interval '-1' year
else (to_date(<your column>,'yy-mon'))
              end
from
<your table>

Fiddle example

---

Related Topics