PIVOT
DECLARE @startDate AS DATETIME
DECLARE @endDate AS DATETIME
SET @startDate = '2008-6-1'
SET @endDate = '2008-6-30 23:59:59';
SELECT
at.ActivityDesc AS ActivityType,
e.FullName AS [Completed by],
COUNT(a.ActivityId) AS [No of Activities Completed]
FROM
Activity a
INNER JOIN ActivityType at ON at.ActivityTypeId = a.ActivityTypeId
INNER JOIN Employee e ON e.EmployeeId = a.AssignedTo_EmployeeID
WHERE
a.AssignedTo_EmployeeID IN (29,30,698,677,27,31,960,1258)
AND a.ActivityTypeId IN (58,59,60)
AND a.RequiredDate BETWEEN @startDate AND @endDate
GROUP BY
at.ActivityDesc,
e.FullName;
WITH ActivityDetail
AS
(
SELECT
at.ActivityDesc AS ActivityType,
e.FullName AS [Completed by],
COUNT(a.ActivityId) AS [No of Activities Completed]
FROM
Activity a
INNER JOIN ActivityType at ON at.ActivityTypeId = a.ActivityTypeId
INNER JOIN Employee e ON e.EmployeeId = a.AssignedTo_EmployeeID
WHERE
a.AssignedTo_EmployeeID IN (29,30,698,677,27,31,960,1258)
AND a.ActivityTypeId IN (58,59,60)
AND a.RequiredDate BETWEEN @startDate AND @endDate
GROUP BY
at.ActivityDesc,
e.FullName
)
SELECT
pt.[Completed By],
ISNULL(pt.[New Media Services Quotes],0) [New Media Services Quotes],
ISNULL(pt.[New Media Services Schedule],0) [New Media Services Schedule],
ISNULL(pt.[New Media Services (Other)],0) [New Media Services (Other)]
FROM
ActivityDetail ad
PIVOT
(
SUM(ad.[No of Activities Completed] )
FOR ad.ActivityType IN ([New Media Services Quotes], [New Media Services Schedule], [New Media Services (Other)])
) AS pt
DECLARE @endDate AS DATETIME
SET @startDate = '2008-6-1'
SET @endDate = '2008-6-30 23:59:59';
SELECT
at.ActivityDesc AS ActivityType,
e.FullName AS [Completed by],
COUNT(a.ActivityId) AS [No of Activities Completed]
FROM
Activity a
INNER JOIN ActivityType at ON at.ActivityTypeId = a.ActivityTypeId
INNER JOIN Employee e ON e.EmployeeId = a.AssignedTo_EmployeeID
WHERE
a.AssignedTo_EmployeeID IN (29,30,698,677,27,31,960,1258)
AND a.ActivityTypeId IN (58,59,60)
AND a.RequiredDate BETWEEN @startDate AND @endDate
GROUP BY
at.ActivityDesc,
e.FullName;
WITH ActivityDetail
AS
(
SELECT
at.ActivityDesc AS ActivityType,
e.FullName AS [Completed by],
COUNT(a.ActivityId) AS [No of Activities Completed]
FROM
Activity a
INNER JOIN ActivityType at ON at.ActivityTypeId = a.ActivityTypeId
INNER JOIN Employee e ON e.EmployeeId = a.AssignedTo_EmployeeID
WHERE
a.AssignedTo_EmployeeID IN (29,30,698,677,27,31,960,1258)
AND a.ActivityTypeId IN (58,59,60)
AND a.RequiredDate BETWEEN @startDate AND @endDate
GROUP BY
at.ActivityDesc,
e.FullName
)
SELECT
pt.[Completed By],
ISNULL(pt.[New Media Services Quotes],0) [New Media Services Quotes],
ISNULL(pt.[New Media Services Schedule],0) [New Media Services Schedule],
ISNULL(pt.[New Media Services (Other)],0) [New Media Services (Other)]
FROM
ActivityDetail ad
PIVOT
(
SUM(ad.[No of Activities Completed] )
FOR ad.ActivityType IN ([New Media Services Quotes], [New Media Services Schedule], [New Media Services (Other)])
) AS pt