# T-SQL Question

181 Reputation points
2020-11-02T17:29:08.67+00:00

Based on the below sample data, we need to calculate a new column for each monthname and MonthProjectionNumber , the formula for the column is as follows:

Example for MonthName : Feb and MonthProjectionNumber : 2, the calculation formula is : sum of (Periodvalue of Feb,where MonthProjectionNumber = 1 + PeriodValueof Jan, where MonthProjectionNumber = 2)

Example for MonthName : Mar and MonthProjectionNumber : 3, the calculation formula is : sum of (Periodvalue of Mar,where MonthProjectionNumber = 1 + PeriodValueof Feb, where MonthProjectionnumber = 2 + MonthPeriodvalueofJan,where MonthProjectionNumber = 3)

rest of the MonthProjectionNumber values greater than the MonthNameNumber will be zero,example for month feb all monthprojectionnumber values > 2 will be zero
similarly for march, all projectionnumber> 3 will be zero,

Note : We are calculating monthprojectionnumber for that particular month

create table #test
(
YearNo int,
MonthName varchar(25),
UnitOfAccount Varchar(25),
MonthProjectionNumber int,
PeriodValue decimal(12,3)
)

INSERT INTO #TEST Values (2020, 'Jan' ,'Term-Onerous-2020', 1 ,250.754317962521)
INSERT INTO #TEST Values (2020, 'Jan' ,'Term-Onerous-2020', 2 ,350.754317962521)
INSERT INTO #TEST Values (2020, 'Jan' ,'Term-Onerous-2020', 3 ,280.752661975435)
INSERT INTO #TEST Values (2020, 'Jan' ,'Term-Onerous-2020', 4 ,375.753413961762)
INSERT INTO #TEST Values (2020, 'Jan' ,'Term-Onerous-2020', 5 ,260.753069962936)
INSERT INTO #TEST Values (2020, 'Jan' ,'Term-Onerous-2020', 6 ,300.752725964404)

INSERT INTO #TEST Values (2020, 'Feb' ,'Term-Onerous-2020', 1 ,0 )
INSERT INTO #TEST Values (2020, 'Feb' ,'Term-Onerous-2020', 2 ,200.754317962521)
INSERT INTO #TEST Values (2020, 'Feb' ,'Term-Onerous-2020', 3 ,200.752661975435)
INSERT INTO #TEST Values (2020, 'Feb' ,'Term-Onerous-2020', 4 ,200.753413961762)
INSERT INTO #TEST Values (2020, 'Feb' ,'Term-Onerous-2020', 5 ,200.753069962936)
INSERT INTO #TEST Values (2020, 'Feb' ,'Term-Onerous-2020', 6 ,200.752725964404)

INSERT INTO #TEST Values (2020, 'Mar' ,'Term-Onerous-2020', 1 ,0)
INSERT INTO #TEST Values (2020, 'Mar' ,'Term-Onerous-2020', 2 ,100.754317962521)
INSERT INTO #TEST Values (2020, 'Mar' ,'Term-Onerous-2020', 3 ,100.752661975435)
INSERT INTO #TEST Values (2020, 'Mar' ,'Term-Onerous-2020', 4 ,100.753413961762)
INSERT INTO #TEST Values (2020, 'Mar' ,'Term-Onerous-2020', 5 ,100.753069962936)
INSERT INTO #TEST Values (2020, 'Mar' ,'Term-Onerous-2020', 6 ,100.752725964404)

select * from #test

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,629 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,620 questions

1. 1 Reputation point
2020-11-02T19:18:17.297+00:00

Hi,

Try:

``````with CTE_D as
(
select *
from #test as t
cross apply
(
select
cast(cast(t.YearNo as char(4)) + '-' + t.MonthName + '-01' as date) as YearMonth
) as d
cross apply
(
select datepart(month, d.YearMonth) as MonthInt
) as m
)

select
c.*,
case when c.MonthProjectionNumber > c.MonthInt
then 0.0
else
(select sum(s.PeriodValue) from CTE_D as s
where
s.MonthProjectionNumber =
datediff(month, s.YearMonth, c.YearMonth) + 1 and
c.MonthProjectionNumber > datediff(month, s.YearMonth, c.YearMonth)
)
end
from CTE_D as c
``````

Hope this help

2. 24,196 Reputation points
2020-11-03T02:44:45.323+00:00

Thank you so much for posting here.

Per my understanding, the calculation of Jan and MonthProjectionNumber : 1 could also be 0.

A little modification on Gapimex-6252's query as below:

``````;with CTE_D as
(
select *
from #test as t
cross apply
(
select
cast(cast(t.YearNo as char(4)) + '-' + t.MonthName + '-01' as date) as YearMonth
) as d
cross apply
(
select datepart(month, d.YearMonth) as MonthInt
) as m
)

select
c.YearNo,c.monthname,c.UnitOfAccount,c.MonthProjectionNumber,c.PeriodValue,
case when c.MonthProjectionNumber > c.MonthInt or (c.MonthProjectionNumber=1 and c.MonthInt=1)
then 0.0
else
(select sum(s.PeriodValue) from CTE_D as s
where
s.MonthProjectionNumber =
datediff(month, s.YearMonth, c.YearMonth) + 1 and
c.MonthProjectionNumber > datediff(month, s.YearMonth, c.YearMonth)
)
end  Calculating
from CTE_D as c
``````

Output:

``````YearNo	monthname	UnitOfAccount	MonthProjectionNumber	PeriodValue	Calculating
2020	Jan	Term-Onerous-2020	1	250.754	0.000
2020	Jan	Term-Onerous-2020	2	350.754	0.000
2020	Jan	Term-Onerous-2020	3	280.753	0.000
2020	Jan	Term-Onerous-2020	4	375.753	0.000
2020	Jan	Term-Onerous-2020	5	260.753	0.000
2020	Jan	Term-Onerous-2020	6	300.753	0.000
2020	Feb	Term-Onerous-2020	1	0.000	0.000
2020	Feb	Term-Onerous-2020	2	200.754	350.754
2020	Feb	Term-Onerous-2020	3	200.753	0.000
2020	Feb	Term-Onerous-2020	4	200.753	0.000
2020	Feb	Term-Onerous-2020	5	200.753	0.000
2020	Feb	Term-Onerous-2020	6	200.753	0.000
2020	Mar	Term-Onerous-2020	1	0.000	0.000
2020	Mar	Term-Onerous-2020	2	100.754	200.754
2020	Mar	Term-Onerous-2020	3	100.753	481.507
2020	Mar	Term-Onerous-2020	4	100.753	0.000
2020	Mar	Term-Onerous-2020	5	100.753	0.000
2020	Mar	Term-Onerous-2020	6	100.753	0.000
``````

Best regards
Melissa