Orderdatabase - Difference in sale from one year to another - SQL?

[Helge's]

I have an orderdatabase with DATE and SALES as one of the fields in a
table. How would the SQL look like if I want a table with MONTH, 2006,
2005, DIFFERENCE. I think when I have the field DATE I can get month
and the year.

Thanks
Helge

[RoyVidar]

Helge's wrote in message
<11**********************@y41g2000cwy.googlegroups .com> :

I have an orderdatabase with DATE and SALES as one of the fields in a
table. How would the SQL look like if I want a table with MONTH,
2006, 2005, DIFFERENCE. I think when I have the field DATE I can get
month and the year.

Thanks
Helge

Hei Helge!

I guess there are lot of possibilities for this. Here's one quickly
thrown together suggestion which includes an integer table containing
(integer) numbers 1-N.

select monthname(i.id),
(select sum(SALES) from YourTable where Year(YourDate) =
Year(Date()-1) and Month(YourDate) = i.id) as s2005,
(select sum(SALES) from YourTable where Year(YourDate) =
Year(Date()) and Month(YourDate) = i.id) as s2006,
(select sum(SALES) from YourTable where Year(YourDate) =
Year(Date()) and Month(YourDate) = i.id) -
(select sum(SALES) from YourTable where Year(YourDate) =
Year(Date()-1) and Month(YourDate) = i.id)
from tblIntegers i
where i.ID <= 12
order by i.id

You might need usage of the NZ function, too, should any of them be
Null

--
Roy-Vidar