Actual Value Calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Actual Value Calculation

Author

Topic

Indsqlbeginner
Starting Member

21 Posts

Posted - 2014-02-06 : 06:44:54

Hi Team

Request you help me out to find out the Actual Value for a KPI based on the formula. The table deatils are below.

1.tblFormula : Contains Formula Details


IdFormula      Formula
  1          Finisheddate<=TargetDate
  2          Finisheddate<TargetDate
  3          Finisheddate-TargetDate < 45

2.tblKPI : Contains KPI with the formula and required Details


IDpK IDKPI  IDFormula   Finisheddate   TargetDate
1     1        1        31-12-2013     30-12-2013
2     1        1        30-12-2013     30-12-2013
3     2        2        15-02-2013     15-02-2013
4     2        2        14-02-2013     15-02-2013
5     3        3        17-03-2013     19-03-2013
6     3        3        15-03-2013     19-03-2013
7     3        3        25-03-2013     19-03-2013

OutPut Expected to find the actual goes below.

Desc: if KPI mets thecondtion based on the formula ,have to store them as 1 else 0. since the actual value column is bit type.




IDKPI ActualValue

1       0
1       1
2       0
2       1
3       1
3       1
3       0

Kindly let me know if this is not clear.

Thank you very much in advance. Appreciate your revert.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-06 : 07:21:52

[code]

;with tblKPI
AS(
select 1 as IDpK,1 as IDKPI,1 as IDFormula,'2013/12/31'as Finisheddate,'2013/12/30' as TargetDate union all
select 2,1,1,'2013/12/30','2013/12/30' union all
select 3,2,2,'2013/02/15','2013/02/15' union all
select 4,2,2,'2013/02/14','2013/02/15' union all
select 5,3,3,'2013/03/17','2013/03/19' union all
select 6,3,3,'2013/03/15','2013/03/19' union all
select 7,3,3,'2013/03/25','2013/03/19'
)

select
IDpk
,case
when IDFormula=1 and Finisheddate<=TargetDate then 1
When IDFormula=1 and Finisheddate>TargetDate then 0
when IDFormula=2 and Finisheddate<TargetDate then 1
When IDFormula=2 and Finisheddate>=TargetDate then 0
when IDFormula=3 and datediff(d,TargetDate,Finisheddate) < 45 then 1
When IDFormula=3 and datediff(d,TargetDate,Finisheddate) >= 45 then 0
end as ActualValue

from tblKPI

[/code]

the output
[code]

IDpk ActualValue
1 0
2 1
3 0
4 1
5 1
6 1
7 1
[/code]

only 1 obs
the last row: difference is stil under 45

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb MCP

Indsqlbeginner
Starting Member

21 Posts

Posted - 2014-02-06 : 08:41:02

Thanks for the reply. but this doesn't help me out as this is completely a static one,but since my rows are completly dynamic.

quote:

Originally posted by stepson



;with tblKPI  
AS(
	select 1 as IDpK,1 as IDKPI,1 as IDFormula,'2013/12/31'as Finisheddate,'2013/12/30' as TargetDate union all
	select 2,1,1,'2013/12/30','2013/12/30' union all
	select 3,2,2,'2013/02/15','2013/02/15' union all
	select 4,2,2,'2013/02/14','2013/02/15' union all
	select 5,3,3,'2013/03/17','2013/03/19' union all
	select 6,3,3,'2013/03/15','2013/03/19' union all
	select 7,3,3,'2013/03/25','2013/03/19'
)

select 
	IDpk
	,case 
		  when IDFormula=1 and Finisheddate<=TargetDate then 1 
		  When IDFormula=1 and Finisheddate>TargetDate then 0
		  when IDFormula=2 and Finisheddate<TargetDate then 1 
		  When IDFormula=2 and Finisheddate>=TargetDate then 0
		  when IDFormula=3 and datediff(d,TargetDate,Finisheddate) < 45 then 1 
		  When IDFormula=3 and datediff(d,TargetDate,Finisheddate) >= 45 then 0
	end as ActualValue

	
from tblKPI

the output



IDpk	ActualValue
1	0
2	1
3	0
4	1
5	1
6	1
7	1

only 1 obs
the last row: difference is stil under 45

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb MCP

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-02-06 : 11:17:24

>>Thanks for the reply. but this doesn't help me out as this is
completely a static one,but since my rows are completly dynamic.

I would try and find another way of meeting the requirement as the
only option is dynamic SQL that will be difficult to verify against
injection.

eg:


-- *** Test Data ***
CREATE TABLE #tblFormula
(
	IdFormula int NOT NULL
	,Formula varchar(255) NOT NULL
);
INSERT INTO #tblFormula
VALUES (1, 'Finisheddate<=TargetDate')
	,(2, 'Finisheddate<TargetDate')
	,(3, 'Finisheddate-TargetDate < 45');

CREATE TABLE #tblKPI 
(
	IDpK int NOT NULL
	,IDKPI int NOT NULL
	,IDFormula int NOT NULL
	,Finisheddate datetime NOT NULL
	,TargetDate datetime NOT NULL
);
INSERT INTO #tblKPI
VALUES (1, 1, 1, '20131231', '20131230')
	,(2, 1, 1, '20131230', '20131230')
	,(3, 2, 2, '20130215', '20130215')
	,(4, 2, 2, '20130214', '20130215')
	,(5, 3, 3, '20130317', '20130319')
	,(6, 3, 3, '20130315', '20130319')
	,(7, 3, 3, '20130325', '20130319');
-- *** End Test Data ***


DECLARE @Thens varchar(MAX) = ''
	,@SQL varchar(MAX);

WITH ThenExprs(Expr)
AS
(
	SELECT CHAR(9) + CHAR(9) + 'WHEN IDFormula = ' + CAST(IDFormula AS varchar(20)) +' AND (' + Formula + ') THEN 1'
	FROM #tblFormula
)
SELECT @Thens = @Thens + Expr  + CHAR(13) + CHAR(10)
FROM ThenExprs
-- print @Thens;

SELECT @SQL = 'SELECT IDpK, IDKPI,IDFormula, Finisheddate, TargetDate' + CHAR(13) + CHAR(10)
	+ CHAR(9) + ',CASE' + CHAR(13) + CHAR(10)
	+ @Thens
	+ CHAR(9) + CHAR(9) + 'ELSE 0' + CHAR(13) + CHAR(10)
	+ CHAR(9) + 'END AS ActualValue' + CHAR(13) + CHAR(10)
	+'FROM #tblKPI;';
--print @SQL;

EXEC (@SQL);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources