Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Select All matches that clubs played with...

Author  Topic 

rDrigu
Starting Member

1 Post

Posted - 2014-12-03 : 19:35:46
Select All matches that clubs played with an interval less than three days between games

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.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-12-04 : 15:29:25
Untested... (But without schema and sample data how could I test it?)
;with OrderedTeamMatches
as (
select
c.id_club,
c.name,
m.dateMatch,
row_number() over(partition by c.id_club order by m.dateMatch) ordering
from
@Club c
inner join
@Match m
on c.id_club in (m.id_Club1, m.id_club2)
)
select
otm.id_club,
otm.name
from
OrderedTeamMatches otm
inner join
OrderedTeamMatches otm2
on otm.id_club = otm2.id_club
and otm.ordering = otm2.ordering - 1
where
datediff(day, otm.dateMatch, otm2.dateMatch) <= 3




No amount of belief makes something a fact. -James Randi
Go to Top of Page
   

- Advertisement -