SQL backlog calculation (MS Access)

Hi, I need to calculate the backlog from a table: components(ProductId, ComponentId, Status, StatusDate) where ComponentId, Status and StatusDate are the primary key. ProductId is a foreign key. Example:

prod1, comp1, 01, 05/01/2009
prod1, comp1, 02, 05/01/2009
prod1, comp1, 03, 06/01/2009
prod1, comp1, 01, 07/01/2009
prod1, comp1, 02, 20/01/2009
prod2, comp2, 01, 22/01/2009
prod1, comp1, 02, 23/01/2009
prod1, comp1, 03, 31/01/2009

Basically what I am trying to calculate is the number of Components per week in status lower than 03. End user will introduce an interval date so I need to show all the weeks in the interval even if there is not backlog for a week. Expected result when end user introduces 01/01/2009-22/01/2009:

Week, Backlog
1,NULL/0
2,1
3,1
4,2

Explanation for Week 2: comp1 reach status 03 in the week but then goes back to status 01
Any help is more than welcome, thanks

From stackoverflow

• This is a partial answer in that I do not see where week 3 (11-18 Jan 2009) is coming from in your example. It illustrates the use of a counter table to get a line for missing values.

  SELECT Counter,WeekNo, CountofStatus FROM Counter LEFT JOIN
      (SELECT Format([StatusDate],"ww") AS WeekNo, COUNT(c.Status) AS CountOfStatus
      FROM components c
      WHERE c.StatusDate BETWEEN #1/1/2009# AND #1/22/2009#
      AND c.Status<3
      GROUP BY Format([StatusDate],"ww")) Comp
  ON Val(Comp.Weekno)=Counter.Counter   
  WHERE Counter.Counter>=Val(Format(#1/1/2009#,"ww"))
  AND Counter.Counter<=Val(Format( #1/22/2009#,"ww"))
  

  Remou : So the status is continuous and only marked at date changed? If so, my example does not suit.

  Remou : Your example shows two components, do you have a set number of components and is it large?

• I'm guessing a bit as to what you're trying to do, but here's my best guess:

  First, you should have a calendar table in your database:

  CREATE TABLE Calendar (
       calendar_date DATETIME NOT NULL,
       week_number INT NOT NULL,
       CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
  )
  GO
  
  INSERT INTO Calendar (calendar_date, week_number) VALUES ('1/1/2009', 1)
  INSERT INTO Calendar (calendar_date, week_number) VALUES ('2/1/2009', 2)
  etc.
  

  You can add additional columns to the table based on your business needs. For example, an "is_holiday" bit column to track whether or not your office is closed that day. This table makes so many different queries trivial.

  Now for your problem:

  SELECT
       CAL.week_number,
       COUNT(DISTINCT C.component_id)
  FROM
       Calendar CAL
  LEFT OUTER JOIN Components C ON
       C.status_date = CAL.calendar_date AND
       C.status IN ('01', '02')
  WHERE
       CAL.calendar_date BETWEEN @start_date AND @end_date
  GROUP BY
       CAL.week_number
  

  I used the IN for the status since you're using strings, so "< '03'" might not always give you what you want. Is '1' less than '03' in your mind?

  Also, if there is a time component on any of your dates the equality and BETWEEN checks might need to be tweaked.

  EDIT: I just saw the comments on the other answer. If you are dealing with just status changes, then the following query should work, although there may be a more performant method:

  SELECT
       CAL.week_number,
       COUNT(DISTINCT C.component_id)
  FROM
       Calendar CAL
  LEFT OUTER JOIN Components C ON
       C.status_date <= CAL.calendar_date AND
       C.status IN ('01', '02')
  LEFT OUTER JOIN Components C2 ON
       C2.component_id = C.component_id AND
       C2.status_date > C.status_date AND
       C2.status_date <= CAL.calendar_date
  WHERE
       CAL.calendar_date BETWEEN @start_date AND @end_date AND
       C2.component_id IS NULL
  GROUP BY
       CAL.week_number
  

  I'm not sure where the product fits in with all of this though.