How to select minimum date where null is also possible
MikailDec 28 2011 — edited Dec 28 2011Hi,
I have Customers table with following columns:
CustomerID (PK),
CustomerName,
ExpDate
and I have another table called Accesses with following columns:
AccessID (PK),
CustomerID (FK),
FacilityID(FK),
ExpDate
Here I'm giving you a sample data view:
AccessID CustomerID FacilityID ExpDate
_______ _________ ________ ___________
---1--------------001------------2---------12-May-2012
---2--------------001------------3---------NULL
---3--------------001------------4---------23-Feb-2013
As you see CustomerID is foregn key in Accesses table, so there's one-to-many relationshio between them. Now, I need to set ExpDate in Customers table to the minimum ExpDate in Accesses table. But I need to make sure that the ExpDate values in Accesses table are greater than today and take into consideration that like in the sample above if a customer's facility access expiration dates are the mixture of NULL and certain dates then NULL should not be taken as minimum, since NULL simply means there's no limit, so the Customer has no limit to use certain facility. Please don't bother thinking what the point is in setting the customers expiration date to the minimum date while he still will have access to other facilities.
Edited by: Mikail on Dec 28, 2011 1:11 AM
Edited by: Mikail on Dec 28, 2011 1:11 AM