T-SQL Question

Question

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

Answer

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

Answer

Hi @Rahul Polaboina ,

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

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Share via

T-SQL Question

2 answers

Your answer