May 24, 2006 at 7:52 am
Looking to get my result in 1 record, but whether I use union or union all, I get three records....WHY?!?!?! Thanks in advance!
SELECT TOP 100 PERCENT 'RVUPTConsecutive' AS RVUPT, r1.Campus, SUM(r1.RVUs) / SUM(r1.Visits) AS RVUPerVisit_current
FROM PCS_DERD.[Rollup - Productivity] r1 INNER JOIN
PCS_DERD.[Rollup - Productivity] r2 ON r1.Campus = r2.Campus
WHERE (SUBSTRING(r1.YYYYMM, 5, 2) + '/1/' + SUBSTRING(r1.YYYYMM, 1, 4) = DATEADD(r1.[Month], - 0, '4/1/2006'))
GROUP BY r1.Campus
UNION ALL
SELECT TOP 100 PERCENT 'RVUPTConsecutive' AS RVUPT, r1.Campus, SUM(r1.RVUs) / SUM(r1.Visits) AS RVUPerVisit_lastmonth
FROM PCS_DERD.[Rollup - Productivity] r1 INNER JOIN
PCS_DERD.[Rollup - Productivity] r2 ON r1.Campus = r2.Campus
WHERE (SUBSTRING(r1.YYYYMM, 5, 2) + '/1/' + SUBSTRING(r1.YYYYMM, 1, 4) = DATEADD(r1.[Month], - 1, '4/1/2006'))
GROUP BY r1.Campus
UNION ALL
SELECT TOP 100 PERCENT 'RVUPTConsecutive' AS RVUPT, r1.Campus, SUM(r1.RVUs) / SUM(r1.Visits) AS RVUPerVisit_2Month
FROM PCS_DERD.[Rollup - Productivity] r1 INNER JOIN
PCS_DERD.[Rollup - Productivity] r2 ON r1.Campus = r2.Campus
WHERE (SUBSTRING(r1.YYYYMM, 5, 2) + '/1/' + SUBSTRING(r1.YYYYMM, 1, 4) = DATEADD(r1.[Month], - 2, '4/1/2006'))
GROUP BY r1.Campus
May 24, 2006 at 9:09 am
xposted
May 24, 2006 at 11:08 am
Sorry about the cross post...didn't know i did it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply