[Solved] Excluding values from a table in a query of another
Posted: Wed Aug 10, 2011 8:00 pm
Hi,
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:
if i change the 6th line's ""hours"."date"="Holidays"."Holiday Date"" to have a != instead of the =, the results i get are wacky. how can change that to sum all the entries that are not in the holidays?
thanks in advance, and please let me know if any more information is needed.
-Jason
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