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
Go to Top of Page

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

Go to Top of Page

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);
Go to Top of Page
   

- Advertisement -