How to mirror a report requirement in MS EXCEL in SQL SERVER 2012

saddsfdsfdsfsgffhgfhfhgfhhgfhgfhgfhgfhgfjj

please provide sample data not as an image (as we do not yet have technology that can scrape data from images) but as follows for ex

create table dbo.sample(Tc decimal(10,2), tc int, age decimal(10,2))

insert into sample
select x, y,z union
select a,b,c 

and for the sample data you provided also indicate your expected results.

Sorry cant help you until you provide sample data as requested above.

please provide sample data not as excel or image but as follows

what are the column names for the table?

for this data sample you have give please provide expected results?

The expected result should be derived from the formulae mentioned in the previous query and is as follows

23 17.13%
24 17.58%
25 18.10%
26 18.53%
27 18.88%
28 19.27%
29 19.59%
30 19.78%
31 20.01%
32 20.22%
33 20.42%
34 20.59%
35 20.69%
36 20.82%
37 20.92%
38 21.08%
39 21.13%
40 21.17%
41 21.22%
42 21.25%
43 21.28%
44 21.31%
45 21.32%
46 21.33%
47 21.33%
48 21.34%
49 21.34%
50 21.34%
51 21.35%
52 21.35%
53 21.35%
54 21.36%
55 21.36%
56 21.36%
57 21.36%
58 21.36%
59 21.37%
60 21.37%

Your explanations are very hard to comprehend, . try this for starters.

create table #timingCurves(age int, TCValue float, cnl float)
declare @cnl float = 16.52;
 INSERT INTO #timingCurves

select 0 as age, 0 as tc, 0 as cnl union 
select 1 as age, 0.0001 as tc, 0 as cnl union 
select 2 as age, 0.0008 as tc, 0.0003 as cnl union 
select 3 as age, 0.0035 as tc, 0.0011 as cnl union 
select 4 as age, 0.0121 as tc, 0.0034 as cnl union 
select 5 as age, 0.0299 as tc, 0.007 as cnl union 
select 6 as age, 0.0619 as tc, 0.0144 as cnl union 
select 7 as age, 0.103 as tc, 0.0255 as cnl union 
select 8 as age, 0.1506 as tc, 0.035 as cnl union 
select 9 as age, 0.2007 as tc, 0.0474 as cnl union 
select 10 as age, 0.254 as tc, 0.0579 as cnl union 
select 11 as age, 0.3064 as tc, 0.0727 as cnl union 
select 12 as age, 0.3604 as tc, 0.0837 as cnl union 
select 13 as age, 0.413 as tc, 0.0938 as cnl union 
select 14 as age, 0.461 as tc, 0.1015 as cnl union 
select 15 as age, 0.506 as tc, 0.1118 as cnl union 
select 16 as age, 0.5586 as tc, 0.117 as cnl union 
select 17 as age, 0.6007 as tc, 0.1232 as cnl union 
select 18 as age, 0.6406 as tc, 0.1319 as cnl union 
select 19 as age, 0.6781 as tc, 0.1402 as cnl union 
select 20 as age, 0.7138 as tc, 0.1497 as cnl union 
select 21 as age, 0.7472 as tc, 0.1585 as cnl union 
select 22 as age, 0.7729 as tc, 0.1652 as cnl union 
select 23 as age, 0.8018 as tc, 0.1713 as cnl union 
select 24 as age, 0.8228 as tc, 0.1758 as cnl union 
select 25 as age, 0.8471 as tc, 0.181 as cnl union 
select 26 as age, 0.8672 as tc, 0.1853 as cnl union 
select 27 as age, 0.8837 as tc, 0.1888 as cnl union 
select 28 as age, 0.902 as tc, 0.1927 as cnl union 
select 29 as age, 0.9168 as tc, 0.1959 as cnl union 
select 30 as age, 0.9257 as tc, 0.1978 as cnl union 
select 31 as age, 0.9365 as tc, 0.2001 as cnl union 
select 32 as age, 0.9463 as tc, 0.2022 as cnl union 
select 33 as age, 0.9558 as tc, 0.2042 as cnl union 
select 34 as age, 0.9633 as tc, 0.2059 as cnl union 
select 35 as age, 0.9682 as tc, 0.2069 as cnl union 
select 36 as age, 0.9741 as tc, 0.2082 as cnl union 
select 37 as age, 0.9792 as tc, 0.2092 as cnl union 
select 38 as age, 0.9863 as tc, 0.2108 as cnl union 
select 39 as age, 0.9886 as tc, 0.2113 as cnl union 
select 40 as age, 0.9909 as tc, 0.2117 as cnl union 
select 41 as age, 0.9932 as tc, 0.2122 as cnl union 
select 42 as age, 0.9945 as tc, 0.2125 as cnl union 
select 43 as age, 0.9958 as tc, 0.2128 as cnl union 
select 44 as age, 0.9971 as tc, 0.2131 as cnl union 
select 45 as age, 0.9976 as tc, 0.2132 as cnl union 
select 46 as age, 0.9981 as tc, 0.2133 as cnl union 
select 47 as age, 0.9983 as tc, 0.2133 as cnl union 
select 48 as age, 0.9985 as tc, 0.2134 as cnl union 
select 49 as age, 0.9987 as tc, 0.2134 as cnl union 
select 50 as age, 0.9989 as tc, 0.2134 as cnl union 
select 51 as age, 0.999 as tc, 0.2135 as cnl union 
select 52 as age, 0.9991 as tc, 0.2135 as cnl union 
select 53 as age, 0.9992 as tc, 0.2135 as cnl union 
select 54 as age, 0.9994 as tc, 0.2136 as cnl union 
select 55 as age, 0.9995 as tc, 0.2136 as cnl union 
select 56 as age, 0.9996 as tc, 0.2136 as cnl union 
select 57 as age, 0.9997 as tc, 0.2136 as cnl union 
select 58 as age, 0.9998 as tc, 0.2136 as cnl union 
select 59 as age, 0.9999 as tc, 0.2137 as cnl union 
select 60 as age, 1 as tc, 0.2137 as cnl  

;with cteEel
as
(
select age, 
       age + 1  as nextage,
       TCValue , case
 when age <= 22 and age >= 1 then (@cnl/TCValue)
 when age = 0 then 0
 when age > 22 then  (cnl/TCValue) * 100
 end as EstEndingLoss	   
  From #timingCurves 
)

select src.age, 
       nextage,
       src.TCValue * 100 as TCValue, 
	   src.EstEndingLoss ,
	   (src.EstEndingLoss * tgt.TCValue)  as CNLForecastValue
from cteEel src
left join #timingCurves tgt on src.nextage = tgt.age

drop table #timingCurves

Thanks:slight_smile:

That worked perfectly:slight_smile:

I was wondering if it's possible to achieve the same results without hard-coding the values & creating the temp table

Well you can get the data from your underlying table then no need for that sample data. that is why its called sample data :rofl:

yes because it ends at 60, there is no 61. so you will have to do a special case there.

select src.age, 
       nextage,
	   src.TCValue * 100 as TCValue, 
	   src.EstEndingLoss ,
	   case 
	      when tgt.TCValue is null then src.EstEndingLoss
		  else (src.EstEndingLoss * tgt.TCValue) 
	      end as CNLForecastValue
from cteEel src
left join #timingCurves tgt on src.nextage = tgt.age