June 10, 2022 at 12:47 am
I have a table with data (just including the relevant stuff here) and each line has a TeamId which is the serving team and the OppId which is the receiving team. I also have a flag for if it is a serve or different stroke in the rally and then finally a bit column that says whether or not the TeamId team won the point. I am trying to find out the % of time a team wins when the serve and wins when they receive and I do this in excel based on the columns I have included below. So here is an example Team A and team B
Team A serves 10 times (isServe flag is set to 1) and team B receives 10 times. If Team A wins 6 times then team A point scored at 60% and team B sideout% was 40%. Alternatively Team B serves 8 times and team A receives 8 times and if Team B wins 4 times in this scenario then Team B point scored at 50% and Team A sideout% was 50%.
Here is a table and data and I am trying to figure out how to accomplish this. Thank you for your help!
In the DataSet Below
Team 101 has a PointScore% of 45% and SideOut% of 71%
Team 102 has a PointScore% of 29% and SideOut% of 55%
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Data]') AND type in (N'U'))
DROP TABLE [dbo].[tbl_Data]
GO
CREATE TABLE [dbo].[tbl_Data](
[Insert_Order] [bigint] NOT NULL,
[Date] [date] NOT NULL,
[Match_Id] [bigint] NOT NULL,
[TeamId] [int] NOT NULL,
[OppId] [int] NOT NULL,
[isServe] [bit] NOT NULL,
[CtTeamWinRally] [bit] NULL
) ON [PRIMARY]
INSERT INTO [dbo].[tbl_Data]
([Insert_Order]
,[Date]
,[Match_Id]
,[TeamId]
,[OppId]
,[isServe]
,[CtTeamWinRally])
SELECT '1','2016-09-21','1','101','102','1','0' UNION ALL
SELECT '2','2016-09-21','1','102','101','1','1' UNION ALL
SELECT '3','2016-09-21','1','101','102','0','0' UNION ALL
SELECT '4','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '5','2016-09-21','1','102','101','1','0' UNION ALL
SELECT '6','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '7','2016-09-21','1','101','102','1','0' UNION ALL
SELECT '8','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '9','2016-09-21','1','101','102','0','0' UNION ALL
SELECT '10','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '11','2016-09-21','1','102','101','1','1' UNION ALL
SELECT '12','2016-09-21','1','101','102','0','0' UNION ALL
SELECT '13','2016-09-21','1','102','101','1','0' UNION ALL
SELECT '14','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '15','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '16','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '17','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '18','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '19','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '20','2016-09-21','1','101','102','1','0' UNION ALL
SELECT '21','2016-09-21','1','102','101','1','1' UNION ALL
SELECT '22','2016-09-21','1','101','102','0','0' UNION ALL
SELECT '23','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '24','2016-09-21','1','102','101','1','0' UNION ALL
SELECT '25','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '26','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '27','2016-09-21','1','101','102','1','1' UNION ALL
SELECT '28','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '29','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '30','2016-09-21','1','101','102','1','0' UNION ALL
SELECT '31','2016-09-21','1','102','101','1','0' UNION ALL
SELECT '32','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '33','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '34','2016-09-21','1','101','102','1','0' UNION ALL
SELECT '35','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '36','2016-09-21','1','101','102','0','0' UNION ALL
SELECT '37','2016-09-21','1','102','101','1','0' UNION ALL
SELECT '38','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '39','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '40','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '41','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '42','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '43','2016-09-21','1','101','102','1','0' UNION ALL
SELECT '44','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '45','2016-09-21','1','101','102','0','0' UNION ALL
SELECT '46','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '47','2016-09-21','1','101','102','0','0' UNION ALL
SELECT '48','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '49','2016-09-21','1','101','102','0','0' UNION ALL
SELECT '50','2016-09-21','1','102','101','1','0' UNION ALL
SELECT '51','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '52','2016-09-21','1','101','102','1','0' UNION ALL
SELECT '53','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '54','2016-09-21','1','101','102','0','0' UNION ALL
SELECT '55','2016-09-21','1','102','101','1','0' UNION ALL
SELECT '56','2016-09-21','1','101','102','1','1' UNION ALL
SELECT '57','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '58','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '59','2016-09-21','1','101','102','1','0' UNION ALL
SELECT '60','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '61','2016-09-21','1','102','101','1','0' UNION ALL
SELECT '62','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '63','2016-09-21','1','101','102','1','1' UNION ALL
SELECT '64','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '65','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '66','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '67','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '68','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '69','2016-09-21','1','101','102','1','0' UNION ALL
SELECT '70','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '71','2016-09-21','1','101','102','0','0' UNION ALL
SELECT '72','2016-09-21','1','102','101','1','1' UNION ALL
SELECT '73','2016-09-21','1','101','102','0','0' UNION ALL
SELECT '74','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '75','2016-09-21','1','101','102','0','0' UNION ALL
SELECT '76','2016-09-21','1','102','101','1','0' UNION ALL
SELECT '77','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '78','2016-09-21','1','101','102','1','1' UNION ALL
SELECT '79','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '80','2016-09-21','1','101','102','1','1' UNION ALL
SELECT '81','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '82','2016-09-21','1','101','102','1','1' UNION ALL
SELECT '83','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '84','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '85','2016-09-21','1','101','102','1','1' UNION ALL
SELECT '86','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '87','2016-09-21','1','101','102','1','0' UNION ALL
SELECT '88','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '89','2016-09-21','1','102','101','1','0' UNION ALL
SELECT '90','2016-09-21','1','101','102','1','1' UNION ALL
SELECT '91','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '92','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '93','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '94','2016-09-21','1','101','102','1','1' UNION ALL
SELECT '95','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '96','2016-09-21','1','101','102','0','1' UNION ALL
SELECT '97','2016-09-21','1','102','101','0','0' UNION ALL
SELECT '98','2016-09-21','1','101','102','1','0' UNION ALL
SELECT '99','2016-09-21','1','102','101','0','1' UNION ALL
SELECT '100','2016-09-21','1','101','102','0','0'
June 10, 2022 at 12:57 am
Also, I tried using a CROSS APPLY and keep getting the error message: Aggregates on the right side of an APPLY cannot reference columns from the left side. Even using something really basic where I was just trying to get number of serve receive opportunities like the following:
Select TeamID, Serve Receives
FROM tbl_data d
CROSS APPLY (Select d.TeamId as TeamID, Sum(CASE when d.isserve = 1 and d.CtTeamWinRally = 0 then 1 else 0 end) as SO)
Finally, because there are multiple matches to get the SO% you need to:
Wum when the oppId is the team, they are receiving and isServe = 1 and win the rally, CtTeamWinRally = 0. This is the calculation for winning when they receive. Then you need to divide that number by: every time oppId is the team and isServe = 1 which is the number of serve receives. I'm thinking this would be a Union All statement within the cross apply.
So the first section of the cross apply could be point score% which is just:
TeamId = TeamId, IsServe=1 and CtTeamWinRally = 1 / TeamId=TeamId, IsServe=1
then Union All
OppId=TeamId, IsServe =1 and CtTeamWinRally =0/ OppId=TeamId,IsServe=1
I hope this makes sense..
Thank you for helping!
June 10, 2022 at 12:11 pm
Working purely with the data that you provided, and getting totals by Match_Id, TeamId, isServe, and CtTeamWinRally you can quickly calculate what the expected values are
SELECT td.Match_Id, td.TeamId, td.isServe
, td.CtTeamWinRally, Records = COUNT(*)
FROM #tbl_Data AS td
GROUP BY td.Match_Id, td.TeamId, td.isServe, td.CtTeamWinRally
ORDER BY td.Match_Id, td.TeamId, td.isServe, td.CtTeamWinRally
Match_Id TeamId isServe CtTeamWinRally Records
-------------------- ----------- ------- -------------- -----------
1 101 0 0 13
1 101 0 1 19
1 101 1 0 11
1 101 1 1 9
1 102 0 0 20
1 102 0 1 14
1 102 1 0 10
1 102 1 1 4
Team 101
Win 19/32 received = 59.38%
Win 9/20 own serve = 45.00%
Team 102
Win 14/34 received = 41.18%
Win 4/14 own serve = 28.57%
Now, use the following SQL to get the totals and percentages
SELECT td.Match_Id, td.TeamId, td.isServe
, WinServe = SUM(CASE WHEN td.CtTeamWinRally = 1 THEN 1 ELSE 0 END)
, WinServe_Perc = CAST(100.0 * SUM(CASE WHEN td.CtTeamWinRally = 1 THEN 1 ELSE 0 END) / COUNT(*) AS decimal(18,2))
, WinReceive = SUM(CASE WHEN td.CtTeamWinRally = 0 THEN 1 ELSE 0 END)
, WinReceive_Perc = CAST(100.0 * SUM(CASE WHEN td.CtTeamWinRally = 0 THEN 1 ELSE 0 END) / COUNT(*) AS decimal(18,2))
FROM #tbl_Data AS td
GROUP BY td.Match_Id, td.TeamId, td.isServe
ORDER BY td.Match_Id, td.TeamId, td.isServe
Match_Id TeamId isServe WinServe WinServe_Perc WinReceive WinReceive_Perc
---------- ------- ------- ----------- -------------- ----------- ----------------
1 101 0 19 59.38 13 40.63
1 101 1 9 45.00 11 55.00
1 102 0 14 41.18 20 58.82
1 102 1 4 28.57 10 71.43
June 11, 2022 at 4:47 am
Thank you for your help. Not exactly what I was looking for but lead me to my solution. I appreciate the help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply