Sub GetReport(Chair As Integer, EducationYear As Integer)
    Dim cn As New ADODB.Connection
    cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
    & "Persist Security Info=False;Initial Catalog=IISU_RELEASE;Data Source=SQL1"
    cn.Open
    
    Dim cmd As ADODB.Command
    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "execute [IISU_RELEASE].[Curriculum].[GetChairReport] @Chair =" _
    & Chair & ", @EducationYear = " & EducationYear
    
    Dim rs As New ADODB.Recordset
    rs.Open cmd
    rs.MoveFirst
    
    Cells( 1 ,  1 ) = "Группа"
    Cells( 1 ,  2 ) = "Дисциплина"
    Cells( 1 ,  3 ) = "Лекции"
    Cells( 1 ,  4 ) = "Семинары"
    Cells( 1 ,  5 ) = "Консультации"
    Cells( 1 ,  6 ) = "Экзам. конс."
    Cells( 1 ,  7 ) = "КП"
    Cells( 1 ,  8 ) = "КР"
    Cells( 1 ,  9 ) = "ИРЗ"
    Cells( 1 ,  10 ) = "Коллоквиум"
    Cells( 1 ,  11 ) = "Зачет"
    Cells( 1 ,  12 ) = "Экзамен"
    Cells( 1 ,  13 ) = "Аспир. магистр."
    Cells( 1 ,  14 ) = "ГАК"
    Cells( 1 ,  15 ) = "Практика"
    Cells( 1 ,  16 ) = "ВКР"
    
    q =  2 
    Do Until rs.EOF
        Cells(q,  1 ) = rs.Fields( 0 )
        Cells(q,  2 ) = rs.Fields( 1 )
        Cells(q,  3 ) = rs.Fields( 2 )
        Cells(q,  4 ) = rs.Fields( 3 )
        Cells(q,  5 ) = rs.Fields( 4 )
        Cells(q,  6 ) = rs.Fields( 5 )
        Cells(q,  7 ) = rs.Fields( 6 )
        Cells(q,  8 ) = rs.Fields( 7 )
        Cells(q,  9 ) = rs.Fields( 8 )
        Cells(q,  10 ) = rs.Fields( 9 )
        Cells(q,  11 ) = rs.Fields( 10 )
        Cells(q,  12 ) = rs.Fields( 11 )
        Cells(q,  13 ) = rs.Fields( 12 )
        Cells(q,  14 ) = rs.Fields( 13 )
        Cells(q,  15 ) = rs.Fields( 14 )
        Cells(q,  16 ) = rs.Fields( 15 )
        q = q +  1 
        rs.MoveNext
    Loop
End Sub

rs.MoveFirst

Public Function OpenDisconnectedRecordsetFromCommand(ByRef cmd as ADODB.Command) as ADODB.Recordset
Dim rs ADODB.Recordset
  Set rs = New ADODB.Recordset
  rs.CursorLocation =  3  ' adUseClient
  rs.Open cmd
  rs.ActiveConnection = Nothing
  Set OpenDisconnectedRecordsetFromCommand = rs
End Function

rs.ActiveConnection = Nothing

    Do While rs.State =  0 
        rs.NextRecordset
    Loop

