hi I have the following select statement:-

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.

Can this statement be amended to do this?

Thanks for any help!

Try adding 2 months to column DATED before analyzing the datepart.

select
customer,
warehouse,
sum(CASE DATEPART(yyyy, dateadd(month,2,dated))
WHEN 1999 THEN val ELSE null END) as y1999,

sum(CASE DATEPART(yyyy, dateadd(month,2,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