Compute subtotal by date range where only starting date is known

[Guest]

I figure some of you experts out there must have run across this type of
query/join before, so thought I would ask before I started beating my brains
out...

Have a table of individual effort by project by day, and a separate table of
project phases and their starting dates. E.g.

Effort table
-------------
UserName ProjectName Date Hours
user1 projectA 1/1/05 4
user1 projectA 1/3/05 3
user1 projectA 1/9/05 3
user1 projectB 1/2/05 2
user2 projectA 1/1/05 1
user2 projectA 1/2/05 2
user2 projectB 1/8/05 2

Phase Table
--------------
ProjectName Phase StartDate
projectA phase1 12/31/04
projectA phase2 1/4/05
projectB phase1 12/31/04

Need to be able to report effort by project phase, so results would look
something like:

User Project Phase EffortInPhase
user1 projectA phase1 7
user1 projectA phase2 3
user1 projectB phase1 2
user2 projectA phase1 3
user2 projectA phase2 0 (or better do not even include this row)
user2 projectB phase1 2

Any suggestions as to how best to implement. Development and initial work
in Access, but expect to have to port to SQLServer 2000 in near future as
well.

Any help you could provide would be appreciated.

Thanks!

JDRaven

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Phase.ProjectName should be an ID number, referring to the Projects
table. Same for UserName - it should be UserID, referring to the Users
table.

Phase.phase column should be a number. Using "phase1", "phase2" - the
word "phase" is redundant. A number in the "phase" column is
sufficient.

Don't use Date as a column name: Access has a Date function that could
be confused w/ the column name.

Anyway, with your defined tables, perhaps:

SELECT e.user, p.project, p.phase, Sum(e.hours) As EffortInPhase

FROM phase p INNER JOIN effort e
ON p.project_name = e.project_name

WHERE e.[date] BETWEEN p.start_date And
(SELECT Min(start_date) FROM phase
WHERE project_name = p.project_name
AND phase > p.phase ) - 1

GROUP BY e.user, p.project, p.phase

HAVING Sum(e.hours) > 0


The subquery in the main query's WHERE clause will get the start_date of
the next phase and subtract 1 day from it, thereby providing the end
date for the BETWEEN evaluation.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgK+M4echKqOuFEgEQI/VwCghXoPfTSJzPHxnkNLkzsTYjCc5swAnjq5
DIjFp1peBsGNt2my5eCS0gUe
=n3n8
-----END PGP SIGNATURE-----