Sql Query Output Join with another table

Question

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 :

ProductId   TotalCalls  TrueCalls   ErrorCalls  PassPercentage
1   6   6   0   100.0
2   1   0   1   85.7
3   33  15  18  92.2

Now I have another Table :

Levels :

LevelId Min Max Bool    ProductId
1   100 100 0   2
2   80  99  0   2
3   60  79  0   2
4   40  59  0   2
5   1   39  1   2
6   0   0   0   2
7   -1  -1  0   2
1   100 100 0   1
2   80  99  0   1
3   60  79  1   1
4   40  59  0   1
5   1   39  0   1
6   0   0   0   1
7   -1  -1  0   1

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 :

ProductId   TotalCalls  TrueCalls   ErrorCalls  PassPercentage  LevelId
1   6   6   0   100.0       1
2   1   0   1   85.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 ??

Maxim Balaganskiy · Accepted Answer · 2013-09-18 23:14:13Z

1

The query would look like

with stats as (
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
)
select s.*, l.LevelId
  from stats s
  join levels l on l.ProductId = s.ProductId and s.PassPercentage between l.Min and l.Max

edited Sep 18, 2013 at 23:14

answered Sep 18, 2013 at 22:52

Maxim Balaganskiy

1,57414 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hannah Vernon Over a year ago

+1 for using a CTE without the actual schema of the question.

Collectives™ on Stack Overflow

Sql Query Output Join with another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related