ansaurus

Question

Answer 1

+3 A:

The first query fetches all the users that have no registrations between @start and @end:

SELECT users.userName
FROM users
LEFT JOIN timesheets
ON timesheets.timesheetForUser = users.userID
AND timesheets.timesheetForDate BETWEEN @start AND @end
WHERE timesheets.timesheetForUser IS NULL

This query fetches all users that are missing any days and which days they are missing (as you requested in an comment to your question):

SELECT dates.timesheetForDate, users.userName
FROM (SELECT DISTINCT timesheetForDate FROM timesheets) AS dates
CROSS JOIN users
LEFT JOIN timesheets
    ON timesheets.timesheetForUser = users.userID
    AND dates.timesheetForDate = timesheets.timesheetForDate
WHERE timesheets.timesheetForUser IS NULL

Test bed:

CREATE TABLE timesheets (timesheetForUser int, timesheetForDate datetime);
INSERT INTO timesheets (timesheetForUser, timesheetForDate) VALUES
(1, '2010-01-01'),
(2, '2010-01-01'),
(3, '2010-01-01'),
(1, '2010-01-02'),
(3, '2010-01-02'),
(2, '2010-01-03'),
(2, '2010-01-04'),
(3, '2010-01-04');

CREATE TABLE users (userId int, userName nvarchar(100));
INSERT INTO users (userId, userName) VALUES
(1, 'Foo'),
(2, 'Bar'),
(3, 'Baz');

Output from second query using test bed:

'2010-01-02 00:00:00', 'Bar'
'2010-01-03 00:00:00', 'Foo'
'2010-01-03 00:00:00', 'Baz'
'2010-01-04 00:00:00', 'Foo'

If you want you can also create the second query as a view and query it like this:

SELECT * FROM ViewMissingRegistrations
WHERE timesheetForDate BETWEEN @start AND @end

Mark Byers 2010-01-21 17:25:10

this does not work as there is no record made in timesheets for days which no time is logged. Unless I really misunderstand SQL which is possible too :S

Jonathan 2010-01-21 17:47:55

@Jonathan, it's using a LEFT JOIN which means that it will return NULL for columns in the right hand table. it should work fine, unless I've misunderstood your question.

AJ 2010-01-21 18:02:01

@Johnathan: I've updated my query to use your column names. I've also added a second query that answers your updated question that you put as a comment to your question. The second query also includes a testbed so that you can see it works here.

Mark Byers 2010-01-21 18:13:44

Answer 2

A:

You could try

SELECT  u.*
FROM    Users u LEFT JOIN
        timesheets t ON u.userid = t.userid
WHERE   t.Date BETWEEN '01 Jan 2009' AND '31 Jan 2009'
AND     t.userID IS NULL

astander 2010-01-21 17:25:59

I think this won't work. I think that if t.userId is null then t.DATE will be null too, so it seems to me that your WHERE clause will always be false. Anyway it returns no rows for the testbed I posted above (after changing the column names accordingly).

Mark Byers 2010-01-21 18:40:58

Answer 3

+2 A:

SELECT * FROM Users U
   WHERE U.UserNo NOT IN (
     SELECT timesheetForUser FROM timesheets 
       WHERE timesheetForDate BETWEEN ??? AND ???
   )

Carlos Gutiérrez 2010-01-21 17:56:55

thx, that was exactly what I needed. I somehow managed to forget that mysql can do subqueries :S.

Jonathan 2010-01-21 18:41:59

ansaurus

tags:

views:

answers:

MySQL absentee report script

related questions