I am fairly new to sql and have never used cross tabs. I have list a charities that receive 2 to 3 flats of mail a day. I need a report that shows how many come in for each one by date, but the problem is totaling for each charity by week. similar to whats below.DATE Total Charity Charity Charity Charity Charity Charity Charity Charity Charity Charity Charity Charity 1/31 40.25 11.5 7.25 7.5 0.5 7.25 2 4.25 2/1 8.75 3 1 1.5 0.75 0.75 0.25 1.5 2/2 8.75 3.25 1.5 1.25 0.25 1 0.5 1 2/3 17.50 3.5 7 2 1 1.5 0.75 1.75 2/4 18.75 7 4 2 1.5 1.25 1 2 week 94.00 0.00 28.25 20.75 14.25 4.00 11.75 4.50 10.50 0.00 0.00 0.00 0.00 0.002/7 41.75 12 10 3.5 7 2 3.5 3.75 2/8 6.00 1.25 1 0.5 1 0.5 1.25 0.5 2/9 7.25 1 2 0.5 1.5 0.75 1.5 0 2/10 15.25 2 4.25 1 2 1.5 4 0.5 2/11 16.00 1.5 5.75 1 1.75 0.5 5 0.5
I can get it to display each daily amount for the charity, but have no idea to total by week. Can anyone point me in the right direction.Here is what I have to far. Any help would be greatly appreciated.ALTER PROC usp_DAILYASselect [date], COUNT(case when okey = 1 then completed else null end) as CH1, count(case when okey = 2 then completed else null end) as CH2, count(case when okey = 3 then completed else null end) as CH3, count(case when okey = 4 then completed else null end) as CH4, count(case when okey = 5 then completed else null end) as CH5, count(case when okey = 6 then completed else null end) as CH6, count(case when okey = 7 then completed else null end) as CH7, count(case when okey = 8 then completed else null end) as CH8, count(case when okey = 9 then completed else null end) as CH9, count(case when okey = 11 then completed else null end) as CH10, count(case when okey = 12 then completed else null end) as CH11, count(case when okey = 13 then completed else null end) as CH12, count(case when okey = 14 then completed else null end) as CH13, count(case when okey = 15 then completed else null end) as CH14, count(*) as Total from CagingTransactionsgroup by [date]
Cross tabs
Posted - 2005-10-20 : 10:24:07
