December 3, 2014 at 5:39 pm
Hi guys!
I am trying to solve this select statement, but I can't do it. :/
-----
I have two tables:
Club - Stores all clubs (id_club, name)
Match - Store all matches (id_club1, id_club2, dateMatch, result)
The match has club 1 and club 2.
And I Have to select all matches that clubs played with an interval less than three days between games.
This is my code:
SELECT DISTINCT a.*
FROM
Matches a
INNER JOIN
Matches b ON
(
a.id_club1 IN (b.id_club1, b.id_club2) OR
a.id_club2 IN (b.id_club1, b.id_club2)
) AND
ABS(DATEDIFF(DAY, a.DateMatch, b.DateMatch)) <= 3
I tried too:
select *, DATEDIFF(day, m1.date, j2.data) from matches m1, matches m2
where abs(DATEDIFF(day, m1.dateMatch, m2.dateMatch))<3
and (m1.id_club1=m2.id_club2)
but it doesn't working.
because I have two clubs in a row. It's so difficult :/
Anyone here can help me?
sorry for bady english.
December 3, 2014 at 8:25 pm
I'm pretty sure Eirikur will shred my SQL, but this works:
SELECT MatchID
, MatchDate
, TeamID
, PrevMatch
, DaysElapsed
FROM (
-- get the difference in days between matches
SELECT MatchID
, MatchDate
, TeamID
, LAG(MatchDate,1) OVER (PARTITION BY TeamID ORDER BY MatchDate) AS PrevMatch
, DATEDIFF(day,LAG(MatchDate,1) OVER (PARTITION BY TeamID ORDER BY MatchDate),MatchDate) AS DaysElapsed
FROM
-- split the teams apart so they have have a record for a game in the same column
(SELECT MatchID, MatchDate, HomeTeamID AS TeamID
FROM Match
UNION ALL
SELECT MatchID, MatchDate, AwayTeamID
FROM Match) x ) y
WHERE y.DaysElapsed>=3
ORDER BY TeamID
December 4, 2014 at 10:14 am
SELECT DISTINCT m1.*
FROM Matches m1
WHERE
EXISTS(
SELECT 1
FROM Matches m2
WHERE
(m1.id_club1 IN (m2.id_club1, m2.id_club2) OR
m1.id_club2 IN (m2.id_club1, m2.id_club2)) AND
m2.DateMatch <> m1.DateMatch AND --make sure match doesn't EXISTS to itself
m2.DateMatch BETWEEN DATEADD(DAY, -2, m1.DateMatch) AND DATEADD(DAY, +2, m2.DateMatch)
)
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".
December 4, 2014 at 2:48 pm
pietlinden (12/3/2014)
I'm pretty sure Eirikur will shred my SQL, but this works:
No shredding, just minor improvement
😎
First a data set
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @DUMBER_OF_CLUBS INT = 200;
IF OBJECT_ID('dbo.TBL_CLUB') IS NOT NULL DROP TABLE dbo.TBL_CLUB;
IF OBJECT_ID('dbo.TBL_MATCH') IS NOT NULL DROP TABLE dbo.TBL_MATCH;
CREATE TABLE dbo.TBL_CLUB
(
CLUB_ID INT NOT NULL PRIMARY KEY CLUSTERED
,CLUB_NAME VARCHAR(50) NOT NULL
);
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMCLUBS(N) AS (SELECT TOP(@DUMBER_OF_CLUBS) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9,T T10,T T11)
INSERT INTO dbo.TBL_CLUB(CLUB_ID,CLUB_NAME)
SELECT
NC.N AS CLUB_ID
,CHAR(65 + NC.N % 26) + REPLICATE('0',6 - LEN(NC.N)) + CAST(NC.N AS VARCHAR(7))
FROM NUMCLUBS NC;
CREATE TABLE dbo.TBL_MATCH
(
MATCH_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,HOME INT NOT NULL
,AWAY INT NOT NULL
,MATCHDAY DATE NOT NULL
,RESULT CHAR(3) NOT NULL
);
INSERT INTO dbo.TBL_MATCH(HOME,AWAY,MATCHDAY,RESULT)
SELECT
CA.CLUB_ID AS HOME
,CB.CLUB_ID AS AWAY
,DATEADD(DAY,CHECKSUM(NEWID()) % 1200,CONVERT(DATE,'2014-07-01',0))
,CONVERT(CHAR(1),ABS(CHECKSUM(NEWID())) % 5) + '-' + CONVERT(CHAR(1),ABS(CHECKSUM(NEWID())) % 5)
FROM dbo.TBL_CLUB CA
CROSS JOIN dbo.TBL_CLUB CB
WHERE CA.CLUB_ID <> CB.CLUB_ID;
and then the test harness
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TIMER TABLE (TSTAMP DATETIME NOT NULL DEFAULT (GETDATE()), METHOD_NAME VARCHAR(20) NOT NULL);
INSERT INTO @TIMER (METHOD_NAME) VALUES ('ScottPletcher')
SELECT DISTINCT m1.*
FROM dbo.TBL_MATCH m1
WHERE
EXISTS(
SELECT 1
FROM dbo.TBL_MATCH m2
WHERE
(m1.HOME IN (m2.HOME, m2.AWAY) OR
m1.AWAY IN (m2.HOME, m2.AWAY)) AND
m2.MATCHDAY <> m1.MATCHDAY AND --make sure match doesn't EXISTS to itself
m2.MATCHDAY BETWEEN DATEADD(DAY, -2, m1.MATCHDAY) AND DATEADD(DAY, +2, m2.MATCHDAY)
)
INSERT INTO @TIMER (METHOD_NAME) VALUES ('ScottPletcher')
INSERT INTO @TIMER (METHOD_NAME) VALUES ('pietlinden')
SELECT MATCH_ID
, MATCHDAY
, TeamID
, PrevMatch
, DaysElapsed
FROM (
-- get the difference in days between matches
SELECT MATCH_ID
, MATCHDAY
, TeamID
, LAG(MATCHDAY,1) OVER (PARTITION BY TeamID ORDER BY MATCHDAY) AS PrevMatch
, DATEDIFF(day,LAG(MATCHDAY,1) OVER (PARTITION BY TeamID ORDER BY MATCHDAY),MATCHDAY) AS DaysElapsed
FROM
-- split the teams apart so they have have a record for a game in the same column
(SELECT MATCH_ID, MATCHDAY, HOME AS TeamID
FROM dbo.TBL_MATCH
UNION ALL
SELECT MATCH_ID, MATCHDAY, AWAY
FROM dbo.TBL_MATCH) x ) y
WHERE y.DaysElapsed<=3;
INSERT INTO @TIMER (METHOD_NAME) VALUES ('pietlinden')
INSERT INTO @TIMER (METHOD_NAME) VALUES ('Eirikur')
;WITH MATCH_INTERVAL AS
(
SELECT
M.MATCH_ID
,T.TEAM
,T.MATCHDAY
,DATEDIFF(DAY,LAG(T.MATCHDAY,1) OVER
(
PARTITION BY T.TEAM
ORDER BY T.MATCHDAY
)
,T.MATCHDAY) AS INTERVAL_DAY
FROM dbo.TBL_MATCH M
CROSS APPLY
(
SELECT MATCH_ID,HOME,MATCHDAY UNION ALL
SELECT MATCH_ID,AWAY,MATCHDAY
) AS T(MATCH_ID,TEAM,MATCHDAY)
)
SELECT
MI.MATCH_ID
,MI.TEAM
,MI.MATCHDAY
,MI.INTERVAL_DAY
FROM MATCH_INTERVAL MI
WHERE MI.INTERVAL_DAY <= 3;
INSERT INTO @TIMER (METHOD_NAME) VALUES ('Eirikur')
SELECT
DATEDIFF(MILLISECOND,MIN(T.TSTAMP),MAX(T.TSTAMP)) AS DURATION
,T.METHOD_NAME
FROM @TIMER T
GROUP BY T.METHOD_NAME
ORDER BY 1;
Results
DURATION METHOD_NAME
-------- ------------
323 Eirikur
453 pietlinden
3230 ScottPletcher
December 6, 2014 at 5:02 am
Thanks a lot guys!
It's helping me a lot.
I got it with another solution too, but your 'selects' helped me to understand.
I am going to post my solution later.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply