sql query - totals by week

[israfel]

Hi there, Can anyone give me a pointer in the right direction on how to tackle this problem. Basically i want to print out totals for all the weeks going back and the dates their from, from a table but im hitting a brick wall. i can get the totals going back seven days but i kinda want it as a rolling loop to the start of the table. and im not sure how to do this showing the dates as well

SELECT DISTINCTROW Format$(Carpark.EntryTime,'Long Date') AS [EntryTime By Day], Sum(Carpark.Bill) AS SumOfBill, COUNT(Carpark.CustomerID) AS NumberOfCars
FROM Carpark
WHERE ((DateDiff("d",Carpark.EntryTime,Now()))<=7)
GROUP BY Format$(Carpark.EntryTime,'Long Date');

this is how i do it for daily totals going back seven days, how could i change this to weekly. Thanks for any help -

[brouse]

I can't tell what DB system you are using, but you could use a loop. It would go something like:

Declare @LastEndDate datetime,
@FirstDBDate datetime

Select @FirstDBDate = Top 1 Carpark.EntryTime
from Carpark
order by EntryTime ascending

Set @LastEndDate = Now()

While (@LastEndDate > @FirstDBDate)
BEGIN

SELECT DISTINCTROW Format$(Carpark.EntryTime,'Long Date') AS [EntryTime By Day], Sum(Carpark.Bill) AS SumOfBill, COUNT(Carpark.CustomerID) AS NumberOfCars
FROM Carpark
WHERE ((DateDiff("d",Carpark.EntryTime,@LastEndDate ))<=7)
GROUP BY Format$(Carpark.EntryTime,'Long Date');

Set @LastEndDate = DateAdd("d", -7, @LastEndDate )

END

[Laurel]

Can't you use a crosstab query <shutters at the thought!>