[AccessD] SQL Query Help: Calculate working (project) days

Hi Paul

> I need help in calculating working days shown in table below.  The tricky thing about this is I need to calculate the number of days between departments, particularly the 'Pricing' department.

> In table below, there are a total of 7 days using DATEDIFF.  I need to report number of days within Pricing, which is two days in the example table shown below.  i.e. Pricing on 2004-05-01 to
> 2004-05-2 equals 1 day and Pricing again on 2004-05-07 to 2004-05-08.

> table1
> StatusDate      Department
> 2004-05-01      Pricing (1 day)
> 2004-05-02      Costing (5 day)
> 2004-05-07      Pricing (1 day)
> 2004-05-08      Closed  

Assuming you have a DepartmentID and this is zero or Null for Closed
(no department), you can obtain the day counts for the departments:

<SQL>

  SELECT
    DepartmentID,
    DateDiff("d",
      [StatusDate],
      (Select Top 1
        aliT.StatusDate
      From tblProjectDates As AliP
      Where
        aliP.StatusDate > tblProjectDates.StatusDate
      Order By aliP.StatusDate;)) AS Days
  FROM
    tblProjectDates
  ORDER BY StatusDate;

</SQL>

Now, save this query and use it as source for another query where you
Group By DepartmentID and Sum Days.

/gustav