List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:June 16 2010 12:01pm
Subject:Re: query help
View as plain text  
Hi!


Jay Blanchard wrote:
> [snip]
> I have a table similar to this:
> 
>  -------------------------
> |transactions             |
> |ID   |DATE      |EMPLOYEE|
> |234  |2010-01-05| 345    |
> |328  |2010-04-05| 344    |
> |239  |2010-01-10| 344    |
> 
> Is there a way to query such a table to give the days of the year that
> employee 344 did not have a transaction?
> [/snip]
> 
> SELECT DATE
> FROM transactions
> WHERE EMPLOYEE != '344'
> GROUP BY DATE;

I strongly doubt this will work - what if several employees have
transactions on the same day?

No, what the poster effectively needs is a set difference:
Take the set of all candidate dates, and subtract the set of days on
which the employee in question did have a transaction.

The first difficulty will be to construct the set of candidate dates, as
this needs a decision what to do about non-working dates (weekends,
public holidays, ...) and how to determine them - depending on the
business logic, that set may be specific to the employee (personal
vacation!).

Only when this has been decided, there is the question how to implement
the set difference:
- SQL "minus" is a candidate, but MySQL doesn't support that AFAIK.
- Outer Join is the other possibility, as proposed by Gavin.
- Having all candidate dates in some temporary table and then deleting
  those with a transaction is another way, but probably very slow.
  (The advantage of this might be that it is the most flexible way.)


Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  Joerg.Bruehe@stripped
Sun Microsystems GmbH,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028

Thread
query helpRichard Reina15 Jun
  • RE: query helpGavin Towey15 Jun
  • RE: query helpJay Blanchard15 Jun
    • Re: query helpJoerg Bruehe16 Jun
  • Re: query helpRichard Reina16 Jun
    • RE: query helpMartin Gainty16 Jun