Segoe UI 0 PPFM_JM None 8c0ca631-271a-475a-8327-978db832f048 DataSource1 WITH DATA AS ( SELECT p.player_nm, p.game_dt, p.player_current_club_dsc, p.[best_position], p.[Best_Role], p.pos_nm, p.role_nm, p.duty_nm, p.prd_total AS key_attrib_tot, p1.prd_total AS pref_attrib_total, p.prd_total + p1.prd_total AS attrib_tot, p.prd_score*100 AS key_score, p1.prd_score*100 AS pref_score, ((CAST((p.prd_total + p1.prd_total) AS DECIMAL(5,2))/CAST((p.prd_count + p1.prd_count) AS DECIMAL(5,2)))/20)*100 AS attrib_score, p.prd_score_order AS pos_order FROM dbo.players_dataset_v p INNER JOIN dbo.players_dataset_v p1 ON p.game_dt = p1.game_dt AND p.player_unique_id = p1.player_unique_id AND p.pos_nm = p1.pos_nm AND p.role_nm = p1.role_nm AND p.duty_nm = p1.duty_nm WHERE p.attrib_importance_ind = 'K' AND p1.attrib_importance_ind = 'P' ),PERS_ORDER AS ( SELECT *, RANK() OVER (PARTITION BY p.pos_nm, p.role_nm, p.duty_nm, p.player_current_club_dsc, p.game_dt ORDER BY key_score DESC) AS Key_Order, RANK() OVER (PARTITION BY p.pos_nm, p.role_nm, p.duty_nm, p.player_current_club_dsc, p.game_dt ORDER BY attrib_score DESC) AS All_Order FROM DATA p ),NATURAL AS ( SELECT DISTINCT p.*, CASE WHEN p.pos_nm = p.best_position THEN 'Yes' ELSE 'No' END AS Natural_Position, CASE WHEN p.pos_nm = p.best_position AND p.role_nm = p.Best_Role THEN 'Yes' ELSE 'No' END AS Natural_Pos_Role FROM PERS_ORDER p INNER JOIN dbo.players_dataset_v v ON p.Player_nm = v.Player_nm AND p.pos_nm = v.pos_nm AND p.role_nm = v.role_nm AND p.duty_nm = v.duty_nm --AND v.Club = @Club --AND v.game_date = @Game_Date --AND v.Player_Name = @Player ) SELECT * FROM NATURAL WHERE All_Order <= 3 ORDER BY Natural_Position DESC, All_Order player_nm System.String game_dt System.DateTime player_current_club_dsc System.String best_position System.String pos_nm System.String Best_Role System.String role_nm System.String duty_nm System.String key_attrib_tot System.Int32 pref_attrib_total System.Int32 attrib_tot System.Int32 key_score System.Decimal pref_score System.Decimal attrib_score System.Decimal pos_order System.Int64 Key_Order System.Int64 All_Order System.Int64 Natural_Position System.String Natural_Pos_Role System.String 5.11938cm 5.80729cm 5.0961cm 2.14313cm 2.5cm 2.5cm 2.5cm 2.5cm 2.5cm 0.6cm true true Textbox47 LightGreen 2pt 2pt 2pt 2pt true true FM-Deduced Natural Position & Role Textbox44 LightGreen 2pt 2pt 2pt 2pt 6 true true Team Order Textbox50 LightGreen 2pt 2pt 2pt 2pt 2 0.6cm true true Player LightGreen 2pt 2pt 2pt 2pt true true Position LightGreen 2pt 2pt 2pt 2pt true true Role LightGreen 2pt 2pt 2pt 2pt true true Duty LightGreen 2pt 2pt 2pt 2pt true true Key Score LightGreen 2pt 2pt 2pt 2pt true true Pref Score LightGreen 2pt 2pt 2pt 2pt true true Total Score LightGreen 2pt 2pt 2pt 2pt true true Key Att Order LightGreen 2pt 2pt 2pt 2pt true true All Att Order LightGreen 2pt 2pt 2pt 2pt 0.6cm true true =Fields!player_nm.Value 2pt 2pt 2pt 2pt true true =Fields!pos_nm.Value 2pt 2pt 2pt 2pt true true =Fields!role_nm.Value 2pt 2pt 2pt 2pt true true =Fields!duty_nm.Value 2pt 2pt 2pt 2pt true true =Fields!key_score.Value =IIF(Fields!key_score.Value >= 50, "Lime","White") 2pt 2pt 2pt 2pt true true =Fields!pref_score.Value =IIF(Fields!pref_score.Value >= 50, "Lime","White") 2pt 2pt 2pt 2pt true true =Fields!attrib_score.Value =IIF(Fields!attrib_score.Value >= 50, "Lime","White") 2pt 2pt 2pt 2pt true true =Fields!Key_Order.Value =IIF(Fields!Key_Order.Value = 1,"Lime","White") 2pt 2pt 2pt 2pt true true =Fields!All_Order.Value =IIF(Fields!All_Order.Value = 1,"Lime","White") 2pt 2pt 2pt 2pt After After =Fields!Natural_Position.Value Equal Yes =Fields!All_Order.Value LessThanOrEqual 5 =Fields!Natural_Pos_Role.Value Equal Yes =Fields!pos_nm.Value =Fields!role_nm.Value Descending =Fields!attrib_score.Value Players_Dataset 0.14911cm 1.8cm 30.6659cm 5.11938cm 5.80729cm 5.0961cm 2.14313cm 2.5cm 2.5cm 2.5cm 2.5cm 2.5cm 0.6cm true true Textbox52 Khaki 2pt 2pt 2pt 2pt true true FM-Deduced Natural Positions Textbox44 Khaki 2pt 2pt 2pt 2pt 6 true true Team Order Textbox50 Khaki 2pt 2pt 2pt 2pt 2 0.6cm true true Player Khaki 2pt 2pt 2pt 2pt true true Position Khaki 2pt 2pt 2pt 2pt true true Role Khaki 2pt 2pt 2pt 2pt true true Duty Khaki 2pt 2pt 2pt 2pt true true Key Score Khaki 2pt 2pt 2pt 2pt true true Pref Score Khaki 2pt 2pt 2pt 2pt true true Total Score Khaki 2pt 2pt 2pt 2pt true true Key Att Order Khaki 2pt 2pt 2pt 2pt true true All Att Order Khaki 2pt 2pt 2pt 2pt 0.6cm true true =Fields!player_nm.Value 2pt 2pt 2pt 2pt true true =Fields!pos_nm.Value 2pt 2pt 2pt 2pt true true =Fields!role_nm.Value 2pt 2pt 2pt 2pt true true =Fields!duty_nm.Value 2pt 2pt 2pt 2pt true true =Fields!key_score.Value =IIF(Fields!key_score.Value >= 50, "Lime","White") 2pt 2pt 2pt 2pt true true =Fields!pref_score.Value =IIF(Fields!pref_score.Value >= 50, "Lime","White") 2pt 2pt 2pt 2pt true true =Fields!attrib_score.Value =IIF(Fields!attrib_score.Value >= 50, "Lime","White") 2pt 2pt 2pt 2pt true true =Fields!Key_Order.Value =IIF(Fields!Key_Order.Value = 1,"Lime","White") 2pt 2pt 2pt 2pt true true =Fields!All_Order.Value =IIF(Fields!All_Order.Value = 1,"Lime","White") 2pt 2pt 2pt 2pt After After =Fields!Natural_Position.Value Equal No =Fields!All_Order.Value LessThanOrEqual 5 =Fields!All_Order.Value =Fields!attrib_score.Value Descending Players_Dataset 3.17639cm 0.14911cm 1.8cm 30.6659cm 1 End true 1.97639cm 0.14911cm 1.02361cm 29.31652cm 2 End true 5.15278cm 0.14911cm 1.02361cm 29.31652cm 3 5.11938cm 5.80729cm 5.09609cm 2.14313cm 2.5cm 2.5cm 2.5cm 2.5cm 2.5cm 0.6cm true true Textbox52 LightGrey 2pt 2pt 2pt 2pt true true FM-Deduced Non-Natural Positions Textbox44 LightGrey 2pt 2pt 2pt 2pt 6 true true Team Order Textbox50 LightGrey 2pt 2pt 2pt 2pt 2 0.6cm true true Player LightGrey 2pt 2pt 2pt 2pt true true Position LightGrey 2pt 2pt 2pt 2pt true true Role LightGrey 2pt 2pt 2pt 2pt true true Duty LightGrey 2pt 2pt 2pt 2pt true true Key Score LightGrey 2pt 2pt 2pt 2pt true true Pref Score LightGrey 2pt 2pt 2pt 2pt true true Total Score LightGrey 2pt 2pt 2pt 2pt true true Key Att Order LightGrey 2pt 2pt 2pt 2pt true true All Att Order LightGrey 2pt 2pt 2pt 2pt 0.6cm true true =Fields!player_nm.Value 2pt 2pt 2pt 2pt true true =Fields!pos_nm.Value 2pt 2pt 2pt 2pt true true =Fields!role_nm.Value 2pt 2pt 2pt 2pt true true =Fields!duty_nm.Value 2pt 2pt 2pt 2pt true true =Fields!key_score.Value =IIF(Fields!key_score.Value >= 50, "Lime","White") 2pt 2pt 2pt 2pt true true =Fields!pref_score.Value =IIF(Fields!pref_score.Value >= 50, "Lime","White") 2pt 2pt 2pt 2pt true true =Fields!attrib_score.Value =IIF(Fields!attrib_score.Value >= 50, "Lime","White") 2pt 2pt 2pt 2pt true true =Fields!Key_Order.Value =IIF(Fields!Key_Order.Value = 1,"Lime","White") 2pt 2pt 2pt 2pt true true =Fields!All_Order.Value =IIF(Fields!All_Order.Value = 1,"Lime","White") 2pt 2pt 2pt 2pt After After =Fields!Natural_Position.Value Equal No =Fields!All_Order.Value LessThanOrEqual 5 =Fields!All_Order.Value =Fields!attrib_score.Value Descending Players_Dataset 6.35278cm 0.14911cm 1.8cm 30.66589cm 4 3.20975in