I'm developing a database to track hours and calculate overtime, hours per job, and such things. i'm replacing a spreadsheet that has all the functionality i need, but has gotten ridiculous. the only experience i have with databases is what i've gained so far on this project, so i apologize if this is a dumb question. i'm using ooo 3.3.0 on windows 7 pro x64. I have a query that totals hours grouped by person and week. I want to exclude from those totals entries whose date matches one of the dates listed in a table of holidays. the following query does the inverse of that, pulling only those hours that match holidays:
Code: Select all
SELECT "guys"."name",
YEAR( "hours"."date" ) AS "year",
case when dayofweek("hours"."date") = 1 then week("hours"."date")-1 else week("hours"."date") end as "week",
SUM( "hours"."hours" ) AS "total hours"-
FROM "guys", "hours", "jobs", "Holidays"
WHERE "guys"."ID" = "hours"."guyID" AND "jobs"."ID" = "hours"."jobID" AND "hours"."date"="Holidays"."Holiday Date"
GROUP BY "guys"."name", "year","week"
ORDER BY "guys"."name" ASC, "year" asc, "week" ASC
thanks in advance, and please let me know if any more information is needed.
-Jason