select customer,warehouse,sum(CASE DATEPART(yyyy, dated)
WHEN 1999 THEN val ELSE null END) as y1999,
sum(CASE DATEPART(yyyy, dated) when 2000 THEN val
ELSE null END) as y2000 from sales table where warehouse='E' or warehouse='EC'
group by customer,warehouse
order by customer
this works fine however I need to refine the datepart section to tell sql that y1999 is actually between 1st november 1998 and 31 october 1999 and so on.