with cte1 as(
                    select
                        ROW_NUMBER() OVER (PARTITION BY o.IdOsoba, o.Nazwisko, o.Imie1, o.Plec, s.Nr_Indeksu, wn.KontoWyplat, w.Miesiec
                        ORDER BY o.IdOsoba, o.Nazwisko, o.Imie1, o.Plec, s.Nr_Indeksu, wn.KontoWyplat, w.Miesiec) EntryId,
                        RANK() OVER (ORDER BY o.IdOsoba, o.Nazwisko, o.Imie1, o.Plec, s.Nr_Indeksu, wn.KontoWyplat, w.Miesiec) RowId,
                            o.IdOsoba IdStudent, o.Nazwisko LastName, o.Imie1 FirstName, o.Plec Sex, s.Nr_Indeksu IndexNumber, wn.KontoWyplat BankAccount,
                            w.Miesiec MonthPayment, SUM(w.Kwota) Amount, rs.Nazwa ScholarshipType
                    from Styp_Wyplata w
                    join Styp_StypendiumStudenta sst on sst.IdStypendiumStudenta = w.IdStypendiumStudenta
                    join Styp_Wniosek wn on wn.IdWniosek = sst.IdStypendiumStudenta
                    join Styp_PodkategoriaStypendium ps on ps.IdPodkategoriaStypendium = wn.IdPodkategoriaStypendium
                    join Styp_RodzajStypendium rs on rs.IdRodzajStypendium = ps.IdRodzajStypendium
                    join Student s on s.IdOsoba = wn.IdStudent
                    join Osoba o on o.IdOsoba = s.IdOsoba
                    WHERE EXISTS(SELECT s2.IdWydzial FROM dbo.Student_Status ss
                    JOIN dbo.Studia s2 ON ss.IdStudia = s2.IdStudia
                    WHERE ss.IdOsoba=s.IdOsoba AND s2.IdWydzial IN (1,2,3)) and rs.IdRodzajStypendium in (1,2,3) and
                    (DATEFROMPARTS( w.Rok, w.Miesiec, 1) between  DATEFROMPARTS(2018, 10, 1) and EOMONTH(DATEFROMPARTS(2019, 7, 1)))
                    group by o.IdOsoba, o.Nazwisko, o.Imie1, o.Plec, s.Nr_Indeksu, wn.IdWniosek, wn.KontoWyplat, w.Miesiec, w.Rok, rs.Nazwa

                ),
                no_sum as(
                    select IdStudent, IndexNumber, FirstName, LastName, Sex, MonthPayment, BankAccount,
                    ISNULL((SELECT c2.Amount FROM cte1 c2 WHERE c2.RowId=c.RowId AND c2.ScholarshipType='Socjalne'),0) Social,
                    ISNULL((SELECT c2.Amount FROM cte1 c2 WHERE c2.RowId=c.RowId AND c2.ScholarshipType='Specjalne'),0) Special,
                    ISNULL((SELECT c2.Amount FROM cte1 c2 WHERE c2.RowId=c.RowId AND c2.ScholarshipType='Rektorskie'),0) Deans from cte1 c
                    CROSS APPLY [dbo].[fn_get_StatusStudenta](c.IdStudent) fn
                    WHERE c.EntryId=1 AND fn.IdStatus IN (1, 2, 10) AND
                    EXISTS(SELECT * FROM dbo.Student_Status ss
                    JOIN dbo.Status s ON ss.IdStatus = s.IdStatus
                    WHERE ss.IdOsoba=c.IdStudent AND s.Nazwa IN ('Student', 'Student na wymianie', 'Urlop') AND ss.OdDnia>='2007-10-01' AND ss.OdDnia<=EOMONTH(DATEFROMPARTS(2019, 7, 1)))
                )
                select IdStudent, IndexNumber, FirstName, LastName, Sex, MonthPayment, BankAccount, Social, Special, Deans, Social+Special+Deans Amount
                from no_sum
                order by IdStudent, MonthPayment;