SELECT TeamTicketLink AS BuyTickets, FixId, FixSource, FixDate, FixTime, FixDays,IF(FixTime IS NOT NULL,CONVERT_TZ(CONCAT(FixDate,' ',FixTime),@@global.time_zone,'+00:00'),NULL) AS GMTFixDateTime, IF(FixTime IS NOT NULL,CONCAT(FixDate,' ',FixTime),NULL) AS FixDateTime, TIME_FORMAT(FixTime,'%H:%i') AS TimeLong, TIME_FORMAT(FixTime,'%l:%i') AS TimeShort,
DATE_FORMAT(FixDate,'%e/%c') AS DateDmShort, DATE_FORMAT(FixDate,'%d/%m') AS DateDmLong, DATE_FORMAT(FixDate,'%e/%c/%y') AS DateDmyShort, DATE_FORMAT(FixDate,'%d/%m/%Y') AS DateDmyLong,
DATE_FORMAT(FixDate,'%a %e %b') AS DateShort, DATE_FORMAT(FixDate,'%a %D %b %y') AS DateAbbrev, DATE_FORMAT(FixDate,'%W %D %M %Y') AS DateLong, DATE_FORMAT(FixDate,'%a') AS DayShort, DATE_FORMAT(FixDate,'%d %b') AS DateDmTxt,
FixTeamA, FixTeamB, CompGroup, CompSubGroup, CompSponsor, CompLive, FixRound AS Round,
FixVenType, IF(VnnmVenueId IS NULL,FixVenCustom,VnnmFullName) AS VenName,
teamsa.TmnmDisplay AS HomeDisplay, teamsb.TmnmDisplay AS AwayDisplay,
teamsa.TmnmShort AS HomeShort, teamsb.TmnmShort AS AwayShort,
FixTryA, FixConvA, FixDropA, FixPenA, FixMarkA, FixTryB, FixConvB, FixDropB, FixPenB, FixMarkB, FixFullPtsA, FixFullPtsB, FixStatus, FixAttend, FixBroadcast AS Broadcast, FixLive,
ProvName, DataTicketInfo AS Ticketing, DataSeasonTicket AS SeasonTicket, DataTicketLink AS TicketLink,
FixRefOffId, FixTjaOffId, FixTjbOffId, FixFourOffId, FixTmoOffId, FixAssrOffId, FixComrOffId, FixRefName, 0+IF(FixSource='hgvision',0,0)+IF(FixSource='sixnations',1,0)+IF(FixSource='irfu18',2,0) AS Ord, DataReport,
GROUP_CONCAT(DISTINCT StgId ORDER BY StgOrd, StgName ASC SEPARATOR ',') AS StageId,
GROUP_CONCAT(DISTINCT StgName ORDER BY StgOrd, StgName ASC SEPARATOR ',') AS StageName,
GROUP_CONCAT(DISTINCT StgAbbrev ORDER BY StgOrd, StgName ASC SEPARATOR ',') AS StageAbbrev,
GROUP_CONCAT(DISTINCT StgAbbrev ORDER BY StgOrd, StgName ASC SEPARATOR ',') AS StageShortName,
GROUP_CONCAT(DISTINCT CompAbbrev ORDER BY CompAbbrev ASC SEPARATOR ',') AS CompAbbrev,
GROUP_CONCAT(DISTINCT CompName ORDER BY CompName ASC SEPARATOR ',') AS CompName
FROM (stats_rugby.fixtures, stats_rugby.fixcompetitions, stats_rugby.competitions, stats_rugby.stages)
LEFT JOIN stats_rugby.teamnames AS teamsa ON teamsa.TmnmTeamId = FixTeamA AND teamsa.TmnmStart <= CompSeason AND (teamsa.TmnmEnd = -1 OR teamsa.TmnmEnd >= CompSeason)
LEFT JOIN stats_rugby.teamnames AS teamsb ON teamsb.TmnmTeamId = FixTeamB AND teamsb.TmnmStart <= CompSeason AND (teamsb.TmnmEnd = -1 OR teamsb.TmnmEnd >= CompSeason)
LEFT JOIN stats_rugby.venues ON VenId = FixVenId AND (VenSource = 'hgvision' OR VenSource = 'sixnations' OR VenSource = 'irfu18')
LEFT JOIN stats_rugby.venuenames ON VnnmVenueId = FixVenId AND VnnmStart <= FixDate AND (venuenames.VnnmEnd = '0000-00-00' OR venuenames.VnnmEnd >= FixDate)
LEFT JOIN stats_rugby.broadcasts ON BcastFixId = FixId
LEFT JOIN stats_rugby.bcastproviders ON BcastProvId = ProvId
LEFT JOIN stats_rugby.fixdata ON DataFixId = FixId AND DataProjId = 'sixnations'
LEFT JOIN stats_rugby.teams ON TeamId = FixTeamA AND (TeamSource = 'hgvision' OR TeamSource = 'sixnations' OR TeamSource = 'irfu18')
WHERE FcmpSource = FixSource AND FcmpFixId = FixId AND FcmpCompId = CompId AND FcmpStgId = StgId AND StgCompId = CompId AND FixDate IS NOT NULL AND (FixSource = 'hgvision' OR FixSource = 'sixnations' OR FixSource = 'irfu18') AND (0 OR CompGroup = 'agegroup') AND CompSubGroup = 'sixnations20' AND (CompVisible = 'Yes' OR CompVisible IS NULL) AND (0 OR CompGroup = 'agegroup') AND CompSubGroup = 'sixnations20' AND (CompVisible = 'Yes' OR CompVisible IS NULL) AND (((CompSeason = '2009' AND CompSeasonEnd = '2010')) OR CompId IS NULL)
GROUP BY FixId, FixSource
ORDER BY FixDate ASC, Ord, FixTime, HomeDisplay, AwayDisplay, Ord
|
| Data | Ora (GMT) | In casa | Punteggio | Fuori casa | Stadio | TV/spett |
|---|
| Ven 5 Feb 10 | 19:30 | Scozia U20 | 8 - 8 | France U20s | Tulloch Caledonian Stadium, Inverness |
|
| Ven 5 Feb 10 | 19:35 | Ireland U20s | 39 - 0 | Italy U20s | Dubarry Park, Athlone |
|
| Ven 5 Feb 10 | 19:45 | Inghilterra U20 | 41 - 14 | Wales U20s | Gloucester RFC |
|
| Ven 12 Feb 10 | 19:00 | Italy U20s | 10 - 16 | Inghilterra U20 | Stadio Comunale, San Doná di Piave, Venice |
|
| Ven 12 Feb 10 | 19:05 | Wales U20s | 20 - 12 | Scozia U20 | Cardiff Arms Park |
|
| Ven 12 Feb 10 | 19:45 | France U20s | 20 - 15 | Ireland U20s | Mazamet (nr Toulouse) |
|
| Ven 26 Feb 10 | 15:30 | Wales U20s | 43 - 8 | France U20s | Cardiff Arms Park |
|
| Sab 27 Feb 10 | 14:00 | Italy U20s | 16 - 18 | Scozia U20 | Stadio Santa Rosa, Capoterra, Cagliari |
|
| Sab 27 Feb 10 | 19:45 | Inghilterra U20 | 10 - 25 | Ireland U20s | Gloucester RFC |
|
| Ven 12 Mar 10 | 19:00 | Scozia U20 | 6 - 27 | Inghilterra U20 | Firhill Stadium, Glasgow |
|
| Ven 12 Mar 10 | 19:35 | Ireland U20s | 24 - 17 | Wales U20s | Dubarry Park, Athlone |
|
| Ven 12 Mar 10 | 19:45 | France U20s | 25 - 6 | Italy U20s | Grenoble (nr Lyon) |
|
| Ven 19 Mar 10 | 19:05 | Wales U20s | v | Italy U20s | Cardiff Arms Park |
|
| Ven 19 Mar 10 | 19:35 | Ireland U20s | v | Scozia U20 | Dubarry Park, Athlone |
|
| Dom 21 Mar 10 | 14:00 | France U20s | v | Inghilterra U20 | Saint Nazaire (nr Nantes) |
|
|
|
