November 17, 2010 at 11:44 am
Hi,
I have a table like below -
Create table #table(ID int,Name varchar(10),Date datetime,Value int, Status varchar(5))
Insert into #table (ID,Name,Date,Value,Status) Select 1,'Display1','2010-01-01',2000,'OK'
UNION SELECT 1,'Display1','2010-01-01',1000,'BAD'
UNION SELECT 2,'Display2','2010-01-01',4000,'OK'
Select * from #table
IDName Date ValueStatus
1Display12010-01-01 00:00:00.0001000BAD
1Display12010-01-01 00:00:00.0002000OK
2Display22010-01-01 00:00:00.0004000OK
In this I want to calculate Success percentage from value column of each Name for latest date
for e.g. if there are two instances of Display1 for the same date with one Status as OK(2000) and another as BAD(1000), then it should give me Success percentage as
2000/(2000+1000) * 100 = 66.67 i.e. Value of OK / Total Value of Display1 * 100
For Display2, Since I have only one row with Status OK, so It should be shown as SuccessRate 100
else if it would have been only BAD then it should be 0.
So the expected output should be like -
IDName DateSuccessRate
1Display12010-01-01 00:00:00.00066.67
1Display22010-01-01 00:00:00.000100
Does anybody knows how to achieve this? I tried at my end but no luck..:crazy:
:rolleyes:
November 17, 2010 at 12:44 pm
C'mon Joe. Not only is the table an obvious example instead of true DDL (there is no way someone could use THAT many reserved keywords at once... just... no...), you skipped the question, which is to take a row valued weighted average, not just a rowcounted one (What, you're going to put 2000 entries into the table with 'OK'?). It's not that uncommon.
You need to subquery. Your first challenge is to get your totals on Name, then by Bad or OK status.
So, start with this:
SELECT
ID,
[Name],
[Date],
SUM( [Value]) AS TotValue,
SUM( CASE [Status] WHEN 'OK' THEN [Value] ELSE 0 END) AS TotalOK,
SUM( CASE [Status] WHEN 'BAD' THEN [Value] ELSE 0 END) AS TotalBad
FROM
#table
GROUP BY
ID,
[Name],
[Date]
From there, your calculations become MUCH easier.
Now you just need to wrap that in a results query:
;WITH cte AS
(
SELECT
ID,
[Name],
[Date],
SUM( [Value]) AS TotValue,
SUM( CASE [Status] WHEN 'OK' THEN [Value] ELSE 0 END) AS TotalOK,
SUM( CASE [Status] WHEN 'BAD' THEN [Value] ELSE 0 END) AS TotalBad --Included just as a doublecheck, not really needed
FROM
#table
GROUP BY
ID,
[Name],
[Date]
)
SELECT
ID, [Name], [Date],
CAST( (Convert( decimal ( 18, 2), TotalOK) / Convert( decimal ( 18, 2), TotValue))*100 AS DECIMAL(18,2)) AS SuccessRate
FROM
cte
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 17, 2010 at 1:30 pm
Thanks a lot Craig... it served the purpose....
I just missed to make column of totals which I was calculating on the run... 😛
:rolleyes:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply