Theory/Advice to find "missing data"

[Homegrownandy]

The question is relating to how we will deal with contractors timesheets. There are currently 4 tables this could relate to,

Staff - engineer info (name)

Timesheet - info about that weeks work (hours miles ect)

PO - this is the order number and description for that engineers time

Weekending - I've made a table that lists every Sunday for about two years. (This is used as a validation check for info we receive.)

the line engineers table Isn't relevant for this.




My problem:



I want to show ever engineer that does not have a time sheet < date(). I'm willing to restructure if the idea of listing all the Sundays isn't the best one.

Just some advice in the direction I'm heading would be appreciated. I've been using unmatched query's for most of the day and had no success.

Thanks in advance, Andy

[CJ_London]

you would use a left join, not quite sure what your tables are showing - perhaps provide some example data.

But in principle it would be

SELECT S.*
FROM staffT S LEFT JOIN (SELECT EngineerID FROM PO_Engineers E INNER JOIN TimesheetData T ON E.Engineerid=T.engineerid WHERE T.Weekending<Date()) X ON S.StaffID=X.StaffID
WHERE X.EngineerID IS NULL

[Homegrownandy]

I'm still testing/reading. Just to try an explain a bit better.

If I have 5 engineers that did not submit a timesheet last week I want to see their name and the date of when they didn't submit. so the date record in this case would appear 5 times.

also if an engineer didn't submit for the past 5 weeks then his name would appear 5 times. (with the corresponding dates not submitted)


I think I'm getting closer.... But as always your input is appreciated

[CJ_London]

Not sure why you are getting 5 records when it should be returning only those without a record

[Homegrownandy]

No, this is what I want to see that's why this is difficult. I want to see the 5 or 6 or however many weeks information I'm missing g for however many people.

[CJ_London]

misread your post.

in that case you will need a slightly different query

Replace

...staffT S LEFT JOIN ....

with

...(SELECT DISTINCT StaffID, Weekending FROM StaffT, TimesheetData WHERE Weekending<Date) S LEFT JOIN ....

As before, you don't need the weekending table, just put the weekending value back into timesheetdata instead of the weekendingID. The only time the above won't work is if none of the engineers have submitted a timesheet.

If this matters, you just need a table of dates and replace TimesheetData in the above change with the name of the table, you don't need to link it to timesheetdata

[Homegrownandy]

Okay. Well. I have some reading to do. Thanks