Hi!
I used below script couldn't get requirement result.
declare @Students table (IdStd char(5), LastName nchar(25), IdSpec char(10), StudYear char(7), StdActive int, StudType int, TypeStud int)
insert into @Students(IdStd, LastName, IdSpec, StudYear, StdActive, StudType, TypeStud)values('0001A', 'Pulodov Rustam', '2-240101', '2020/21', 1, 1, 1)
insert into @Students(IdStd, LastName, IdSpec, StudYear, StdActive, StudType, TypeStud)values('0002A', 'Odinaev Komron', '2-240101', '2020/21', 1, 1, 1)
insert into @Students(IdStd, LastName, IdSpec, StudYear, StdActive, StudType, TypeStud)values('0003A', 'Nasimov Shohrukh', '2-240101', '2020/21', 1, 1, 1)
insert into @Students(IdStd, LastName, IdSpec, StudYear, StdActive, StudType, TypeStud)values('0004A', 'Abdurahmonov Muhammad', '2-240101', '2020/21', 1, 1, 1)
insert into @Students(IdStd, LastName, IdSpec, StudYear, StdActive, StudType, TypeStud)values('0005A', 'Davlatov Jumakhon', '2-240101', '2020/21', 1, 1, 1)
insert into @Students(IdStd, LastName, IdSpec, StudYear, StdActive, StudType, TypeStud)values('0006A', 'Davlatov Odinamad', '2-240101', '2020/21', 1, 1, 2)
--select * from @Students
declare @Agrement table (Id int, SMoney money, Corresp money, Spec char(10), StudyYear char(7))
insert into @Agrement(Id, SMoney, Corresp, Spec, StudyYear)values(1, 3000, 2700, '2-240101', '2020/21')
--select * from @Agrement
declare @Cash table (Id int, SMoney money, IdSpec char(10), IdStd char(5))
insert into @Cash(Id, SMoney, IdSpec, IdStd)values(1, 2000, '2-240101', '0001A')
insert into @Cash(Id, SMoney, IdSpec, IdStd)values(2, 3000, '2-240101', '0003A')
insert into @Cash(Id, SMoney, IdSpec, IdStd)values(3, 1500, '2-240101', '0002A')
insert into @Cash(Id, SMoney, IdSpec, IdStd)values(4, 1000, '2-240101', '0001A')
insert into @Cash(Id, SMoney, IdSpec, IdStd)values(5, 1500, '2-240101', '0002A')
insert into @Cash(Id, SMoney, IdSpec, IdStd)values(6, 1500, '2-240101', '0006A')
select distinct st.IdStd, ltrim(rtrim(LastName))LastName, case st.TypeStud when 1 then a.SMoney when 2 then a.Corresp end 'Money', c.SMoney as payed, case st.TypeStud when 1 then a.SMoney - sum(c.SMoney) when 2 then a.Corresp - sum(c.SMoney) end remain from @Students st inner join @Agrement a on st.IdSpec=a.Spec and st.StudYear=a.StudyYear join @Cash c on c.IdStd = st.IdStd where st.StdActive = 1 and StudType = 1 group by st.IdStd, LastName, st.TypeStud, a.SMoney, c.SMoney, a.Corresp
My requirement result:
IdStd
|
LastName
|
Money
|
payed
|
remain
|
0001A
|
Pulodov Rustam
|
3000,00
|
3000,00
|
0,00
|
0002A
|
Odinaev Komron
|
3000,00
|
1500,00
|
1500,00
|
0003A
|
Nasimov Shohrukh
|
3000,00
|
3000,00
|
0,00
|
0006A
|
Davlatov Odinamad
|
2700,00
|
1500,00
|
1200,00
|