sqlsql-serversql-server-2008aptitude

How to calculate overall percentage of two items in SQL?


I have two teams A and B. A team member will enter records. B team member will evaluate the records. Each team has their own daily targets, like A's Daily Target is 30 entries and B's Daily Target is 60 evaluations. So, in some situations A or B team member might do the both the jobs.During that, how should we calculate overall percentage against 100% ?

In clear, lets say A team member has done 15 entries out of 30 entries(target), then his individual achievement percentage is 50%.The same logic applies for B Team member too. I am able to calculate individual team member achievement percentage.

But what if a person from any team works on both the things. I can't sum up them or do average as both were doing different jobs. SQL Structure Can you guys help me out with any suggestions or formulas in SQL ?

I am able to write query for calculating percentage if its single, for eg. in the picture you can observer the first case, where I can calculate percentage as that person did only entry,so his target would be 30.Then achievement percentage would be 02*100/30 %. Same applies for second one as 10*100/60 %. I got struck in the third case where the user performs both the different actions. How should I calculate his overall percentage? Please suggest.


Solution

  • If I really understand what do you want, try next code:

    use [your_db_name];
    
    create table dbo.test17
    (
        [user] int null,
        [date] datetime null,
        records_entered int not null,
        records_evaluated int not null
    );
    
    insert into dbo.test17
    values (1, '2017-04-17', 2, 0), (2, '2017-04-17', 0, 10), (3, '2017-04-17', 5, 20)
    
    select 
        [user],
        [date],
        (sum(records_entered) * 2 + sum(records_evaluated)) * 100.0 / 60 as target
    from dbo.test17
    group by
        [user]
        ,[date]