SELECT CM."Ref_Number", CM."First_Name", CM."Last_Name", CM."Co_Name", CM."Last_Visit",

(select count(AM."Ref_Number") from "Animal_Main" AM where AM."Owner" = CM."Ref_Number" and AM."Status" = 'A') as "Number_Animals"

from "Client_Main" CM

where CM."Ref_Number" > 10000001000

and CM."Status" ='A'

and (CM."Last_Visit" < :Date/*Last Visit*/ or CM."Last_Visit" is null) 

and not exists (select * from "Bill_Main" BM

    where CM."Ref_Number" = BM."Cln_Number"

    and BM."Dis_Date" >= :Date/*Last Visit*/)

and exists (select * from "Bill_Main" BM

    where CM."Ref_Number" = BM."Cln_Number"

    and BM."Dis_Date" < :Date/*Last Visit*/) 

and not exists (select DM."Ref_Number" from "Diary_Main" DM

    where CM."Ref_Number" = DM."Cln_Num"

    and DM."Date" >= current_date)

and not exists (select SM."Ref_Number" from "Staff_Main" SM

    where CM."Ref_Number" = SM."Cln_Link"

    and SM."Status" = 'A')

order by CM."Ref_Number"