views:

283

answers:

3

Hi all,

I have the following query, which uses a CASE statement. Is there anyway to add into the where clause WHERE IsBusinessDayFinal = 0? without using a temporary table?

thanks so much!

SELECT 
     ac.DateTimeValue,
     CASE 
      WHEN pc.IsBusinessDay IS NOT NULL THEN pc.IsBusinessDay
      ELSE ac.IsBusinessDay
     END AS IsBusinessDayFinal,
     ac.FullYear,
     ac.MonthValue,
     ac.DayOfMonth,
     ac.DayOfWeek,
     ac.Week 
    FROM 
     [dbo].[AdminCalendar] ac LEFT JOIN
     [dbo].ProjectCalendar pc ON ac.DateTimeValue = pc.DateTimeValue AND pc.ProjectId = @projectId
    WHERE ac.DateTimeValue >= @startDate AND ac.DateTimeValue <= @finishDate;
+2  A: 
WHERE ac.DateTimeValue >= @startDate AND ac.DateTimeValue <= @finishDate
      AND ((pc.IsBusinessDay IS NOT NULL AND pc.IsBusinessDay = 0) OR ac.IsBusinessDay = 0)
Mitch Wheat
+3  A: 

Use

WHERE (pc.IsBusinessDay IS NULL AND ac.IsBusinessDay = 0)
OR pc.IsBusinessDay = 0
Scott Whitlock
Thank you! I didn't realize that the solution was actually really simple. I guess I didn't think hard enough. =p
AbeP
+1. Even though logic is identical, I prefer your IS NULL version!
Mitch Wheat
+2  A: 

You can use COALESCE instead of CASE because you're checking for null:

SELECT ac.DateTimeValue,
       COALESCE(pc.IsBusinessDay, ac.IsBusinessDay) AS IsBusinessDayFinal,
       ac.FullYear,
       ac.MonthValue,
       ac.DayOfMonth,
       ac.DayOfWeek,
       ac.Week 
  FROM [dbo].[AdminCalendar] ac 
LEFT JOIN [dbo].ProjectCalendar pc ON ac.DateTimeValue = pc.DateTimeValue 
                                  AND pc.ProjectId = @projectId
WHERE ac.DateTimeValue >= @startDate 
 AND ac.DateTimeValue <= @finishDate
 AND COALESCE(pc.IsBusinessDay, ac.IsBusinessDay) = 0
OMG Ponies