Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Hi All,
I have a simple staff booking table where everytime a staff member is 'out' it gets logged (B_ID is the primary key and auto incremented, and staff can have multiple entries for the same date), like so -
code:
B_ID | B_Date | Staff_ID
-----|------------|----------
1 2012-02-20 10
2 2012-02-20 11
3 2012-03-20 11
I basically need to return a list of any Staff member that haven't been 'out' for the last 6 months but i'm having a seriously bad case of brain farts today.
So far i can return the last booking of a specific staff -
code:
SELECT b2.`Staff_ID`, b2.`B_Date`
FROM `bookings` b2
WHERE b2.`Staff_ID` = 11
ORDER BY b2.`B_Date` DESC
LIMIT 1
or something like
SELECT MAX(b2.`B_Date`), b2.`Staff_ID`
FROM `bookings` b2
WHERE b2.`Staff_ID` = 11
LIMIT 1
But I'm being completely dense it grabbing a list of all Staff IDs that have a last known booking date (B_DATE) older than 6 months.
Anyone got any ideas/pointers?
[Edited on 23-02-2012 by Dom]
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Something like:
SELECT b2.`Staff_ID`, MAX(b2.'B_Date')
FROM `bookings` b2
GROUP BY b2.'Staff_ID'
HAVING MAX(b2.'B_Date') < DATEADD(mm, -6, GetDate())
Sorry I don't know MySQL so there's a bit of SQL Server in there 
[Edited on 23-02-2012 by James]
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
MySQL might be something like this:
SELECT b2.`Staff_ID`, MAX(b2.'B_Date')
FROM `bookings` b2
GROUP BY b2.'Staff_ID'
HAVING MAX(b2.'B_Date') < DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
Edit: Left a bit of SQL Server in there 
[Edited on 23-02-2012 by James]
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
You lovely Man!! 
HAVING clause is what i was after, been pissing around all morning trying to work/remeber it out  
Cheers again 
[Edited on 23-02-2012 by Dom]
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
No worries. Sort out your column names though
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by James
No worries. Sort out your column names though
They were only reference names for this, not the actual names of columns 
Cheers again though 
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
Glad to hear it.
I'm happy to help with SQL. I was Mr SQL in my old job
|
