Solved

Report comparing year over year data

Posted on 2007-11-28
6
208 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

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 …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

822 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