Hi
I am Working on a GridView which Displays the values from the below query,and I need to get the Total of each row at its footer
something like this:
R1 A B
R2 5 6
R3 1 11
tot 6 17
What I have tried:
;with t1 as (
SELECT DeptID, COUNT(EmpID) AS TotalHeadCount FROM EmployeeDetails
WHERE (datepart(yyyy,DOJ) between 2005 and @years) and Status =0
group by DeptID
),
t2 as (
SELECT DeptID,COUNT(EmpID) AS NewJoinees FROM EmployeeDetails AS EmployeeDetails_7
WHERE (DATEPART(yyyy, DOJ) = @years) AND (DATEPART(mm, DOJ) = @months) and datepart(mm,DOJ)>= @months and Status = 0
group by DeptID
),
t3 as (
SELECT DeptID,COUNT(EmpID) AS Resigned FROM EmployeeDetails AS EmployeeDetails_7
WHERE (DATEPART(yyyy, deactivate) = @years) AND (DATEPART(mm, deactivate) = @months) and datepart(mm,deactivate)>= @months and Status =1 group by DeptID
),
t4 as (
Select Distinct DeptID,Tobehired As ToBeHired,Openposition As OpenPositions,Status As Status1 From RecruitmentDetails1
WHERE Tobehired=45
)
Select t1.DeptID, CASE WHEN TotalHeadCount IS NULL THEN '0' ELSE TotalHeadCount END AS TotalHeadCount,CASE WHEN NewJoinees IS NULL THEN '0' ELSE NewJoinees END AS NewJoinees,CASE WHEN Resigned IS NULL THEN '0' ELSE Resigned END AS Resigned, t4.Tobehired as ToBeHired, t4.OpenPositions as OpenPositions, t4.Status1 as Status1
from t1 full outer join t2 on t1.DeptID = t2.DeptID
full outer join t3 on t1.DeptID = t3.DeptID
full outer join t4 on t1.DeptID = t4.DeptID