create procedure filterByYear
as
CREATE TABLE #temp2014 ( Mesec INT, BrojKrediti INT, SumaKrediti DECIMAL(18,2), ProsecnaKamata DECIMAL(18,2))
INSERT INTO #temp2014 ( Mesec, BrojKrediti, SumaKrediti, ProsecnaKamata )
SELECT MONTH(AgreementDate), COUNT(loLoanID), SUM(LoanAmountCurrent), AVG(InterestRateCurrent) FROM loLoan WHERE YEAR(AgreementDate) = 2014 GROUP BY MONTH(AgreementDate)

CREATE TABLE #temp2015 ( Mesec INT, BrojKrediti INT, SumaKrediti DECIMAL(18,2), ProsecnaKamata DECIMAL(18,2))
INSERT INTO #temp2015 ( Mesec, BrojKrediti, SumaKrediti, ProsecnaKamata )
SELECT MONTH(AgreementDate), COUNT(loLoanID), SUM(LoanAmountCurrent), AVG(InterestRateCurrent) FROM loLoan WHERE YEAR(AgreementDate) = 2015 GROUP BY MONTH(AgreementDate)

CREATE TABLE #temp2016 ( Mesec INT, BrojKrediti INT, SumaKrediti DECIMAL(18,2), ProsecnaKamata DECIMAL(18,2))
INSERT INTO #temp2016 ( Mesec, BrojKrediti, SumaKrediti, ProsecnaKamata )
SELECT MONTH(AgreementDate), COUNT(loLoanID), SUM(LoanAmountCurrent), AVG(InterestRateCurrent) FROM loLoan WHERE YEAR(AgreementDate) = 2016 GROUP BY MONTH(AgreementDate)

CREATE TABLE #Final ( Naziv NVARCHAR(MAX), [2014] INT, [2015] INT, [2016] INT )
INSERT INTO #Final ( Naziv )
SELECT 'NumberOfLoans' UNION
SELECT 'AmountLoan' UNION
SELECT 'AvgMargin'

DECLARE @MaxLoans INT, @MaxAmount DECIMAL(18,2), @MaxInterest DECIMAL(18,2)
DECLARE @MonthLoans INT, @MonthAmount INT, @MonthInterest INT

SELECT @MaxLoans = MAX(BrojKrediti), @MaxAmount = MAX(SumaKrediti), @MaxInterest = MAX(ProsecnaKamata) FROM #temp2014
SELECT @MonthLoans = Mesec FROM #temp2014 WHERE BrojKrediti = @MaxLoans
SELECT @MonthAmount = Mesec FROM #temp2014 WHERE SumaKrediti = @MaxAmount
SELECT @MonthInterest = Mesec FROM #temp2014 WHERE ProsecnaKamata = @MaxInterest
UPDATE #Final SET [2014] = @MonthLoans WHERE Naziv = 'NumberOfLoans'
UPDATE #Final SET [2014] = @MonthAmount WHERE Naziv = 'AmountLoan'
UPDATE #Final SET [2014] = @MonthInterest WHERE Naziv = 'AvgMargin'

SELECT @MaxLoans = MAX(BrojKrediti), @MaxAmount = MAX(SumaKrediti), @MaxInterest = MAX(ProsecnaKamata) FROM #temp2015
SELECT @MonthLoans = Mesec FROM #temp2015 WHERE BrojKrediti = @MaxLoans
SELECT @MonthAmount = Mesec FROM #temp2015 WHERE SumaKrediti = @MaxAmount
SELECT @MonthInterest = Mesec FROM #temp2015 WHERE ProsecnaKamata = @MaxInterest
UPDATE #Final SET [2015] = @MonthLoans WHERE Naziv = 'NumberOfLoans'
UPDATE #Final SET [2015] = @MonthAmount WHERE Naziv = 'AmountLoan'
UPDATE #Final SET [2015] = @MonthInterest WHERE Naziv = 'AvgMargin'

SELECT @MaxLoans = MAX(BrojKrediti), @MaxAmount = MAX(SumaKrediti), @MaxInterest = MAX(ProsecnaKamata) FROM #temp2016
SELECT @MonthLoans = Mesec FROM #temp2016 WHERE BrojKrediti = @MaxLoans
SELECT @MonthAmount = Mesec FROM #temp2016 WHERE SumaKrediti = @MaxAmount
SELECT @MonthInterest = Mesec FROM #temp2016 WHERE ProsecnaKamata = @MaxInterest
UPDATE #Final SET [2016] = @MonthLoans WHERE Naziv = 'NumberOfLoans'
UPDATE #Final SET [2016] = @MonthAmount WHERE Naziv = 'AmountLoan'
UPDATE #Final SET [2016] = @MonthInterest WHERE Naziv = 'AvgMargin'

SELECT * FROM #Final

DROP TABLE #temp2014
DROP TABLE #temp2015
DROP TABLE #temp2016
DROP TABLE #Final

go