Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Report comparing year over year data

Posted on 2007-11-28
6
Medium Priority
?
221 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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

650 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