Time recording query

SQL Apprentice Question
Here is an interesting problem:
Lets say I own some imaginary company. Like for all companies, my
employees report their time to some time reporting application.
Following table stores these time recording activities:

EmpId Date Project No_of_hours
1 08/01 P1 4
1 08/01 P2 3
1 08/01 P3 1
1 08/02 P3 7
1 08/02 P1 1
1 08/03 P1 4
1 08/03 P1 4


- In given month, employees spend hours on multiple projects


I want to write a query which entering the begin and end dates and the
emp id will return the projects the employee worked on during that
period and the no_of_hours he worked on each
Output I am looking should be something similar to this:


EmpId Project No_of_hours
1 P1 30
1 P2 15
1 P3 20


Any help is appreciated ...


Celko Answers
The start and stop times are what you should have been catching in the
first place and not the computed hours. Think raw data and single
facts when designing a table. Let me use a history table for price
changes. The fact to store is that a price had a duration:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date < end_date),
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);


You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a BETWEEN
predicate to get the appropriate price.


SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);


It is also a good idea to have a VIEW with the current data:


CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;