1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| SELECT
Thematique.Title AS Thematique,
Workgroup.Title AS [Groupes de travail],
Visite.Visitor_Id,
DATENAME(yyyy, Visite.DateTime) + '-' + CAST(MONTH(Visite.DateTime) AS nvarchar) AS logDate,
COUNT(Visite.DateTime) AS Count
FROM Visite
INNER JOIN Workgroup ON Visite.Workgroup_Id = Workgroup.Id
INNER JOIN Workgroup_Themes_WorkgroupTheme ON Workgroup.Id = Workgroup_Themes_WorkgroupTheme.Id
INNER JOIN WorkgroupTheme ON Workgroup_Themes_WorkgroupTheme.Id2 = WorkgroupTheme.Id
INNER JOIN Thematique ON Thematique.Id = WorkgroupTheme.Thematique_Id
INNER JOIN [User] u ON u.Id = Visite.Visitor_Id
WHERE u.DisplayName <> 'sys_moss'
GROUP BY Thematique.Title, Workgroup.Title, Visite.Visitor_Id, DATENAME(yyyy, Visite.DateTime), MONTH(Visite.DateTime)
ORDER BY Thematique, [Groupes de travail], Visite.Visitor_Id, DATENAME(yyyy, Visite.DateTime) DESC, MONTH(Visite.DateTime) DESC |