How to get a distinct count based on date

I have a requirement to get a distinct count of people we offered a job, problem is since we can offer multiple jobs on potential candidate , when i write my query its counting multiple offers. Requirement is to count only the first offer, any subsequent offer should not count. any suggestions on this?

Solution

You can use this COUNT(DISTINCT...) like the following :

SELECT a.p_id, b.p_name, c.p_desc, 
       COUNT(DISTINCT CASE WHEN a.date BETWEEN TRUNC(ADD_MONTHS(LAST_DAY(sysdate),-4) + 1) AND 
                                    ADD_MONTHS(LAST_DAY(TO_DATE(sysdate)),-1) ...