Need help in query [get data week wise]

Hello everyone, i want to show the data of previous 3 months but week wise, Here is my query which show the data of last 3 months but the date wise.

SELECT count(VehicleRecognitionLogID) as Total, CONVERT(CHAR(10),DateTime,110) as Date
FROM dbo.VehicleRecognitionLogs
where DateTime <= getdate() and
DateTime >= DATEADD (mm , -3, GetDate())
Group by DateTime

it displays data like this:

Total Date
2 10-11-2009
1 10-12-2009

but i want to show data week wise of previous 3 months so i m doing this

SELECT count(EventLogID) as EventsGenerate, DatePart(wk,CreateDate) as CreateDate
FROM dbo.EventLogs
where CreateDate <= getdate() and
CreateDate >= DATEADD (mm , -3, GetDate())
Group By DatePart(wk,CreateDate)

now it displays data like this:

Total Date
3 42

but i want to display data like this::::::::::

Total Date
3 Week1
2 week2 and so on ..........................

plz tell me how i do this. Thanx in Advance.

Do you have more than 1 week's worth of data in your tables now? If so, you should get the results you are expecting.Is it important to show a full week at a time, or is it OK to show results from partial weeks? If you need to show full weeks, you might want to change your WHERE condition to the following:WHERE DatePart(wk,CreateDate) BETWEEN DatePart(wk,DateAdd(mm,-3,GetDate())) AND DatePart(wk,GetDate()).

The first thing you have to do is establish the time zero (T0) of yourquery. For this you cannot use the DatePart function as this will notproperly handle a three month period that spans from one year to the nextyear. And you can't just subtract three months from NOW because itcould start off in the middle or end of a week and the first week's datawould be wrong. So your T0 would be this:

DateAdd( wk, 0, DateDiff( wk, 0, DateAdd( mm, -3, GetDate())))

This will start you off at the beginning of the first week that started at least three months ago. So your WHERE clause will look like this:

WHERE CreateDate between DateAdd( wk, 0, DateDiff( wk, 0, DateAdd( mm, -3, GetDate()))) and GetDate()

or if CreateDate can't be in the future

WHERE CreateDate >= DateAdd( wk, 0, DateDiff( wk, 0, DateAdd( mm, -3, GetDate())))

To get the "WeekN" output you want, you have to count the number of weeks from T0 to the date value:

'Week' + DateDiff( wk, DateAdd( mm, -3, GetDate()), CreateDate )

and your GROUP BY clause will have to look like this:

GROUP BY DateDiff( wk, DateAdd( mm, -3, GetDate()), CreateDate )

Slap it all together and your query should look something like this:

SELECT Count(*) as Total, 'Week' + DateDiff( wk, DateAdd( mm, -3, GetDate()), CreateDate ) as Date
FROM dbo.EventLogs
WHERE CreateDate between DateAdd( wk, 0, DateDiff( wk, 0, DateAdd( mm, -3, GetDate()))) AND GetDate()
GROUP BY DateDiff( wk, DateAdd( mm, -3, GetDate()), CreateDate );

Sorry -- I'm not able to test it out at the moment, but this should at least get you close.