• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Report comparing year over year data

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
joe10czar
Asked:
joe10czar
  • 2
  • 2
1 Solution
 
imitchieCommented:
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
 
joe10czarAuthor Commented:
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
 
joe10czarAuthor Commented:
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
 
imitchieCommented:
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
 
Computer101Commented:
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now