So based on a start date of 5 years ago on the 07th February – I want to generate 5 rows - one per year from the start date. If the start date was 8 years ago - 8 rows.
| Year | StartDate | EndDate |
|---|---|---|
| 1 | 07-02-2020 | 06-02-2021 |
| 2 | 07-02-2021 | 06-02-2022 |
| 3 | 07-02-2022 | 06-02-2023 |
| 4 | 07-02-2023 | 06-02-2024 |
| 5 | 07-02-2024 | 06-02-2025 |
The following logic has to apply .
Ongoing Allowance – This is a £500 allowance that someone is given.
Customer Allowance – For Year 1 this will always be £500. Each year an additional £500 is added. Also need to take into account the Balance from the previous year. This should never exceed £1000. Also if by Year 3 they have not used their Allowance from Year 1 – it is removed. Same for Year 4 – the balance from year 2 would be removed.
Claimed – This is the amount from the Customer Allowance that they have actually spent.
Balance – Customer Allowance minus Claimed.
So are worked example is per below. It is the Actual Allowance and the Balance field I am having trouble calculating.
| Year | OngoingAllowance | ActualAllowance | Claimed | Balance |
|---|---|---|---|---|
| 1 | 500 | 500 | 100 | 400 |
| 2 | 500 | 900 | 0 | 900 |
| 3 | 500 | 1000 | 700 | 300 |
| 4 | 500 | 300 | 0 | 800 |
| 5 | 500 | 1000 | 0 | 1000 |
Year 1 – Actual Allowance starts at £500. This is always the starting point. £100 is claimed giving you a balance of £400.
Year 2 – Balance in year 1 of £400 PLUS the Ongoing Allowance of £500. Nothing is claimed that year – so the balance is £900 at the end of the year.
Year 3 – This is where is gets more complicated as Year 1 balance of £400 to be removed from Balance year 2 (900) as the balance is over two years old. So that gets you back to £500 PLUS the £500 for Year 3 Allowance - gives us an actual allowance of £1000. £700 is claimed – so the Balance at the end of year 3 is £300.
Year 4 – Balance at the end of Year 3 (£300) plus the £500 allowance for year 4 – nothing claimed. .. Balance at the end of year 4 = £800
Year 5. The Balance in Year 3 (£300) was not used so remove that from the balance at the end of year 4 = £500. Plus the balance for Year 5 of £500 = £1000 for the actual allowance.
I have some code per below – that created me 3 tables in order to store the Year, OngoingAllowance and Claimed. . . but I have no idea how to put into practice the Actual Allowance and Balance columns.
declare @StartDate datetime,
@EndDate datetime
select @StartDate = DateAdd(yy, -5, GetDate()) , --This will be set to the Lease Start Date
@EndDate = dateadd(year,1,DateAdd(yy, -5, GetDate())) -1 --Calculate the Lease End Date based on the Lease Start Date
--Create a table the holds up to 10 years worth of periods
;with myCTE as
(
select 1 as 'Year' ,@StartDate 'PeriodStart' ,@EndDate 'PeriodEnd'
union all
select Year+1 ,dateadd(YEAR, 1, PeriodStart) , dateadd(YEAR, 1, PeriodEnd)
FROM myCTE
where Year+1 <= 5 --Only up to 5 years
)
select Year
,Convert(varchar(10),PeriodStart,105) as StartDate
,Convert(varchar(10),PeriodEnd,105) as EndDate
into #TmpPeriods
from myCTE
--Create Table to Hold Potential Allowance
create table #TmpAllowance ([Year] int, [Allowance] decimal (18,2))
INSERT INTO #TmpAllowance (Year,Allowance)
values (1,500),(2,500),(3,500), (4,500),(5,500)
--Create Table to Hold Allowance Claimed
create table #TmpClaimed ([Year] int, [Claimed] decimal (18,2))
INSERT INTO #TmpClaimed (Year,Claimed)
values (1,100),(2,0),(3,700), (4,0),(5,0)
select p.Year,p.StartDate, p.EndDate, a.Allowance 'AvailableAllowance', c.Claimed
from #TmpPeriods p
join #TmpAllowance a on a.Year = p.Year
join #TmpClaimed c on c.Year = a.Year
drop table #TmpPeriods
drop table #TmpAllowance
drop table #TmpClaimed
Becouse ActualAllowance should be calculated with some constraints,
we can calculate ActualAllowance and Balance recursively, year by year, instead of cumulative sum (running total).
Balance for previous year take from recursive part.
See example:
Source data as query
select p.Year -- ,p.StartDate, p.EndDate
, a.Allowance OngoingAllowance, c.Claimed
from #TmpPeriods p
join #TmpAllowance a on a.Year = p.Year
join #TmpClaimed c on c.Year = a.Year
;
| Year | OngoingAllowance | Claimed |
|---|---|---|
| 1 | 500.00 | 100.00 |
| 2 | 500.00 | 0.00 |
| 3 | 500.00 | 700.00 |
| 4 | 500.00 | 0.00 |
| 5 | 500.00 | 0.00 |
Recursive calculation of OnGoingAllowance, ActualAllowance and Balance
with src as(
select p.Year -- ,p.StartDate, p.EndDate
, a.Allowance OngoingAllowance, c.Claimed
from #TmpPeriods p
join #TmpAllowance a on a.Year = p.Year
join #TmpClaimed c on c.Year = a.Year
)
,rCte as(
select year,OnGoingAllowance
,OnGoingAllowance ActualAllowance
,Claimed
,cast(OnGoingAllowance-Claimed as decimal(18,2)) Balance
from src t
where year=1
union all
select t.year,t.OnGoingAllowance
,cast(
case when (r.Balance+t.OnGoingAllowance)>1000 then 1000
else r.Balance+t.OnGoingAllowance
end
as decimal(18,2)) ActualAllowance
,t.Claimed
,cast(
case when (r.Balance+t.OnGoingAllowance)>1000 then 1000
else r.Balance+t.OnGoingAllowance
end -t.Claimed
as decimal(18,2)) Balance
from rCte r inner join src t on t.year=(r.year+1)
)
select * from rCte
;
| year | OnGoingAllowance | ActualAllowance | Claimed | Balance |
|---|---|---|---|---|
| 1 | 500.00 | 500.00 | 100.00 | 400.00 |
| 2 | 500.00 | 900.00 | 0.00 | 900.00 |
| 3 | 500.00 | 1000.00 | 700.00 | 300.00 |
| 4 | 500.00 | 800.00 | 0.00 | 800.00 |
| 5 | 500.00 | 1000.00 | 0.00 | 1000.00 |