SELECT (c.FirstName+' '+c.LastName) AS Name,
(SELECT TOP 1 (ms.FromTime+'-'+ms.ToTime) FROM Schedule AS ms INNER JOIN [Status] AS s ON s.StatusId = ms.StatusId WHERE ms.ChildId = c.ChildId AND YEAR(ms.Date) = @Year AND DatePart(ww, ms.Date) = @Week AND DATEPART(dw, ms.Date) = 2) AS Monday
FROM Child AS c
När Monday är null så vill jag istället för att returnera null returnera s.Name, skulle även s.Name vara null ska saknas returneras i Monday.
(SELECT TOP 1 coalesce(ms.FromTime+'-'+ms.ToTime,coalesce(s.Name,'saknas')) FROM Schedule AS ms INNER JOIN [Status] AS s ON s.StatusId = ms.StatusId WHERE ms.ChildId = c.ChildId AND YEAR(ms.Date) = @Year AND DatePart(ww, ms.Date) = @Week AND DATEPART(dw, ms.Date) = 2) AS Monday
(SELECT TOP 1 case when ms.FromTime is null or ms.ToTime is null then coalesce(s.Name, 'saknas') else ms.FromTime+'-'+ms.ToTime end FROM Schedule AS ms INNER JOIN [Status] AS s ON s.StatusId = ms.StatusId WHERE ms.ChildId = c.ChildId AND YEAR(ms.Date) = @Year AND DatePart(ww, ms.Date) = @Week AND DATEPART(dw, ms.Date) = 2) AS Monday
