SELECT e.EventId, e.NeedEntries, e.Header, e.Message, CONVERT(VARCHAR(11), e.Date, 113) AS Date,
(SELECT COUNT(ChildId) FROM ChildToEvent WHERE ChildId = @ChildId AND EventId = e.EventId AND Status = 1)
as Status,
CASE
WHEN e.Date < getdate() then 'past'
WHEN e.Date = getdate() then 'today'
ELSE 'coming' END AS DateStatus,
d.Name AS DepartmentName,
ISNULL((SELECT Message FROM ChildToEvent WHERE ChildId = @ChildId AND EventId = e.EventId), '') AS ChildMessage
FROM Event AS e
INNER JOIN EventToDepartment AS etd
ON e.EventId = etd.EventId
INNER JOIN Department AS d
ON etd.DepartmentId = d.DepartmentId
WHERE (d.PreschoolId = @SchoolId OR @SchoolId = 0)
AND (etd.DepartmentId = @DepartmentId OR @DepartmentId = 0)
AND (DatePart("ww", e.Date) = @Week OR @Week = 0)
AND (YEAR(e.Date) = @Year OR @Year = 0)
GROUP BY e.Header, e.Message, e.Date, e.EventId, e.NeedEntries, d.Name
ORDER BY e.Date DESC
Problemet är att jag får ut tre likadana event, jag vill bara ha ut samma event endast en gång. Antar att det är:
INNER JOIN EventToDepartment AS etd
ON e.EventId = etd.EventId
mjo, men problemet ligger här då:
AND (etd.DepartmentId = @DepartmentId OR @DepartmentId = 0)
När @DepartmentId = 0 då vill jag ju få ut eventet en gång, skickar jag in 0 nu så blir det fel. Borde väll finnas någon lösning för detta?
