SQL Server Performance

Need help in query [get data week wise]

Discussion in 'SQL Server 2005 General Developer Questions' started by isa, Oct 14, 2009.

  1. isa New Member

    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.
  2. gregj New Member

    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()).
  3. TommCatt New Member

    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.

Share This Page