SQL backlog calculation (MS Access)

Question

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

Answer 1

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"))

Answer 2

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.