joe10czar
asked on
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.
ASKER
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.
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)
ASKER
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
Community Support Moderator
Computer101
Community Support Moderator
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