USE [IISU_RELEASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [Curriculum].[GetChairReport]
	@Chair INT, -- Кафедра, по которой нужно получить отчет
	@EducationYear INT -- Год обучения

AS
SET NOCOUNT ON

EXECUTE [IISU_RELEASE].[Curriculum].[PGetChairTeachingLoad] 
   @ChairID = @Chair
  ,@AcademicYearNumber = @EducationYear

DECLARE @TeachingLoadCalculationID INT

CREATE TABLE #TeachingLoadCalculationList
(
	ChairTeachingLoadCalculationID INT PRIMARY KEY,
	ChairID INT,
	AcademicYearNumber INT,
	CalculationDate DATETIME,
	DisciplineTeachingLoadHourQuantity DECIMAL( 12 , 2 ),
	OtherTeachingLoadHourQuantity DECIMAL( 12 , 2 ),
	IsCurrent BIT,
	IsApproved BIT,
	IsDistributed BIT
)

INSERT INTO 
	#TeachingLoadCalculationList
	EXECUTE [IISU_RELEASE].[Curriculum].[PGetChairTeachingLoadCalculationList] 
	   @ChairID = @Chair
	  ,@AcademicYearNumber = @EducationYear

SELECT 
	@TeachingLoadCalculationID = ChairTeachingLoadCalculationID
FROM
	#TeachingLoadCalculationList

DROP TABLE #TeachingLoadCalculationList


CREATE TABLE #FullReport
(
	ChairTeachingLoadCalculationItemID INT PRIMARY KEY,
	ChairTeachingLoadCalculationID INT,
	TeachingLoadObjectID INT ,
	TeachingLoadObjectName VARCHAR( 200 ),
	TeachingLoadObjectShortName VARCHAR( 200 ),
	PlannedStudentGroupID INT,
	PlannedStudentGroupName VARCHAR( 100 ),
	StreamID INT,
	StreamName VARCHAR( 100 ),
	PersonQuantity INT,
	SubGroupQuantity DECIMAL( 4 , 2 ),
	CurriculumDisciplineID INT,
	DisciplineFullName VARCHAR( 200 ),
	CurriculumID INT,
	CurriculumCode VARCHAR( 100 ),
	EducationFormID INT,
	EducationFormName VARCHAR( 100 ),
	TeachingLoadHourQuantity DECIMAL( 6 , 2 ),
	EducationYearID INT,
	EducationYearNumber INT,
	TermID INT,
	TermNumber INT,
	[TeachingLoadObjectCalculationTypeID] INT
)

INSERT INTO
	#FullReport
EXEC [IISU_RELEASE].[Curriculum].[PGetChairTeachingLoadCalculationItemList]
	@ChairTeachingLoadCalculationID = @TeachingLoadCalculationID


-- Внешний запрос: Группирует и суммирует данные полученные во внутреннем запросе
SELECT
	PlannedStudentGroupName,
	DisciplineFullName,
	SUM(Lectures) as 'Lectures',
	SUM(Seminars) as 'Seminars',
	SUM(Consultation) as 'Consultation',
	SUM(ExamConsultation) as 'ExamConsultation',
	SUM(CourseProject) as 'CourseProject',
	SUM(ControlWork) as 'ControlWork',
	SUM(IRZ) as 'IRZ',
	SUM(Colloquium) as 'Colloquium',
	SUM(Offset) as 'Offset',
	SUM(Exam) as 'Exam',
	SUM(GraduateManage) as 'GraduateManage',
	SUM(GAK) as 'GAK',
	SUM(Practice) as 'Practice',
	SUM(VKR) as 'VKR'
FROM
	(
	-- Внутренний запрос: формирует набор записей, отображающий данные о количестве часов
	-- по конкретному виду работ, в каждой группе и по каждой дисциплине
	SELECT
		PlannedStudentGroupID,
		PlannedStudentGroupName,
		CurriculumDisciplineID,
		DisciplineFullName,
		CASE TeachingLoadObjectID
			WHEN  30  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'Lectures',

		CASE TeachingLoadObjectID
			WHEN  1  THEN TeachingLoadHourQuantity
			WHEN  2  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'Seminars',

		CASE TeachingLoadObjectID
			WHEN  54  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'Consultation',

		CASE TeachingLoadObjectID
			WHEN  53  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'ExamConsultation',

		CASE TeachingLoadObjectID
			WHEN  7  THEN TeachingLoadHourQuantity
			WHEN  8  THEN TeachingLoadHourQuantity
			WHEN  9  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'CourseProject',

		CASE TeachingLoadObjectID
			WHEN  51  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'ControlWork',

		CASE TeachingLoadObjectID
			WHEN  10  THEN TeachingLoadHourQuantity
			WHEN  11  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'IRZ',

		CASE TeachingLoadObjectID
			WHEN  52  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'Colloquium',

		CASE TeachingLoadObjectID
			WHEN  3  THEN TeachingLoadHourQuantity
			WHEN  4  THEN TeachingLoadHourQuantity
			WHEN  41  THEN TeachingLoadHourQuantity
			WHEN  56  THEN TeachingLoadHourQuantity
			WHEN  57  THEN TeachingLoadHourQuantity
			WHEN  60  THEN TeachingLoadHourQuantity
			WHEN  61  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'Offset',

		CASE TeachingLoadObjectID
			WHEN  5  THEN TeachingLoadHourQuantity
			WHEN  6  THEN TeachingLoadHourQuantity
			WHEN  58  THEN TeachingLoadHourQuantity
			WHEN  59  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'Exam',

		CASE TeachingLoadObjectID
			WHEN  21  THEN TeachingLoadHourQuantity
			WHEN  22  THEN TeachingLoadHourQuantity
			WHEN  24  THEN TeachingLoadHourQuantity
			WHEN  25  THEN TeachingLoadHourQuantity
			WHEN  26  THEN TeachingLoadHourQuantity
			WHEN  27  THEN TeachingLoadHourQuantity
			WHEN  28  THEN TeachingLoadHourQuantity
			WHEN  29  THEN TeachingLoadHourQuantity
			WHEN  34  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'GraduateManage',

		CASE TeachingLoadObjectID
			WHEN  12  THEN TeachingLoadHourQuantity
			WHEN  13  THEN TeachingLoadHourQuantity
			WHEN  14  THEN TeachingLoadHourQuantity
			WHEN  23  THEN TeachingLoadHourQuantity
			WHEN  35  THEN TeachingLoadHourQuantity
			WHEN  39  THEN TeachingLoadHourQuantity
			WHEN  50  THEN TeachingLoadHourQuantity
			WHEN  55  THEN TeachingLoadHourQuantity
			WHEN  64  THEN TeachingLoadHourQuantity
			WHEN  65  THEN TeachingLoadHourQuantity
			WHEN  85  THEN TeachingLoadHourQuantity
			WHEN  87  THEN TeachingLoadHourQuantity
			WHEN  89  THEN TeachingLoadHourQuantity
			WHEN  90  THEN TeachingLoadHourQuantity
			WHEN  91  THEN TeachingLoadHourQuantity
			WHEN  92  THEN TeachingLoadHourQuantity
			WHEN  93  THEN TeachingLoadHourQuantity
			WHEN  94  THEN TeachingLoadHourQuantity
			WHEN  95  THEN TeachingLoadHourQuantity
			WHEN  96  THEN TeachingLoadHourQuantity
			WHEN  97  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'GAK',

		CASE TeachingLoadObjectID
			WHEN  16  THEN TeachingLoadHourQuantity
			WHEN  17  THEN TeachingLoadHourQuantity
			WHEN  31  THEN TeachingLoadHourQuantity
			WHEN  32  THEN TeachingLoadHourQuantity
			WHEN  33  THEN TeachingLoadHourQuantity
			WHEN  36  THEN TeachingLoadHourQuantity
			WHEN  37  THEN TeachingLoadHourQuantity
			WHEN  38  THEN TeachingLoadHourQuantity
			WHEN  48  THEN TeachingLoadHourQuantity
			WHEN  49  THEN TeachingLoadHourQuantity
			WHEN  66  THEN TeachingLoadHourQuantity
			WHEN  67  THEN TeachingLoadHourQuantity
			WHEN  80  THEN TeachingLoadHourQuantity
			WHEN  81  THEN TeachingLoadHourQuantity
			WHEN  82  THEN TeachingLoadHourQuantity
			WHEN  83  THEN TeachingLoadHourQuantity
			WHEN  84  THEN TeachingLoadHourQuantity
			WHEN  86  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'Practice',

		CASE TeachingLoadObjectID
			WHEN  18  THEN TeachingLoadHourQuantity
			WHEN  19  THEN TeachingLoadHourQuantity
			WHEN  20  THEN TeachingLoadHourQuantity
			WHEN  40  THEN TeachingLoadHourQuantity
			WHEN  42  THEN TeachingLoadHourQuantity
			WHEN  44  THEN TeachingLoadHourQuantity
			WHEN  45  THEN TeachingLoadHourQuantity
			WHEN  62  THEN TeachingLoadHourQuantity
			WHEN  63  THEN TeachingLoadHourQuantity
			WHEN  88  THEN TeachingLoadHourQuantity
			ELSE  0 
		END AS 'VKR'

	FROM #FullReport
	) AS TMP
GROUP BY PlannedStudentGroupName, DisciplineFullName
ORDER BY PlannedStudentGroupName
	
DROP TABLE #FullReport

    Dim cmd As ADODB.Command
    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "execute [IISU_RELEASE].[Curriculum].[GetChairReport] @Chair =" _
    & Chair & ", @EducationYear = " & EducationYear
    
    Dim rs As New ADODB.Recordset
    rs.Open cmd
    rs.MoveFirst

    Dim rs As New ADODB.Recordset
    set rs=cn.Execute "execute [IISU_RELEASE].[Curriculum].[GetChairReport] @Chair =" _
    & Chair & ", @EducationYear = " & EducationYear
    

    Do While rs.State =  0 
        rs.NextRecordset
    Loop