need help in query [get data week wise]

• isa-369599

  SSCommitted

  Points: 1602

  More actions

  October 14, 2009 at 5:10 am

  #212109

  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 byDateTime

  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.

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  October 14, 2009 at 6:15 am

  #1065606

  SELECT count(EventLogID) as EventsGenerate, DatePart(wk,CreateDate) as CreateDate,

  row_number() over (order by DatePart(wk,CreateDate))

  FROM dbo.EventLogs

  where CreateDate <= getdate() and

  CreateDate >= DATEADD (mm , -3, GetDate())

  Group By DatePart(wk,CreateDate)

  ---

  
  Clear Sky SQL
  My Blog[/url]