Year to year comparison - 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

Analysis Server and Reporting Services (2005)

Year to year comparison

Author

Topic

jhaney
Starting Member

33 Posts

Posted - 2010-10-12 : 15:39:38

I have the following query and it works great to give me my counts for a particualr month/year. I need to be able to display the prev year and the current year for comparison. Going to also try and add a percentage of change for year to year. Any idea on how to add the previous year.

Aug2009 Aug2010 % of change
Doctor1 25 50 %100
Doctor2 10 8 -%20

SELECT CLREFER.NAME, MWBOOK.FACILITY, CLFSCHED.TOS2, CLFSCHED.CPT, MWAPPTS.ADATE
FROM MWAPPTS INNER JOIN
MWBOOK ON MWAPPTS.COMPANY = MWBOOK.COMPANY AND MWAPPTS.BOOK = MWBOOK.BOOKCODE INNER JOIN
CLREFER ON MWAPPTS.COMPANY = CLREFER.COMPANY AND MWAPPTS.REFERRAL = CLREFER.CODE INNER JOIN
MWFSCHED ON MWAPPTS.COMPANY = MWFSCHED.COMPANY AND MWAPPTS.REASON = MWFSCHED.REASON AND
MWAPPTS.DEPARTMENT = MWFSCHED.DEPARTMENT INNER JOIN
CLFSCHED ON MWAPPTS.COMPANY = CLFSCHED.COMPANY AND MWFSCHED.CPT = CLFSCHED.CPT
WHERE (MWAPPTS.COMPANY = 'company') AND (MWAPPTS.ADATE BETWEEN @start1 AND @end1) AND (MWAPPTS.USERFLAG = 'b') AND
(MWAPPTS.DEPARTMENT NOT LIKE '%dept1%') AND (MWAPPTS.DEPARTMENT NOT LIKE '%gay%') AND
(MWAPPTS.DEPARTMENT NOT LIKE '%dept2%') AND (CLFSCHED.PTYPE = 'standard') AND (CLFSCHED.TOS4 = 'exam') AND
(MWBOOK.FACILITY IN (@facility)) AND (CLFSCHED.TOS2 IN (@modality))

thanks

Joshua

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-12 : 15:41:18

Could you just UNION together another query that specifies the previous year via @start1 and @end1?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog