declare
@begin_date datetime,
@end_date datetime
set @begin_date =:begin_date
set @end_date =:end_date
set @begin_date=convert(varchar(10),@begin_date,120) +' 00:00:00'
set @end_date=convert(varchar(10),@end_date,120) +' 23:59:59'

select visit_dept,convert(varchar(10),visit_date,120) date,name,clinic_type into #visit_table from mz_visit_table
where datediff(mi,mz_visit_table.visit_date ,@begin_date )<=0
and datediff(mi, mz_visit_table.visit_date , @end_date )>=0
group by visit_dept,convert(varchar(10),visit_date,120),name,clinic_type


select sum(a) a,sum(b) b,sum(c) c,sum(d) d,sum(e) e,sum(f) f,sum(g) g,sum(h) h,sum(i) i,sum(j) j,sum(k) k ,sum(m) m,sum(l) l,sum(n) n ,sum(o) o,sum(p) p ,sum(q) q from(select
(select count(*) from #visit_table a where a.visit_dept in

('1010100','1010101','1010200','1010201','1010300','1010301','1010500','1010501',
'1010600','1010601','1010400','1010401','1010700','1010701') and isnull(clinic_type,'01') in

('01','05','06','11','14') )a ,
(select count(*) from #visit_table a where a.visit_dept in

('1020100','1020101','1020300','1020301','1020500','1020501','1020400','1020401',
'1020600','1020601','1020700','1020701','1020800','1020801' )and isnull(clinic_type,'01') in

('01','05','06','11','14') )b,
(select count(*) from #visit_table a where a.visit_dept in ('1040000','1040001','1030000','1030001')and

isnull(clinic_type,'01') in ('01','05','06','11','14') )c,
(select count(*) from #visit_table a where a.visit_dept in ('1060300','1060301')and

isnull(clinic_type,'01') in ('01','05','06','11','14'))d,
(select count(*) from #visit_table a where a.visit_dept in ('1050000','1050001')and

isnull(clinic_type,'01') in ('01','05','06','11','14'))e,

(select count(*) from #visit_table a where a.visit_dept in ('1010900','1010901')and

isnull(clinic_type,'01') in ('01','05','06','11','14'))f,
(select count(*) from #visit_table a where a.visit_dept in ('1070000','1070001')and

isnull(clinic_type,'01') in ('01','05','06','11','14'))g,
(select count(*) from #visit_table a where a.visit_dept in ('1060200','1060201')and

isnull(clinic_type,'01') in ('01','05','06','11','14'))h,
(select count(*) from #visit_table a where a.visit_dept in

('1080000','1080001','1010800','1010801')and isnull(clinic_type,'01') in ('01','05','06','11','14'))i,
(select count(*) from #visit_table a where a.visit_dept in ('1120000')and isnull(clinic_type,'01') in

('01','05','06','11','14'))j,
(select count(*) from #visit_table a where a.visit_dept in ('1060100','1060101')and isnull(clinic_type,'01') in

('01','05','06','11','14'))o,
(select count(*) from #visit_table a where a.visit_dept not in ('1010100','1010101','1010200','1010201','1010300','1010301','1010500','1010501',
'1010600','1010601','1010400','1010401','1010700','1010701','1020100','1020101','1020300','1020301','1020500','1020501','1020400','1020401',
'1020600','1020601','1020700','1020701','1020800','1020801','1040000','1040001','1030000','1030001','1060300','1060301','1050000','1050001','1010900','1010901','1070000','1070001','1060200','1060201','1080000','1080001','1010800','1010801','1120000','1060100','1060101')and isnull(clinic_type,'01') in

('01','05','06','11','14'))q,
0 k,
0 m,
0 l,
0 n,
0 p
union all
select 0,0,0,0,0,0,0,0,0,0,0,0,
(select count(*) from #visit_table a where isnull(clinic_type,'01') in('03','04','07','08','09','10')) k,
(select count(*) from #visit_table a where isnull(clinic_type,'01') in('12'))m,
(select count(*) from #visit_table a where isnull(clinic_type,'01') in('02','16'))l,
(select count(*) from #visit_table a where isnull(clinic_type,'01') in('13','15'))n,
(select count(*) from #visit_table p)
)aa
drop table #visit_table