July 22, 2022 at 7:21 pm
I have a database with sports data, the raw data I received is poorly designed, but after getting an acceptable structure into sql database tables, I now want to return some meaningfull result sets.
The table I'm working on (no primary key as of yet, but may not be in question for this task I'm attempting) :
Create table FootballLog
(
[Gamedate] [date] not null,
[Rotation] [int] not null,
[Locale] [char](2) not null
[Team] [varchar](50) not null,
[Odds] [decimal](18,1) not null,
[Score] [tinyint]not null
)
So the visiting team is designated in the Locale column , with the value of V for visiting, whereas the home team is designated H. The Rotation number for each row is either an odd number for the visiting team, of an even number for home. The data is sequentially ordered, the visiting team comes first, then the home team. The actual matchup for a given Gamedate is sequential in this process; a visitng team is listed first with an odd numbered Rotation number, and the home team for a given game is listed next, with the next rotation number in order, thus even numbered.
I want to grab the Score of the visiting team and insert it into my resultset as Oppscore. And I want to use the Team name in my selective Where clause
My query:
select Gamedate,Rotation,Locale,Team,Score,
case when Locale = 'h' then (lag(Score) over(order by Score)) end as Oppscore
from [dbo].[FootballLog] where Team = 'illinois' order by Gamedate
.. when I get around to querying a team that is going to be essentially a visiting team, then I will use the LEAD function so I can grab the Score from the very next row.
But I'm not getting the correct data for the visiting team in this context. For example, last year on 10/2/2021 , Illinois defeated Charlotte 24-14, but the Oppscore I received when making this query was 23 . And I can cite other Oppscore results in the resultset that are off (some have NULLs). The query runs , doesn't error, but getting wrong erroneous result.
Thanks in advance, can clarify more upon curious inquiries
Zo
July 22, 2022 at 8:56 pm
case when Locale = 'h' then (lag(Score) over(order by Score)) end as Oppscore
CASE WHEN Locale = 'h' THEN ( LAG(score,1) OVER ( ORDER BY Score ) ) END AS OppScore
?
If you need to group the items, then you need
...OVER ( PARTITION BY <grouping column> OVER ( ORDER BY Score ) ) END AS OppScore
July 22, 2022 at 9:50 pm
If your query contains Team = 'Illinois', those are the only rows that will be evaluated.
Lag(Score,1) over ORDER By Score) will give you the next lowest score, regardless of team or date. This is why you had a score of 24 and an OppScore of 23, it was the next lowest score achieved by Illinois.
I have created some test data based on the assumption that the Rotation number is unique for each Gamedate, If Rotation is unique for all dates then the "partition by" is not necessary, but it might be helpful anyway (not sure about that). To view the results for 'Illinois' it is necessary to query all the data and use the lag function to assign an opposing team to a home team row, then filter that set for rows where the home team or the opposing team is Illinois. Based on this sample data, does the query return the right results?
drop table if exists AmericanFootballLog
Create table AmericanFootballLog
(
[Gamedate] [date] not null,
[Rotation] [int] not null,
[Locale] [char](2) not null,
[Team] [varchar](50) not null,
[Odds] [decimal](18,1) not null,
[Score] [tinyint]not null
)
delete AmericanFootballLog
insert AmericanFootballLog VALUES
('2021-10-02', 1, 'V', 'Denver', 0.1, 26),
('2021-10-02', 2, 'H', 'Kansas', 0.1, 30),
('2021-10-02', 3, 'V', 'Illinois', 0.1, 24),
('2021-10-02', 4, 'H', 'Charlotte', 0.1, 14),
('2021-09-09', 1, 'V', 'Charlotte', 0.1, 24),
('2021-09-09', 2, 'H', 'Kansas', 0.1, 14),
('2021-09-09', 3, 'V', 'Illinois', 0.1, 21),
('2021-09-09', 4, 'H', 'Denver', 0.1, 26),
('2021-07-09', 1, 'V', 'Charlotte', 0.1, 86),
('2021-07-09', 2, 'H', 'Illinois', 0.1, 0),
('2021-07-09', 3, 'V', 'Birmingham', 0.1, 2),
('2021-07-09', 4, 'H', 'London', 0.1, 1)
select *
from (
select Gamedate,Rotation,Locale,Team,Score,
lag(Score, 1) over(partition by Gamedate order by [Rotation] ) as Oppscore,
lag(Team, 1) over(partition by Gamedate order by [Rotation] ) as OppTeam
from [dbo].AmericanFootballLog
) as a
WHERE Locale = 'h'
AND (team = 'illinois' OR OppTeam = 'Illinois')
July 25, 2022 at 6:45 pm
HI, thanks for the answers and the time presenting your insights. I've tried using the arg value available to LAG function lag(score,1) in various combinations with and without Case When, but still get the same result for Oppscore . I'll be addressing this more this evening and thru tomorrow, and could come back with more questions.
Thanks
Zo
July 25, 2022 at 7:10 pm
I'm very confused about what final results you want to see. Here's a query that returns all columns; adjust it to return only the columns you want.
SELECT *
FROM dbo.FootballLog f1
CROSS APPLY (
SELECT TOP (1) *
FROM dbo.FootballLog f2
WHERE f2.Gamedate = f1.Gamedate AND
f2.Rotation = CASE WHEN f1.Locale = 'V' THEN f1.Rotation + 1 ELSE f1.Rotation - 1 END
) AS f2
WHERE f1.Team = 'Illinois'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 26, 2022 at 10:32 pm
Thanks, this is the right track: it renders both Illinois (the queried team parameter) and its opponent for that date in one record. I'll play around with this a bit and may come back for any more insight that i may need
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply