Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
How to Create a YTD Query
Message
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
00638903
Message ID:
00638915
Views:
19
Well I figured it out, but I'm not sure if it is the best way (I'm guessing not). Anyway, if any one has suggestions, on design or suggested indexs that might be required on this query, I'm wide open.
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
Previous
Reply
Map
View

Click here to load this message in the networking platform