May 24, 2006 at 7:53 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 8:20 am
union will distinct records. If one part of your union returns different values you will get extra row(s). Are you trying to group accoss the whole record set? In which case you need remove sum and group by from the three queries union them together in sub select first and then sum and group over that unified record set.
May 24, 2006 at 8:31 am
Er, because you're asking for three records?
Seriously, I think you're misunderstanding the nature of UNION. UNION is supposed to take multiple datasets with identical structures, and append them vertically.
What I think you're looking for is more like a cross-tab. Like so:
RVUPT . . . . . . Campus . . . . . Current . . . LastMonth . . . TwoMonth
RVUPTConsecutive . North Campus . . .25 . . . . . .35 . . . . . . . .77
Which would be better accomplished, in your case, with a join.
SELECT 'RVUPTConsecutive' AS RVUPT, r1.Campus, r1.RVUPerVisit_current, r2.RVUPerVisit_lastmonth, r3.RVUPerVisit_2Month
FROM (SELECT Campus, SUM(RVUs)/SUM(Visits) AS RVUPerVisit_current
FROM PCS_DERD.[Rollup - Productivity]
WHERE (SUBSTRING(YYYYMM, 5, 2) + '/01/' + SUBSTRING(YYYYMM, 1, 4) =
DATEADD([Month], - 0, '4/1/2006'))
GROUP BY Campus) r1
JOIN (SELECT Campus, SUM(RVUs)/SUM(Visits) AS RVUPerVisit_lastmonth
FROM PCS_DERD.[Rollup - Productivity]
WHERE (SUBSTRING(YYYYMM, 5, 2) + '/01/' + SUBSTRING(YYYYMM, 1, 4) =
DATEADD([Month], - 1, '4/1/2006'))
GROUP BY Campus) r2
ON r1.Campus = r2.Campus
JOIN (SELECT Campus, SUM(RVUs)/SUM(Visits) AS RVUPerVisit_2Month
FROM PCS_DERD.[Rollup - Productivity]
WHERE (SUBSTRING(YYYYMM, 5, 2) + '/01/' + SUBSTRING(YYYYMM, 1, 4) =
DATEADD([Month], - 2, '4/1/2006'))
GROUP BY Campus) r3
ON r1.Campus = r3.Campus
{As a note, I couldn't figure out the point of the INNER JOIN in each of your original queries. You did a self-join, on the same field, and never used the second table. So, I dropped that from my query.}
May 24, 2006 at 8:47 am
Marshall, you are absolutely correct. I wasn't thinking about the fact that the different values would do it! Your solution is perfect! Thanks!!
May 24, 2006 at 8:53 am
Glad I could help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply