September 18, 2013 at 4:36 pm
I have a query which gives me the following output :
select
PD.ProductId, TotalCalls = COUNT(DISTINCT PD.LogId),
TrueCalls = COUNT(DISTINCT case when PD.ExceptionCode = ' ' then PD.LogId END),
ErrorCalls =COUNT(DISTINCT case when PD.ExceptionCode != ' ' then PD.LogId END),
PassPercentage = CONVERT(DECIMAL(10,1),100 - (CAST(COUNT(DISTINCT case when PD.ExceptionCode != ' ' then PD.LogId END) as float)/CAST(COUNT(PD.LogId) as float)*100))
from
Log P
INNER JOIN LogProduct PD ON P.LogId = PD.LogId
WHERE
(ResponseTime < '2013-09-28' and RequestTime > '2013-09-01')
Group By
PD.ProductId
It gives me the following output :
ProductIdTotalCallsTrueCallsErrorCallsPassPercentage
1660100.0
210185.7
333151892.2
Now I have another Table :
Levels :
LevelIdMinMaxBoolProductId
110010002
2809902
3607902
4405902
513912
60002
7-1-102
110010001
2809901
3607911
4405901
513901
60001
7-1-101
What I would like to do is compare the output of the first query and add a new LevelId column :
example :
I am looking for an output like this :
ProductIdTotalCallsTrueCallsErrorCallsPassPercentage LevelId
1660100.0 1
210185.7 2
The logic here is that : I would like to compare the PassPercentage for each row for that particular product and find out which level it falls in .
In the example above : PassPercentage is 85.7 for product 2 . If you check the Levels table above for ProductId 2 ,
Level 2 should be chosen as 80 < 87.5 < 99
I cannot figure out How I can do this..
Please let me know how I go forward from here ... or give me ideas of what I ought to do ??
September 18, 2013 at 5:49 pm
Put your fisrt query in a subquery or a CTE (Common table expression).
Then use a (Non-Equi) JOIN using BETWEEN for the ranges and equal for the products.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply