How to calculate table values dynamically and find Min and Max target from parameter in SQL Server

I want to calculate table values dynamically and find Min and Max target from parameter

I have the following target table with data:

Name        Quarter Method  MethodID    GivenTarget Percentage 
Json Ray    1       AB      1           153000      0.2 
Json Ray    1       BC      2           208000      0.5 
Json Ray    1       CD      3           252000      0.8 
Json Ray    1       DE      4           450000      0.2 
Json Ray    2       AB      1           223000      1.4 
Json Ray    2       BC      2           308000      1.8 
Json Ray    2       CD      3           352000      1.2 
Json Ray    2       DE      4           550000      1.5 
Json Ray    3       AB      1           323000      2.8 
Json Ray    3       BC      2           408000      2.3 
Json Ray    3       CD      3           552000      2.4 
Json Ray    3       DE      4           650000      2.9 
Json Ray    4       AB      1           423000      2.2 
Json Ray    4       BC      2           508000      3.3 
Json Ray    4       CD      3           652000      3.5 
Json Ray    4       DE      4           750000      3.9 

I am passing a parameter AchievedTarget for a particular quarter (1, 2, 3, 4).

IF quarter = 1 and AchievedTarget = 250000 I want the record where AchievedTarget lies in between

Output should be like:

IF quarter = 1 and AchievedTarget = 400000 I want the record where AchievedTarget lies in between

Output should be like:

IF quarter = 2 and AchievedTarget = 400000, I want to sum GivenTarget column from Target tables where Quarter = 1, Method = ’AB’, Method_ID = 1 With Quarter= 2 Method = ’AB’ Method_ID = 1 and so on for add each respective method and Method_ID with quarters, and need to pick Percentage values from current Quarter i.e Quarter 2 in this case with respective Method and Method_ID

Expected New Target tables for Quarter 2 should be as follows:

Now depend upon above table I want to calculate Min_Target and Max_Target as calculated earlier

Expected output with Quarter= 2 and AchievedTarget= 500000 should be:

Following is the code which i was trying, its gives me output for Quarter = 1 only

--DECLARE Input Parameters
DECLARE @AchievedTarget Money =200000                          
DECLARE @Name varchar(30)='Json Ray' , @Quarter int =1, @Mothod varchar(10)='AB'    

--DECLARE Ouput variable
DECLARE @Min_Target Money, @Max_Target Money, @Min_Percenatge float ,@Max_Percenatge float                         

--Finding Min_Target    
SET @Min_Target=(SELECT TOP 1 [GivenTarget] as Min_Target
FROM [Target]
WHERE  [Name] = @Name
AND Quarter = @Quarter 
AND Mothod =@Mothod 
and [GivenTarget] < @AchievedTarget
order by [GivenTarget] desc)
IF @Min_Target IS Null 
SET @Min_Target=(@AchievedTarget)

--Finding Max_Target
SET @Min_Target=(SELECT TOP 1 [GivenTarget] as Min_Target
FROM [Target ]
WHERE  [Name] = @Name
AND Quarter = @Quarter 
AND Mothod =@Mothod 
and [GivenTarget] < @AchievedTarget
order by [GivenTarget] ASC)
IF @Max_Target IS Null 
SET @Max_Target=(@AchievedTarget)

--Finding @Min_Percenatge
SET @Min_Percenatge=(SELECT TOP 1 [Percenatge] AS Min_Percenatge
from [Target ]  
WHERE  [Name] = @Name
AND Quarter = @Quarter 
AND Mothod =@Mothod  
AND [GivenTarget] in (@Min_Target,@Max_Target))

--Finding @Max_Percenatge
SET @Max_Percenatge=(SELECT TOP 1 [Percenatge] AS Max_Percenatge
from [Target ]  
WHERE  [Name] = @Name 
AND Quarter = @Quarter 
AND Mothod =@Mothod 
AND [GivenTarget] in (@Min_Target,@Max_Target) 
ORDER BY [Percenatge] DESC)

--Display Ouput
SELECT @Name, @Quarter,@Mothod ,@Min_Target, @Max_Target, @Min_Percenatge,@Max_Percenatge

I am not able to calculate table values dynamically and find Min and Max target from parameter quarter.

Answer

I have put together a set of sample data and query that will do this. I have removed references to Mothod, as you only have 1 row per Mothod per quarter. You can add the column back in to my solution if you have more rows that your sample.

First set up the table and test data

declare @comm table(Name varchar(20), Qtr int, Mothod varchar(2), Method_ID int, GivenTarget int, Pct decimal(5,2))
insert @comm values 
('Json Ray',1,'AB',1,153000,0.2 )
,('Json Ray',1,'BC',2,208000,0.5 )
,('Json Ray',1,'CD',3,252000,0.8 )
,('Json Ray',1,'DE',4,450000,0.2 )
,('Json Ray',2,'AB',1,223000,1.4 )
,('Json Ray',2,'BC',2,308000,1.8 )
,('Json Ray',2,'CD',3,352000,1.2 )
,('Json Ray',2,'DE',4,550000,1.5 )
,('Json Ray',3,'AB',1,323000,2.8 )
,('Json Ray',3,'BC',2,408000,2.3 )
,('Json Ray',3,'CD',3,552000,2.4 )
,('Json Ray',3,'DE',4,650000,2.9 )
,('Json Ray',4,'AB',1,423000,2.2 )
,('Json Ray',4,'BC',2,508000,3.3 )
,('Json Ray',4,'CD',3,652000,3.5 )
,('Json Ray',4,'DE',4,750000,3.9 )

Add a top row in case as a catch for anyone who has achieved higher than your top target

insert @comm
select Name, qtr, null, null, 999999, max(pct) from @comm group by Name, qtr

Set up your conditions. I have taken out Mothod as you only have one row per Mothos per Qtr

DECLARE @AchievedTarget Money                          
DECLARE @Name varchar(30), @Quarter int 
select @AchievedTarget=500000,  @Name='Json Ray' , @Quarter=2

Use a CTE to get cumulative totals per qtr, and a second CTE to flatten each row so it also had the values of the previous row. I’ve used row_number to make sure I correctly order your rows, even though in your sample date mothod_id appears to also have the same data.

The final query then simply finds the correct row out of the CTE.

;with c as (
select Name, upto as qtr, Mothod, Method_ID, 
    sum(GivenTarget) as GivenTarget, 
    max(case when c.qtr=q.upto then c.pct else 0 end) as Pct,
    row_number() over (partition by Name, upto order by sum(GivenTarget)) as rn 
from @comm c
join (select distinct qtr as upto from @comm) q on c.qtr<=upto
group by Name, upto, Mothod, Method_ID
)
, c2 as (
select c.Name, c.Qtr, c.GivenTarget as MaxTarget, c.Pct as MaxPct, 
    isnull(c2.GivenTarget,0) as MinTarget, isnull(c2.Pct,0) as MinPct 
from c
left join c c2 on c2.name=c.name and c2.qtr=c.qtr and c2.rn=c.rn-1
where c.name=@name and c.qtr=@Quarter 
)
select Name, Qtr, MinTarget, MaxTarget, MinPct, MaxPct 
from c2
where @AchievedTarget>MinTarget and @AchievedTarget<=MaxTarget

Result, using @qtr=2 and @achieved=500000:

Name       Qtr  MinTarget   MaxTarget   MinPct  MaxPct
Json Ray    2   376000      516000      1.40    1.80