Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    75

    Unanswered: Average of Count by Day

    Clients begin service on Date1 and end service on Date2.

    How would I go about determining the average daily number of clients over a year (any period of time)... as well as the min and max # of client on any given day during this time period?

    As with all of my problems... this sounds ridiculously easy, but I'm stumped

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Pick some point in the day that is your "yardstick" time, when you want to make your measurements. Good choices are 00:00 (midnight), 23:59 (end of the day), and 12:00 (noon), but you can use whatever time is the most meaningful to you. Populate a working table with datetime values for all of the days you want to measure and the yardstick time.

    For each row in the yardstick table, count the number of rows in the service table that straddle the yardstick row... Where the service start datetime is before the yardstick datetime, and the service end datetime is after the yardstick datetime. Use the yardstick table to determine the bounds of your query... In other words, when the query starts and when it ends.

    I could write some SQL to do this, but it would take too long to build an Access MDB unless I'm really unclear.

    -PatP

  3. #3
    Join Date
    Jan 2004
    Posts
    75
    wowwee... this may be bigger than my britches can handle...

    I understand the basic concept of what you're saying (I think).

    For hours, I was trying to figure out how to create some sort of loop where for xdate, date1 < xdate and date2 > xdate...

    But I didn't know how to capture that as a count... so I see where I may be able to work from your idea...

    Am I completely off base?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, granted that this is SQL instead of Jet (the default database engine for MS-Access), but you could use something like:
    PHP Code:
    CREATE TABLE dbo.yardstick (
       
    target DATETIME NOT NULL
       PRIMARY KEY 
    (target)
       )

    DECLARE @
    d DATETIME

    SET 
    @'2000-01-01 12:00'

    WHILE @'2010-01-01'
       
    BEGIN
          
    IF DatePart(weekday, @dIN (23456)
             
    INSERT INTO dbo.yardstick (targetVALUES (@d)
          
    SET @DateAdd(day1, @d)
       
    END 
    This will populate your working table with a decade worth of weekdays, with your target time of interest.

    Then you could build your count query as:
    PHP Code:
    SELECT y.target
    ,  (SELECT Count(*)
          
    FROM dbo.services AS s
          WHERE  y
    .target BETWEEN s.Start AND ys.EndDate) AS active_services
       FROM dbo
    .yardstick AS y
       WHERE  y
    .target BETWEEN @StartReport AND @EndReport
    I can't think of a way to make this any easier for the machine to execute... Maybe I've made it tougher to understand somehow in the process.

    edited to correct missing FROM clause

    -PatP
    Last edited by Pat Phelan; 04-13-04 at 22:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •