sql query - totals by week


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: sql query - totals by week

  1. #1
    Join Date
    Mar 2005
    Posts
    1

    Unhappy sql query - totals by week

    Hi there, Can anyone give me a pointer in the right direction on how to tackle this problem. Basically i want to print out totals for all the weeks going back and the dates their from, from a table but im hitting a brick wall. i can get the totals going back seven days but i kinda want it as a rolling loop to the start of the table. and im not sure how to do this showing the dates as well

    SELECT DISTINCTROW Format$(Carpark.EntryTime,'Long Date') AS [EntryTime By Day], Sum(Carpark.Bill) AS SumOfBill, COUNT(Carpark.CustomerID) AS NumberOfCars
    FROM Carpark
    WHERE ((DateDiff("d",Carpark.EntryTime,Now()))<=7)
    GROUP BY Format$(Carpark.EntryTime,'Long Date');

    this is how i do it for daily totals going back seven days, how could i change this to weekly. Thanks for any help -

  2. #2
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    I can't tell what DB system you are using, but you could use a loop. It would go something like:

    Declare @LastEndDate datetime,
    @FirstDBDate datetime

    Select @FirstDBDate = Top 1 Carpark.EntryTime
    from Carpark
    order by EntryTime ascending

    Set @LastEndDate = Now()

    While (@LastEndDate > @FirstDBDate)
    BEGIN

    SELECT DISTINCTROW Format$(Carpark.EntryTime,'Long Date') AS [EntryTime By Day], Sum(Carpark.Bill) AS SumOfBill, COUNT(Carpark.CustomerID) AS NumberOfCars
    FROM Carpark
    WHERE ((DateDiff("d",Carpark.EntryTime,@LastEndDate ))<=7)
    GROUP BY Format$(Carpark.EntryTime,'Long Date');

    Set @LastEndDate = DateAdd("d", -7, @LastEndDate )

    END
    Bob Rouse
    Dimension Data

  3. #3
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557
    Can't you use a crosstab query <shutters at the thought!>
    A balanced diet is a cookie in each hand.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center