CREATE PROCEDURE ytd_ProcHis @tiYear int, @tiDept int as declare @tiPrevious int set @tiPrevious = @tiYear-1 select distinct mp.description, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=1) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiYear GROUP BY mp1.dcode),0) as mon1, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=2) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiYear GROUP BY mp1.dcode),0) as mon2, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=3) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiYear GROUP BY mp1.dcode),0) as mon3, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=4) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiYear GROUP BY mp1.dcode),0) as mon4, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=5) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiYear GROUP BY mp1.dcode),0) as mon5, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=6) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiYear GROUP BY mp1.dcode),0) as mon6, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=7) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiYear GROUP BY mp1.dcode),0) as mon7, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=8) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiYear GROUP BY mp1.dcode),0) as mon8, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=9) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiYear GROUP BY mp1.dcode),0) as mon9, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=10) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiYear GROUP BY mp1.dcode),0) as mon10, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=11) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiYear GROUP BY mp1.dcode),0) as mon11, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=12) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiYear GROUP BY mp1.dcode),0) as mon12, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=1) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiPrevious GROUP BY mp1.dcode),0) as pmon1, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=2) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiPrevious GROUP BY mp1.dcode),0) as pmon2, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=3) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiPrevious GROUP BY mp1.dcode),0) as pmon3, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=4) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiPrevious GROUP BY mp1.dcode),0) as pmon4, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=5) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiPrevious GROUP BY mp1.dcode),0) as pmon5, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=6) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiPrevious GROUP BY mp1.dcode),0) as pmon6, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=7) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiPrevious GROUP BY mp1.dcode),0) as pmon7, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=8) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiPrevious GROUP BY mp1.dcode),0) as pmon8, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=9) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiPrevious GROUP BY mp1.dcode),0) as pmon9, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=10) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiPrevious GROUP BY mp1.dcode),0) as pmon10, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=11) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiPrevious GROUP BY mp1.dcode),0) as pmon11, isnull((select COUNT(mp1.dcode) FROM dbo.meetings m1 INNER JOIN meetProc mp1 ON m1.meetingnumber = mp1.meetingnumber where (datepart(mm,m1.begintime)=12) and mp1.dcode=mp.dcode and datepart(yyyy,m1.begintime)=@tiPrevious GROUP BY mp1.dcode),0) as pmon12 from dbo.meetings m inner join dbo.meetproc mp on m.meetingnumber = mp.meetingnumber where datepart(yyyy,m.begintime)=@tiYear