Pivot Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivot Function

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-11-27 : 09:58:23

I am trying to replicate this Access Query below into SQL Server.
My problem is that only the total column shows the same values as in the Access Query. How can I incorporate the Actual_Attendees as I do in Access?

Access Code
TRANSFORM Sum(dbo_REP_NON_PATIENT_EVENT_PERF_MAN.ACTUAL_ATTENDEES) AS SumOfACTUAL_ATTENDEES
SELECT SueProfCarers2.SERVICE
FROM dbo_REP_NON_PATIENT_EVENT_PERF_MAN LEFT JOIN SueProfCarers2 ON dbo_REP_NON_PATIENT_EVENT_PERF_MAN.PROCA_REFNO=SueProfCarers2.PROCA_REFNO
WHERE (((dbo_REP_NON_PATIENT_EVENT_PERF_MAN.START_DTTM) Between #4/1/2008# And #9/30/2008 23:59:59#)
And ((dbo_REP_NON_PATIENT_EVENT_PERF_MAN.MAIN_CODE)
In ("ANT","CHILD","DENT","GRPSS","HED","HPROM","PATNT","WELL")))
GROUP BY SueProfCarers2.SERVICE
PIVOT Mid(dbo_REP_NON_PATIENT_EVENT_PERF_MAN.START_DTTM,4,2);

SQL Code
SELECT PARA_SUEPROFCARERS2.Service,
SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 1 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 2 THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 3 THEN 1 ELSE 0 END) AS March,
SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 4 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 5 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 6 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 7 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 8 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 9 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 10 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 11 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 12 THEN 1 ELSE 0 END) AS December,
SUM(REP_NON_PATIENT_EVENT_PERF_MAN_DATES.ACTUAL_ATTENDEES) AS 'Total'
FROM REP_NON_PATIENT_EVENT_PERF_MAN_DATES LEFT JOIN PARA_SUEPROFCARERS2
ON REP_NON_PATIENT_EVENT_PERF_MAN_DATES.PROCA_REFNO=PARA_SUEPROFCARERS2.PROCA_REFNO
WHERE (((REP_NON_PATIENT_EVENT_PERF_MAN_DATES.START_DTTM) BETWEEN '01/04/2008 00:00:00' AND '30/09/2008 23:59:59')
AND ((REP_NON_PATIENT_EVENT_PERF_MAN_DATES.MAIN_CODE)
IN ('ANT', 'CHILD', 'DENT', 'GRPSS', 'HED', 'HPROM', 'PATNT', 'WELL')))
GROUP BY PARA_SUEPROFCARERS2.Service

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-27 : 10:02:53

THEN 1 ELSE 0

should be

THEN dbo_REP_NON_PATIENT_EVENT_PERF_MAN.ACTUAL_ATTENDEES ELSE 0

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-11-27 : 10:11:17

Excellent Thanks for that :-) it works great...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources