
select A.visitno,A.visitdate from visit A 
INNER JOIN (

select visitno from visitdrug 
INNER JOIN cdrug ON cdrug.drugcode = visitdrug.drugcode
  WHERE
left(cdrug.drugcode24,19) NOT IN ('2011101000199999203', '2011203200377262217', '2020301200199999203') group by visitno
) B 
on a.visitno=b.visitno


INNER JOIN (
SELECT visitno FROM visitanc GROUP BY visitno ) C
on A.visitno=C.visitno 
where A.visitdate BETWEEN '2014-10-01' and '2015-09-30'
