--I need to get background checks that are going to expire in 30 days from the current day, but don't already have a newer one of that type.

SELECT bg_check_id ,
    provider_id ,
    check_type ,
    upload_id ,
    who_approved ,
    notes ,
    approval_date ,
    started_date ,
    status FROM  (SELECT *
        FROM providers_background_checks
        WHERE status = 'approved'
        AND approval_date >= '2011-09-13 00:00:00.0'
        ORDER BY approval_date  desc) as subq
    WHERE approval_date >= '2011-09-13 00:00:00.0'
    AND approval_date <= '2011-09-13 23:59:59.9999'
    AND (check_type, provider_id) NOT IN(SELECT check_type, provider_id
        FROM providers_background_checks
        WHERE approval_date > '2011-09-13 00:00:00.0')