none
Need Query RRS feed

  • Question

  • Create Table Temp (DBName Nvarchar(50), MF Numeric(9,2), DP Numeric(9,2), Script Numeric(9,2), Resolve Numeric(9,2),) Insert Into('X','1','5','9','13'),('X','2','6','10','14'),('X','3','7','11','15'),('X','4','8','12','16'), ('Y','1','5','9','13'),('Y','2','6','10','14'),('Y','3','7','11','15'),('Y','4','8','12','16')

    alter table Temp  add MF_score Numeric(9,2)

    alter table Temp  add Script_score  NumeriC(9,2)

    alter table Temp  add Resolve_score  Numeric(9,2)

    alter table Temp  add DP_score Numeric(9,2)

    Sum(MF),Sum(Script),Sum(Resolve),Sum(Dp)

    which one is greater value we have to score as 10 .

    for  example MF is max(sum) value MF_score is 10

    If Script is scond highest we have  to compare Sum(Script) percentage of Sum(Mf) if it is greater to 90 and less than to 100 then Script_score 9

    If Resolve is Thierd highest we have  to compare Sum(Resolve) percentage of Sum(Mf) if it is greater to 80 and less than to 90 then Script_score 8

    If DP is Thierd highest we have  to compare Sum(DP) percentage of Sum(Mf) if it is greater to 70 and less than to 80 then Script_score 7

    Max value  is Dynamic for MF,Script,Resolve,DP

    Wednesday, September 18, 2019 5:56 AM

Answers

  • Hi harikiran2010,

     

    Please try following script .

     
    IF OBJECT_ID('Temp') IS NOT NULL drop table  Temp
    go 
    Create Table Temp 
    (DBName Nvarchar(50), 
    MFO Numeric(9,2), 
    DP Numeric(9,2), 
    Script Numeric(9,2), 
    Resolve Numeric(9,2)) 
    
    Insert Into Temp values 
    ('X','1','5','9','13'),
    ('X','2','6','10','14'),
    ('X','3','7','11','15'),
    ('X','4','8','12','16'), 
    ('Y','1','5','9','13'),
    ('Y','2','6','10','14'),
    ('Y','3','7','11','15'),
    ('Y','4','8','12','16')
    
    alter table Temp  add MF_score Numeric(9,2)
    alter table Temp  add Script_score  NumeriC(9,2)
    alter table Temp  add Resolve_score  Numeric(9,2)
    alter table Temp  add DP_score Numeric(9,2)
    go
    
    ;with cte1 as (
    select DBName, sum(MFO) [sum(MFO)], sum(DP) [sum(DP)],
     sum(Script) [sum(Script)], sum(Resolve) [sum(Resolve)] 
    from Temp
    group by DBName)
    ,cte2 as (
    select DBName,[KPI],[Value] Agg_Value,
    row_number()over(partition by DBName order by [Value] desc) Rank ,
    [Value]*100/max([Value])over(partition by DBName ) Percentage 
    from cte1 
    UNPIVOT ([Value] FOR [KPI] IN ([sum(MFO)],[sum(DP)],[sum(Script)],[sum(Resolve)])) u)
    
    select DBName,KPI,Agg_Value,Rank, 
    case when Rank=1 then 10 else null end Score, 
    case when Rank=1 then null  else Percentage end Percentage 
    from cte2 a
    /*
    DBName       KPI                   Agg_Value     Rank   Score       Percentage
    ------------ --------------------- ------------- ------------------ -----------------
    X            sum(Resolve)          58.00         1      10          NULL
    X            sum(Script)           42.00         2      NULL        72.413793
    X            sum(DP)               26.00         3      NULL        44.827586
    X            sum(MFO)              10.00         4      NULL        17.241379
    Y            sum(Resolve)          58.00         1      10          NULL
    Y            sum(Script)           42.00         2      NULL        72.413793
    Y            sum(DP)               26.00         3      NULL        44.827586
    Y            sum(MFO)              10.00         4      NULL        17.241379
    */
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 19, 2019 7:31 AM

All replies

  • Please post desired result, and expand on the logic behind the scene, it is unclear

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 18, 2019 7:12 AM
    Moderator
  • 1.My Input have MFO,Devops,Resolve,Script Values (From D:G)

    2.Summarize the Input Values From(J:M)
    3.Assign Rank for Top Most Value as 1 and next Top most value Follows  2,3,4
    4.Score for Top Most 10 .(Percentage of 2,3,4 Ranks formula)= Score 42*100/58,26*100/58,10*100/58
    5.all should be dynamic .Rank value should be dynamic.Percentage also should be dynamic



    Wednesday, September 18, 2019 10:40 AM
  • Sorry but I cannot see the picture you posted above

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 18, 2019 10:42 AM
    Moderator
  • Hi harikiran2010,

     

    Please try following script .

     
    IF OBJECT_ID('Temp') IS NOT NULL drop table  Temp
    go 
    Create Table Temp 
    (DBName Nvarchar(50), 
    MFO Numeric(9,2), 
    DP Numeric(9,2), 
    Script Numeric(9,2), 
    Resolve Numeric(9,2)) 
    
    Insert Into Temp values 
    ('X','1','5','9','13'),
    ('X','2','6','10','14'),
    ('X','3','7','11','15'),
    ('X','4','8','12','16'), 
    ('Y','1','5','9','13'),
    ('Y','2','6','10','14'),
    ('Y','3','7','11','15'),
    ('Y','4','8','12','16')
    
    alter table Temp  add MF_score Numeric(9,2)
    alter table Temp  add Script_score  NumeriC(9,2)
    alter table Temp  add Resolve_score  Numeric(9,2)
    alter table Temp  add DP_score Numeric(9,2)
    go
    
    ;with cte1 as (
    select DBName, sum(MFO) [sum(MFO)], sum(DP) [sum(DP)],
     sum(Script) [sum(Script)], sum(Resolve) [sum(Resolve)] 
    from Temp
    group by DBName)
    ,cte2 as (
    select DBName,[KPI],[Value] Agg_Value,
    row_number()over(partition by DBName order by [Value] desc) Rank ,
    [Value]*100/max([Value])over(partition by DBName ) Percentage 
    from cte1 
    UNPIVOT ([Value] FOR [KPI] IN ([sum(MFO)],[sum(DP)],[sum(Script)],[sum(Resolve)])) u)
    
    select DBName,KPI,Agg_Value,Rank, 
    case when Rank=1 then 10 else null end Score, 
    case when Rank=1 then null  else Percentage end Percentage 
    from cte2 a
    /*
    DBName       KPI                   Agg_Value     Rank   Score       Percentage
    ------------ --------------------- ------------- ------------------ -----------------
    X            sum(Resolve)          58.00         1      10          NULL
    X            sum(Script)           42.00         2      NULL        72.413793
    X            sum(DP)               26.00         3      NULL        44.827586
    X            sum(MFO)              10.00         4      NULL        17.241379
    Y            sum(Resolve)          58.00         1      10          NULL
    Y            sum(Script)           42.00         2      NULL        72.413793
    Y            sum(DP)               26.00         3      NULL        44.827586
    Y            sum(MFO)              10.00         4      NULL        17.241379
    */
    

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 19, 2019 7:31 AM