Please help.
I created a dynamic pivot which returns a desired set of results. However, I need to insert a 'group by' column called [Positions Available] just before the pivot column. I can get the [Positions Available] results separately but I'm stuck even trying to append the [Positions Available] column. Should I use a temptable?
The desired output being:
Region--Division--Reference--Positions Available--New
SELECT *
FROM (
SELECT
RC.Region,
RC.Division,
J.Reference,
LEFT(ISNULL(NULLIF(ST.Name,''),'Missing Column Name'),50) AS StatusName
FROM
JobApps JA
INNER JOIN Jobs J on J.JobId = JA.JobId
INNER JOIN Recruiters RC on RC.RecruiterId = J.RecruiterId
INNER JOIN [Status] ST on ST.StatusId = JA.StatusId
WHERE
J.ClientId = '1'
AND J.LiveDate BETWEEN 'Nov 1 2010 12:00AM' AND 'Feb 1 2018 12:00AM'
) T
PIVOT
(
COUNT(StatusName)
FOR StatusName IN ([New])
) P
> LEFT OUTER JOIN
> (
> SELECT
> count(jobid) AS [Positions Available]
> FROM
> Jobs J
> INNER JOIN
> Recruiters RC on RC.RecruiterId = J.RecruiterId
> GROUP BY
> rc.region, rc.division, j.reference
> )