Cross Tab query

aware · June 29th, 2006, 05:40 AM

Hi

I think this is a cross tab query, but not sure, either way I's appreciate some help.

I have a single table of weekly ratings similar to this
id
date
cost centre
rating
organisation

What I need out a row per cost centre with the last 4 weeks rating by organisation

like this

org1
cost centre1 week1 week2 week3 week4
cost centre2 week1 week2 week3 week4
cost centre3 week1 week2 week3 week4

org2
cost centre1 week1 week2 week3 week4
cost centre2 week1 week2 week3 week4
cost centre3 week1 week2 week3 week4

Any help would be great

Regards

ANdy

Peso · June 29th, 2006, 02:06 PM

1. Last 4 weeks including present week? Or last 4 weeks before present week?
2. Define week. Running 7 days or first week of year starting with January 1 or any sunday to monday?
3. Rating is ascending or descending? Rating on what aggregation for organization?

aware · June 29th, 2006, 04:08 PM

Hi

Last 4 weeks including this week, the weeks is stored in the table as a week number, so no date manipulation is required.

Just the last 4 entries per cost centre by org.

The rating is a varchar field and can be either B R A or G.

Does that clear things up.

Regards & thanks for your time

Andy

Peso · June 29th, 2006, 04:22 PM

Too much is unknown for completing this task, but this should give you an idea how to proceed.

Code:

declare @test table (cc varchar(50), dt datetime, amount int)

insert @test
select 'cost centre 1', '2006-01-19', 12 union all
select 'cost centre 1', '2006-02-19', 36 union all
select 'cost centre 1', '2006-03-19',  3 union all
select 'cost centre 1', '2006-04-19', 34 union all
select 'cost centre 1', '2006-05-19', 11 union all
select 'cost centre 1', '2006-06-19',  5 union all
select 'cost centre 1', '2006-07-19', 33 union all
select 'cost centre 2', '2006-01-19', 22 union all
select 'cost centre 2', '2006-02-19', 78 union all
select 'cost centre 2', '2006-03-19', 12 union all
select 'cost centre 2', '2006-04-19', 95 union all
select 'cost centre 2', '2006-05-19', 41 union all
select 'cost centre 2', '2006-06-19', 39 union all
select 'cost centre 2', '2006-07-19', 16

declare @today datetime
select @today = getdate()

select    cc,
          sum(case when convert(varchar(6), dt, 112) = CONVERT(varchar(6), dateadd(mm, -3, @today), 112) THEN amount else 0 end) 'Month 1',
          sum(case when convert(varchar(6), dt, 112) = CONVERT(varchar(6), dateadd(mm, -2, @today), 112) THEN amount else 0 end) 'Month 2',
          sum(case when convert(varchar(6), dt, 112) = CONVERT(varchar(6), dateadd(mm, -1, @today), 112) THEN amount else 0 end) 'Month 3',
          sum(case when convert(varchar(6), dt, 112) = CONVERT(varchar(6), dateadd(mm, -0, @today), 112) THEN amount else 0 end) 'Month 4'
from      @test
group by  cc

select @today = '2006-03-25'

select    cc,
          sum(case when convert(varchar(6), dt, 112) = CONVERT(varchar(6), dateadd(mm, -3, @today), 112) THEN amount else 0 end) 'Month 1',
          sum(case when convert(varchar(6), dt, 112) = CONVERT(varchar(6), dateadd(mm, -2, @today), 112) THEN amount else 0 end) 'Month 2',
          sum(case when convert(varchar(6), dt, 112) = CONVERT(varchar(6), dateadd(mm, -1, @today), 112) THEN amount else 0 end) 'Month 3',
          sum(case when convert(varchar(6), dt, 112) = CONVERT(varchar(6), dateadd(mm, -0, @today), 112) THEN amount else 0 end) 'Month 4'
from      @test
group by  cc