<div>
I actually didn't need the MAX. &nbsp;I only put it in because I did not want to group by the date. &nbsp;But I took it out and it works GREAT! &nbsp;Thank you so much!
</div>


I actually didn't need the MAX.  I only put it in because I did not want to group by the date.  But I took it out and it works GREAT!  Thank you so much!

<div>
<div class="content wysiwyg-content">
The following code is giving me the results I want except i have the date hard coded in the WHERE clause because the result of my CASE statement doesn't work. &nbsp;How do I substitute '4/2/12' with EndDate so it's not a static value?<br />
<br />
SELECT &nbsp; &nbsp; &nbsp; &nbsp;COUNT(DeptID) AS Count, DeptID, CASE WHEN MAX(vw_HrIndividuals.LastT<wbr />ermDate) IS NULL THEN GetDate() END AS EndDate<br />
FROM &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;vw_HrIndividuals<br />
WHERE &nbsp; &nbsp; &nbsp; &nbsp;(@Date BETWEEN CONVERT(datetime, CONVERT(char(8), LastStartDate, 112)) AND '4/2/12')<br />
GROUP BY DeptID<br />
ORDER BY DeptID
</div>
</div>


The following code is giving me the results I want except i have the date hard coded in the WHERE clause because the result of my CASE statement doesn't work.  How do I substitute '4/2/12' with EndDate so it's not a static value?

SELECT        COUNT(DeptID) AS Count, DeptID, CASE WHEN MAX(vw_HrIndividuals.LastTermDate) IS NULL THEN GetDate() END AS EndDate
FROM            vw_HrIndividuals
WHERE        (@Date BETWEEN CONVERT(datetime, CONVERT(char(8), LastStartDate, 112)) AND '4/2/12')
GROUP BY DeptID
ORDER BY DeptID

Use CASE statement results in WHERE

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.