visa schema, problem

Har följande:

	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.

Hur löser man detta??

Tacksam för all hjälp :stud

(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

tack, men resultatet blir detsamma. Monday innehåller null :r

Iiih! Inlägget är uppdaterat :)

tack, men det blir fortfarande null :(

Hm, så här då:

(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

coalesce((select ...),'saknas')

Om subselecten inte hittar någon post så returnerar den null

tack, nu fungerar den :D