I want to select data that is not meeting a certain criteria, in my case I want to select from rows where the the VALUE of a column called PSD_MIN (minutes worked on a certain day) is less than a certain value such as 480. The SQL at the bottom works great for data that is in the table but not where there is not row in the detail table.
in my example, for the week I would usually have 5 rows in PSDETAIL 1 for each day of the week with '20090831', '20090901', '20090902', '20090903' and '20090904'. There is no rows in the table for '20090829' and '20090830' since these are Saturday and Sunday.and since folks do not work these days we do not store any data for them.
So the question is, how do I get a result back that includes the criteria set and also for the missing rows as my example shows for Saturday and Sunday which in my example I specify I want to compare for these dates, but there are no rows to compare against.
Please keep in mind that I must write this SQL to be compatible with SQL Server, Oracle and Sybase. I can separate the code of course for each db if required, but would prefer one that works for all db's or a solution for each.
SELECT DISTINCT PSH_KEY, PSH_WEDATE, 'Hours Missing' AS TSH_STATUS,EMH_NAME
FROM PSHEADER,EMPHIS,PSDETAIL,P
SLINES
WHERE PSH_EMH=EMH_KEY AND PSL_PSH=PSH_KEY AND PSD_PSL=PSL_KEY
AND PSH_WEDATE = '20090904' AND ((PSD_DATE='20090829' AND PSD_MIN < 480) OR (PSD_DATE='20090830' AND PSD_MIN < 600) OR (PSD_DATE='20090831' AND PSD_MIN < 480)
OR (PSD_DATE='20090901' AND PSD_MIN < 480) OR (PSD_DATE='20090902' AND PSD_MIN < 480) OR
(PSD_DATE='20090903' AND PSD_MIN < 480) OR (PSD_DATE='20090904' AND PSD_MIN < 480))
ORDER BY PSH_WEDATE,EMH_NAME
