aggregate data on a per week basis

[Fearless_Shultz]

Morning monkeys,

I have a table of data in a sql server db which relates to booking data.

Basically what I am looking to write is a query which looks at monday to sunday on a weekly basis over a period of say 2 years and returns something like the following example:

Date From Date To Total Bookings Total Value

26/02/07 04/03/07 450 £55,000
05/03/07 11/03/07 500 £60,000

Obviously I have a booking date field to work with and can sum the total of bookings between just pondering on how to incremently run the query for each 7 day period....

Hope this makes sense and thanks in advance for any suggestions

[Neko]

1) Write a function that gets the start monday of the week it's in

2) Select sum of values, the GetMonday() function, and group on the latter

[Fearless_Shultz]

Cheers for the suggestion Neko but I actually ended up finding a way to get pretty much what I was after without having to write any extra functions

Code:

SELECT DATEADD(wk, DATEDIFF(wk, 0, bookingDate), 0) AS [Week Commencing], COUNT(*) AS [Total Bookings], SUM(totalcost) AS [Total Revenue]
FROM tblOrders
GROUP BY DATEADD(wk, DATEDIFF(wk, 0, bookingDate), 0)
ORDER BY DATEADD(wk, DATEDIFF(wk, 0, bookingDate), 0)