Sum of particular column with month and year with fiscal year from custom date

Question

I have following data in my table:

uniqueId    d_date      amount
1         2018-02-01    100.25
2         2019-03-01    456.5
3         2018-02-01    455
4         2019-05-01    200.48
5         2018-06-01    100
6         2019-07-01    200
7         2018-12-01    6950
8         2019-02-01    60
9         2020-01-20    100

Now when I enter start date = '2018-03-12' then my fiscal year must start with march 2018 to feb 2019 and so on.

If i enter start date = '2019-05-12' then my fiscal year must start with May 2019 to April 2020

I have tried below query but it is not working properly and also it calculate past year which is 2017 I do not want any data from past year from my entered custom date. So if entered start date = '2018-03-12' then is must start calculation for 2018 and above years only. No past year.

Declare @startdate as date
Declare @monthDate as int
Declare @ownmonth as int
set @startdate = '2018-03-12'
set @monthDate = month(@startdate)
set @ownmonth = 1
select
    year(dateadd(month, -@monthDate, d_date)) year,
    sum(case when month(d_date) = case when @monthDate+1 > 12 then @ownmonth else @monthDate+1 End  then amount end) ,
    sum(case when month(d_date) = case when @monthDate+2 > 12 then @ownmonth+1 else @monthDate+2  End then amount end) ,
    sum(case when month(d_date) = case when @monthDate+3 > 12 then @ownmonth+2 else @monthDate+3  End  then amount end) ,
    sum(case when month(d_date) = case when @monthDate+4 > 12 then @ownmonth+3 else @monthDate+4  End  then amount end) ,
    sum(case when month(d_date) = case when @monthDate+5 > 12 then @ownmonth+4 else @monthDate+5  End  then amount end) ,
    sum(case when month(d_date) = case when @monthDate+6 > 12 then @ownmonth+5 else @monthDate+6  End  then amount end) ,
    sum(case when month(d_date) = case when @monthDate+7 > 12 then @ownmonth+6 else @monthDate+7  End  then amount end) ,
    sum(case when month(d_date) = case when @monthDate+8 > 12 then @ownmonth+7 else @monthDate+8  End then amount end) ,
    sum(case when month(d_date) = case when @monthDate+9 > 12 then @ownmonth+8 else @monthDate+9  End  then amount end) ,
    sum(case when month(d_date) = case when @monthDate+10 > 12 then @ownmonth+9 else @monthDate+10  End then amount end) ,
    sum(case when month(d_date) = case when @monthDate+11 > 12 then @ownmonth+10 else @monthDate+11  End then amount end) ,
    sum(case when month(d_date) = case when @monthDate+12 > 12 then @ownmonth+11 else @monthDate+12  End then amount end) ,
    sum(amount) total
from mytable
group by year(dateadd(month, -@monthDate, amount))
order by year

But above query does not show proper year & month wise data

Now I want output with fiscal year calculation:

Year    Mar     Apr    May    Jun   July   Aug   Sept   Oct   Nov    Dec  Jan     Feb    Total
2018     -      -       -     100     -     -    -     -     -      6950   -      60     7110
2019    456.5    -    200.48   -    200     -    -     -     -        -    100     -     956.98

I can not use PIVOT as it is not supported in my compact SQL Server version.

How can I do this?

Thorsten Kettner Thorsten Kettner · Accepted Answer · 2020-04-09T05:50:56

Your rule for a fiscal year is the year's March until the following year's February:

date       | fiscal year
...        | ...
2018-02-28 | 2017
2018-03-01 | 2018
...        | 2018
2019-02-28 | 2018
2019-03-01 | 2019
...        | ...

That means when we subtract two months from a date, we get a date the year of which is the fiscal year:

date       | date - 2 months | fiscal year
...        | ...             | ...
2018-02-28 | 2017-12-28      | 2017
2018-03-01 | 2018-01-01      | 2018
...        | ...             | 2018
2019-02-28 | 2018-12-28      | 2018
2019-03-01 | 2019-01-01      | 2019
...        | ...             | ...

select
  year(dateadd(month, -2, d_date)) as fiscal_year,
  sum(case when month(d_date) =  3 then amount else 0 end) as mar,
  sum(case when month(d_date) =  4 then amount else 0 end) as apr,
  sum(case when month(d_date) =  5 then amount else 0 end) as may,
  sum(case when month(d_date) =  6 then amount else 0 end) as jun,
  sum(case when month(d_date) =  7 then amount else 0 end) as jul,
  sum(case when month(d_date) =  8 then amount else 0 end) as aug,
  sum(case when month(d_date) =  9 then amount else 0 end) as sep,
  sum(case when month(d_date) = 10 then amount else 0 end) as oct,
  sum(case when month(d_date) = 11 then amount else 0 end) as nov,
  sum(case when month(d_date) = 12 then amount else 0 end) as dec,
  sum(case when month(d_date) =  1 then amount else 0 end) as jan,
  sum(case when month(d_date) =  2 then amount else 0 end) as feb,
  sum(amount) as total
from mytable
group by year(dateadd(month, -2, d_date))
order by year(dateadd(month, -2, d_date));

If you want to limit this to the fiscal year a given date resides in, add:

where year(dateadd(month, -2, d_date)) = year(dateadd(month, -2, @given_date))

And well, if you want to limit this to the fiscal years beginning with that year, that would of course be:

where year(dateadd(month, -2, d_date)) >= year(dateadd(month, -2, @given_date))

UPDATE: You want a fiscal year to start with the first day of the month of a given date. I.e. If the given date is 1990-04-23, then a fiscal year starts with April. This changes above query only slightly, because rather than subtracting 2 months (for March), we must generalize this to subtracting one month less than the given month.

I am using a modulo operation when comparing months in order not to end up with months 13, 14, etc.

select
  year(dateadd(month, - month(@startdate) + 1, d_date)) as fiscal_year,
  sum(case when month(d_date) = (month(@startdate) +  0) % 12 then amount else 0 end) as first,
  sum(case when month(d_date) = (month(@startdate) +  1) % 12 then amount else 0 end) as second,
  sum(case when month(d_date) = (month(@startdate) +  2) % 12 then amount else 0 end) as third,
  sum(case when month(d_date) = (month(@startdate) +  3) % 12 then amount else 0 end) as fourth,
  sum(case when month(d_date) = (month(@startdate) +  4) % 12 then amount else 0 end) as fith,
  sum(case when month(d_date) = (month(@startdate) +  5) % 12 then amount else 0 end) as sixth,
  sum(case when month(d_date) = (month(@startdate) +  6) % 12 then amount else 0 end) as seventh,
  sum(case when month(d_date) = (month(@startdate) +  7) % 12 then amount else 0 end) as eighth,
  sum(case when month(d_date) = (month(@startdate) +  8) % 12 then amount else 0 end) as nineth,
  sum(case when month(d_date) = (month(@startdate) +  9) % 12 then amount else 0 end) as tenth,
  sum(case when month(d_date) = (month(@startdate) + 10) % 12 then amount else 0 end) as eleventh,
  sum(case when month(d_date) = (month(@startdate) + 11) % 12 then amount else 0 end) as twelfth,
  sum(amount) as total
from mytable
group by year(dateadd(month, - month(@startdate) + 1, d_date))
order by year(dateadd(month, - month(@startdate) + 1, d_date));

And again, if we want our results start from the fiscal year of the given date, we'd add:

where year(dateadd(month, - month(@startdate) + 1, d_date)) >= year(@startdate)

Sum of particular column with month and year with fiscal year from custom date

1 Answers