Solved

Report comparing year over year data

Posted on 2007-11-28
6
215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

695 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