Solved

Report comparing year over year data

Posted on 2007-11-28
6
206 Views
Last Modified: 2010-03-20
What is the method of creating a query and report (SSRS) that compares sales for the current year versus last year?  Sorry, I know this is pretty fundamental.
0
Comment
Question by:joe10czar
  • 2
  • 2
6 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20370216
You basically create a report as you would any other report. However, for the SQL part, you will need to involve GROUP BY, DATEADD and DATEDIFF

the query below will give you, for each product, the number of items sold this year and the number of items sold last year

select productCode,
  sum(case when year(saledate) = year(getdate()) then 1 else 0 end) as ThisYear,
  sum(case when year(saledate) <> year(getdate()) then 1 else 0 end) as LastYear
from sales
where saledate >= convert(datetime, cast(year(getdate())-1 as varchar) +'-01-01')
group by year(saledate)

you would modify to suit the situation, i.e. price or percentage difference
0
 

Author Comment

by:joe10czar
ID: 20375548
Thanks for the quick reply.  I have expanded your idea to include all of the fields and tables I need.  QUery won't run, becasue of syntax near the WHERE clause.

Basically, the "Week" in the "Z1Attendance" table is related to the "z1Week" table, which holds the weeks "EndDate" column.
SELECT z1Attendance.Attraction, z1Attendance.Attendance, z1Attraction.z1AttractionCaption, z1Attendance.Revenue, z1Attendance.GuidebookSales, z1Week.EndDate, z1Week.WeekNumber, z1Week.Year, z1Market.z1MarketCaption,

   sum(case when year(z1Week.EndDate) = year(getdate()) then 1 else 0 end) as ThisYear,

   sum(case when year(z1Week.EndDate) <> year(getdate()) then 1 else 0 end) as LastYear
 

FROM ((z1Attendance INNER JOIN z1Attraction ON z1Attendance.Attraction = z1Attraction.z1AttractionId) INNER JOIN z1Week ON z1Attendance.Week = z1Week.z1WeekId) INNER JOIN z1Market ON z1Attraction.Market = z1Market.z1MarketId;
 

WHERE z1Week.EndDate >= convert(datetime, cast(year(getdate())-1 as varchar) +'-01-01')

GROUP BY year(z1Week.EndDate)
 

FROM ((z1Attendance INNER JOIN z1Attraction ON z1Attendance.Attraction = z1Attraction.z1AttractionId) INNER JOIN z1Week ON z1Attendance.Week = z1Week.z1WeekId) INNER JOIN z1Market ON z1Attraction.Market = z1Market.z1MarketId;
 

WHERE z1Week.EndDate >= convert(datetime, cast(year(getdate())-1 as varchar) +'-01-01')

GROUP BY year(z1Week.EndDate)

Open in new window

0
 

Author Comment

by:joe10czar
ID: 20375558
Obviuosly, only the first WHERE clause is there.
SELECT z1Attendance.Attraction, z1Attendance.Attendance, z1Attraction.z1AttractionCaption, z1Attendance.Revenue, z1Attendance.GuidebookSales, z1Week.EndDate, z1Week.WeekNumber, z1Week.Year, z1Market.z1MarketCaption,

   sum(case when year(z1Week.EndDate) = year(getdate()) then 1 else 0 end) as ThisYear,

   sum(case when year(z1Week.EndDate) <> year(getdate()) then 1 else 0 end) as LastYear
 

FROM ((z1Attendance INNER JOIN z1Attraction ON z1Attendance.Attraction = z1Attraction.z1AttractionId) INNER JOIN z1Week ON z1Attendance.Week = z1Week.z1WeekId) INNER JOIN z1Market ON z1Attraction.Market = z1Market.z1MarketId;
 

WHERE z1Week.EndDate >= convert(datetime, cast(year(getdate())-1 as varchar) +'-01-01')

GROUP BY year(z1Week.EndDate)

Open in new window

0
 
LVL 25

Accepted Solution

by:
imitchie earned 125 total points
ID: 20377357
try this

SELECT Z1ATTENDANCE.ATTRACTION,
       Z1ATTENDANCE.ATTENDANCE,
       Z1ATTRACTION.Z1ATTRACTIONCAPTION,
       Z1ATTENDANCE.REVENUE,
       Z1ATTENDANCE.GUIDEBOOKSALES,
       Z1WEEK.ENDDATE,
       Z1WEEK.WEEKNUMBER,
       Z1WEEK.YEAR,
       Z1MARKET.Z1MARKETCAPTION,
       SUM(CASE
             WHEN YEAR(Z1WEEK.ENDDATE) = YEAR(GETDATE()) THEN 1
             ELSE 0
           END) AS THISYEAR,
       SUM(CASE
             WHEN YEAR(Z1WEEK.ENDDATE) = YEAR(GETDATE()) -1 THEN 1 --last year
             ELSE 0
           END) AS LASTYEAR
       
FROM   ((Z1ATTENDANCE
         INNER JOIN Z1ATTRACTION
           ON Z1ATTENDANCE.ATTRACTION = Z1ATTRACTION.Z1ATTRACTIONID)
        INNER JOIN Z1WEEK
          ON Z1ATTENDANCE.WEEK = Z1WEEK.Z1WEEKID)
       INNER JOIN Z1MARKET
         ON Z1ATTRACTION.MARKET = Z1MARKET.Z1MARKETID

WHERE z1Week.EndDate >= convert(datetime, cast(year(getdate())-1 as varchar) +'-01-01')
GROUP BY Z1ATTENDANCE.ATTRACTION,
       Z1ATTENDANCE.ATTENDANCE,
       Z1ATTRACTION.Z1ATTRACTIONCAPTION,
       Z1ATTENDANCE.REVENUE,
       Z1ATTENDANCE.GUIDEBOOKSALES,
       Z1WEEK.ENDDATE,
       Z1WEEK.WEEKNUMBER,
       Z1WEEK.YEAR,
       Z1MARKET.Z1MARKETCAPTION
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20953229
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now