Hi this is quite a tricky one (for me). I have three tables.
One contains mobile numbers and a unique id (tbldealermobiles)
FCS - Mobile number
1234 - 07464648787
1234 - 07565465465
3566 - 07878989989
7899 - 07464646466
7899 - 07565465464
9654 - 07589898998
One contains purchase dates and a unique id and other details (tblhistory)
FCS - purchase date - purchased
1234 - 22/04/2013 - gloves
1234 - 14/03/2013 - hat
1234 - 01/03/2013 - coat
3566 - 20/04/2013 - gloves
3566 - 19/04/2012 - hat
7899 - 14/03/2013 - shoes
9654 - 24/05/2013 - hat
9654 - 19/04/2013 - shoes
One contains customer type and a unique id and other details. (tblAllDealers)
FCS - Cust type - name
1234 - Virtual - Jim
3566 - Outbound - Jon
7899 - Virtual - Jack
9654 - Outbound - Susan
My problem comes when I want to display customers who have bought more than 30 days ago if they're 'Outbound' and more than 60 days ago if they're virtual.
I only want to return all the mobile numbers for Jon and Jack because the others have purchased since the dates specified for their customer type.
I'm using INNER JOIN to link the 3 tables on the unique id(FCS), I'm using MAX to return only values who's MAX value is less than a date but I have no clue how to add criteria to specify two different dates.
Here is the query I have so far -
SELECT *
FROM tbldealermobiles
INNER JOIN tblhistory ON tbldealermobiles.FCS = tblhistory.FCS
INNER JOIN tblAllDealers ON tbldealermobiles.FCS = tblAllDealers.FCS
WHERE (tblAllDealers.CustGroup = 'Virtual' AND
tblhistory.PurchaseDate < date('2013-03-22'))
OR
(tblAllDealers.CustGroup = 'Outbound' AND
tblhistory.PurchaseDate < date('2013-04-21'))
GROUP BY tbldealermobiles.mobilenumber
HAVING MAX(tblhistory.PurchaseDate) < date('2013-04-21')
ORDER BY tblhistory.PurchaseDate DESC
The problem is that the FCS will probably always have a purchase date earlier than the date specified but I only want to return a mobile number that hasn't got a purchase date after the date specified depending on customer group.
Thanks in advance for your help.
Edit: thanks for formatting my code Dukeling.