Display data by Weekly in a specified period

3 Nov 2005 10:08 PM

shil

I am have a table like this

Date      Clicks    Impressions
--------------------------------------------
10/1/2005 10              100
10/2/2005   20              100
    .
    .
    .
10/30/2005 100           1000

I want to display a report by weekly based upon the dates user selects.
User can select which week ending and how many weeks.
For example if user selects Monday as weekending and 3 weeks as how
many, then I want to show the report three weeks back from todays date,
which ends on Monday.

Here is how I needed to show

Week            Clicks                  Impressions
-----------------------------------------------------------------
10/17/2005        100                      1000
10/24/2005        200                      2000
10/31/2005        300                      3000
-----------------------------------------------------------------

Weekending 10/17 means I needed to calcluate data from previous Tuesday
to that Monday.
10/11 to 10/17, the same way weekending 10/24 means it is from 10/18 -
10/24.

Any one knows how to do this?

3 Nov 2005 11:47 PM

John Bell

Hi

This sort of thing is easy if you have a calendar table see
http://www.aspfaq.com/show.asp?id=2519 , otherwise you are resorting to using
the dateadd and datepart functions (documented in Books online)

John
Show quote

"shil" wrote:

>
> I am have a table like this
>
> Date      Clicks    Impressions
> --------------------------------------------
> 10/1/2005 10              100
> 10/2/2005   20              100
>     .
>     .
>     .
> 10/30/2005 100           1000
>
> I want to display a report by weekly based upon the dates user selects.
> User can select which week ending and how many weeks.
> For example if user selects Monday as weekending and 3 weeks as how
> many, then I want to show the report three weeks back from todays date,
> which ends on Monday.
>
> Here is how I needed to show
>
> Week            Clicks                  Impressions
> -----------------------------------------------------------------
> 10/17/2005        100                      1000
> 10/24/2005        200                      2000
> 10/31/2005        300                      3000
> -----------------------------------------------------------------
>
> Weekending 10/17 means I needed to calcluate data from previous Tuesday
> to that Monday.
> 10/11 to 10/17, the same way weekending 10/24 means it is from 10/18 -
> 10/24.
>
> Any one knows how to do this?
>
>