with cte as (
SELECT ROW_NUMBER() OVER (PARTITION BY (st.IdStd) ORDER BY Years DESC) Row_No, st.IdStd, ltrim(rtrim(st.LastName))LastName, st.IdSpec, st.TypeStud, st.StudType, st.Finance, st.Year, st.IdCourse, st.SdtGroup, st.Param,
case when st.TypeStud = 1 and st.StudType = 0 then a.Money when st.TypeStud = 1 and st.StudType = 1 then a.MNine when st.TypeStud = 1 and st.StudType = 2 then a.Master when st.TypeStud = 1 and st.StudType = 3 then a.Phd when st.TypeStud = 3 and st.StudType = 0 then a.Corresp when st.TypeStud = 2 and st.StudType = 3 then a.Corresp when st.TypeStud = 2 and st.StudType = 0 then a.Corresp else 0 end'Money',
case when st.TypeStud = 1 and st.StudType = 0 then ISNULL(SUM(c.Money),0) when st.TypeStud = 1 and st.StudType = 1 then ISNULL(SUM(c.Money),0) when st.TypeStud = 1 and st.StudType = 2 then ISNULL(SUM(c.Money),0) when st.TypeStud = 1 and st.StudType = 3 then ISNULL(SUM(c.Money),0) when st.TypeStud = 3 and st.StudType = 0 then ISNULL(SUM(c.Money),0) when st.TypeStud = 2 and st.StudType = 3 then ISNULL(SUM(c.Money),0) when st.TypeStud = 2 and st.StudType = 0 then ISNULL(SUM(c.Money),0) else 0 end'Payed',
case when st.TypeStud = 1 and st.StudType = 0 then ISNULL((a.Money - SUM(c.Money)),0) when st.TypeStud = 1 and st.StudType = 1 then ISNULL((a.MNine - SUM(c.Money)),0) when st.TypeStud = 1 and st.StudType = 2 then ISNULL((a.Master - SUM(c.Money)),0) when st.TypeStud = 1 and st.StudType = 3 then ISNULL((a.Phd - SUM(c.Money)),0) when st.TypeStud = 3 and st.StudType = 0 then ISNULL((a.Corresp - SUM(c.Money)),0) when st.TypeStud = 2 and st.StudType = 3 then ISNULL((a.Corresp - SUM(c.Money)),0) when st.TypeStud = 2 and st.StudType = 0 then ISNULL((a.Corresp - SUM(c.Money)),0) else 0 end'Remain',
ISNULL(c.Years,'')Years
FROM cash c right outer join students st on c.IdStd = st.IdStd inner join agrem a on st.Year = a.Year and st.IdSpec = a.Spec where st.StdActive = 1 and c.PayType = 0 group by c.Years, st.Year, st.IdStd, st.LastName, st.IdSpec, st.TypeStud, st.StudType, st.Finance, st.IdCourse, st.SdtGroup, c.PayType, st.Param, a.Money, a.Corresp, a.MNine, a.Master, a.Phd
),
outercte as
(
select e.IdStd, ltrim(rtrim(e.LastName))LastName, e.Money, e.payed, e.remain, e.Years
from CTE e where e.idspec= '2790133' and idcourse = '1' and sdtgroup = '103' and param = '279013304' and e.Years IN (SELECT DISTINCT MAX(Years) FROM cash GROUP BY IdStd,course)
union
select r.IdStd, ltrim(rtrim(r.LastName))LastName, case r.Finance when 1 then case r.StudType when 0 then case r.TypeStud when 1 then a.Money when 2 then a.Corresp when 3 then a.Corresp end when 1 then a.MNine when 2 then a.Master when 3 then a.Phd end else 0 end'Money', NULL, NULL, case r.Finance when 1 then r.Year else '' end Years
from students r inner join agrem a on a.Spec = r.IdSpec and r.Year = a.Year and r.idspec= '2790133' and idcourse = '1' and sdtgroup = '103' and param = '279013304' where r.IdStd not in (select IdStd from CTE) and r.StdActive = 1 group by r.Year, r.Year, r.IdStd, r.IdCourse, r.IdSpec, r.LastName, r.Param, r.SdtGroup, r.StudType, r.TypeStud, r.Finance, a.Money, a.Corresp, a.MNine, a.Master, a.Phd
)
select t.IdStd, ltrim(rtrim(t.LastName))LastName, t.Money, t.payed, t.remain, t.Years
from outercte t order by t.LastName
