- Katılım
- 1 Aralık 2010
- Mesajlar
- 313
- Excel Vers. ve Dili
- Office 2010
Merhaba,
Müşteri ziyaret raporunda; Aşağıdaki tablodaki satır toplamlarını nasıl sıralayabilirim? Ayrıca sorguda 3 ziyaret ve üzerini nasıl tanımlayabilirim?
Select
isnull (customer, 'Grand Total) as 'Definition',
isnull (count (case datepart (month, visitdate_) when 1 then customer end),0) as 1,
isnull (count (case datepart (month, visit_date_) when 2 then customer end),0) as 2,
isnull (count (case datepart (month, visit_date_) when 3 then customer end),0) as 3,
isnull (count (case datepart (month, visit_date_) when 4 then customer end),0) as 4,
isnull (count (case datepart (month, visit_date_) when 5 then customer end),0) as 5,
isnull (count (case datepart (month, visit_date_) when 6 then customer end),0) as 6,
isnull (count (case datepart (month, visit_date_) when 7 then customer end),0) as 7,
isnull (count (case datepart (month, visit_date_) when 8 then customer end),0) as 8,
isnull (count (case datepart (month, visit_date_) when 9 then customer end),0) as 9,
isnull (count (case datepart (month, visit_date_) when 10 then customer end),0) as 10,
isnull (count (case datepart (month, visit_date_) when 11 then customer end),0) as 11,
isnull (count (case datepart (month, visit_date_) when 12 then customer end),0) as 12,
isnull (count (visit_date_), 0) as Grand Total
from customer_visit
group by
grouping sets
((datepart (year, visit_date_), customer),
())
order by
grouping (customer),
grouping (datepart (year, visit_date_)),
customer;
Müşteri ziyaret raporunda; Aşağıdaki tablodaki satır toplamlarını nasıl sıralayabilirim? Ayrıca sorguda 3 ziyaret ve üzerini nasıl tanımlayabilirim?
Select
isnull (customer, 'Grand Total) as 'Definition',
isnull (count (case datepart (month, visitdate_) when 1 then customer end),0) as 1,
isnull (count (case datepart (month, visit_date_) when 2 then customer end),0) as 2,
isnull (count (case datepart (month, visit_date_) when 3 then customer end),0) as 3,
isnull (count (case datepart (month, visit_date_) when 4 then customer end),0) as 4,
isnull (count (case datepart (month, visit_date_) when 5 then customer end),0) as 5,
isnull (count (case datepart (month, visit_date_) when 6 then customer end),0) as 6,
isnull (count (case datepart (month, visit_date_) when 7 then customer end),0) as 7,
isnull (count (case datepart (month, visit_date_) when 8 then customer end),0) as 8,
isnull (count (case datepart (month, visit_date_) when 9 then customer end),0) as 9,
isnull (count (case datepart (month, visit_date_) when 10 then customer end),0) as 10,
isnull (count (case datepart (month, visit_date_) when 11 then customer end),0) as 11,
isnull (count (case datepart (month, visit_date_) when 12 then customer end),0) as 12,
isnull (count (visit_date_), 0) as Grand Total
from customer_visit
group by
grouping sets
((datepart (year, visit_date_), customer),
())
order by
grouping (customer),
grouping (datepart (year, visit_date_)),
customer;