Queries for reporting

Nuno Fernandes

Staff

Question

Mind the following query:
/**********************************************************/
select
product,
saleschannel,
sum(case DATEPART(weekday, saledate) when 1 then 1 else 0 end) as Sun,
sum(case DATEPART(weekday, saledate) when 2 then 1 else 0 end) as Mon,
sum(case DATEPART(weekday, saledate) when 3 then 1 else 0 end) as Tue,
sum(case DATEPART(weekday, saledate) when 4 then 1 else 0 end) as Wed,
sum(case DATEPART(weekday, saledate) when 5 then 1 else 0 end) as Thu,
sum(case DATEPART(weekday, saledate) when 6 then 1 else 0 end) as Fri,
sum(case DATEPART(weekday, saledate) when 7 then 1 else 0 end) as Sat,
count(1) as Total
from Sales
where
year(saledate) = @year and
DATEPART(week, saledate) = @week
group by product, saleschannel with rollup
order by product, saleschannel
/**********************************************************/
The interesting parts of this query are:
- the 2nd dimensioning of data presentation (on this case by weekday) given by the "sum(case ..." select field pattern;
- the "extra" sum lines freely given by the "with rollup" keywords that aggregate values of groupable lines as showed in the attached example (lines 1, 2 and 5 are put in the result by the "with rollup" keywords).

result_example.txt

08 Apr 2004

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines