T-SQL and SSRS Grouping and Count

I have to take data from 2 tables and output the data in the form of a Pivot. the data has 3 types of head counts, Actual, Plan and Pending and the headcount number is pivoted with the months. Now I'm able to get a partial solution. The following is my code to get data for Actual headcount.

SELECT

' Actual' HC_TYPE, p.[Owner], h1.ProjectID, p.ProjectName, h1.Center,
substring(convert(varchar,h1.Period,120),1,7) Period,
(Case when (substring(h1.ID,1,3) <> 'TBD') then count(*) else '0' end) HC

FROM HC_PLAN_EXPANDED h1, PRO开发者_开发知识库JECTS p 

WHERE p.Status = 'Active' 

AND h1.ProjectID = p.ProjectID          

GROUP BY  p.Owner, h1.ProjectID, h1.Center, p.ProjectName,
substring(convert(varchar,h1.Period,120),1,7), h1.ID

I need to not use group by h1.ID but be able to use h1.ID in the case condition.

Perhaps this will work for you:

SELECT

' Actual' HC_TYPE, p.[Owner], h1.ProjectID, p.ProjectName, h1.Center,
substring(convert(varchar,h1.Period,120),1,7) Period,
(Select Case when (substring(h1.ID,1,3) <> 'TBD') then count(*) else '0' end from h1) HC

FROM HC_PLAN_EXPANDED h1, PROJECTS p 

WHERE p.Status = 'Active' 

AND h1.ProjectID = p.ProjectID          

GROUP BY  p.Owner, h1.ProjectID, h1.Center, p.ProjectName,
substring(convert(varchar,h1.Period,120),1,7), h1.ID

Even though you say you have already found your solution, you may still want to have a look at this. Just in CASE. :)

SELECT

' Actual' HC_TYPE, p.[Owner], h1.ProjectID, p.ProjectName, h1.Center,
substring(convert(varchar,h1.Period,120),1,7) Period,

count(Case substring(h1.ID,1,3) when 'TBD' then null else 1 end) HC  /* <-- */
/* alternatively, and closer to your structuring too:
count(Case when substring(h1.ID,1,3) <> 'TBD' then 1 end) HC
*/

FROM HC_PLAN_EXPANDED h1, PROJECTS p 

WHERE p.Status = 'Active' 

AND h1.ProjectID = p.ProjectID          

GROUP BY  p.Owner, h1.ProjectID, h1.Center, p.ProjectName,
substring(convert(varchar,h1.Period,120),1,7)

This is what I did..

SELECT a.ProjectID, Center, ID, Period, [Source], p.ProjectName, p.Owner, COUNT(1) AS HC, CASE WHEN substring(ID,1,3) <> 'TBD' THEN count(1) ELSE 0 END AS HC_TYPEA,

     CASE 
     WHEN substring(ID,1,3) = 'TBD'  THEN COUNT(1)
     ELSE 0
     END AS HC_TYPEp

FROM HC_PLAN_EXPANDED a JOIN PROJECTS p ON a.ProjectID = p.ProjectID WHERE p.Status IN ('ACTIVE') and period between '2011-01-01' and '2011-12-01'

GROUP BY a.ProjectID, Center, ID, Period, [Source], p.ProjectID, p.ProjectName, p.[Owner]

I grouped the 3 types of Headcount on the reports builder.. I know its a lot different from what I actually asked.. but it served my purpose.. thank you all for your support..