Calculate balance from multiple tables in SQL Server

Last Reply 4 months ago By dharmendr

Posted 4 months ago

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

Posted 4 months ago

Hi PRA,

Refer below query.

SQL

declare @Students table (IdStd char(5), LastName nchar(25), IdSpec char(10), StudYear char(7), StdActive int, StudType int, TypeStud int)
insert into @Students values ('0001A', 'Pulodov Rustam', '2-240101', '2020/21', 1, 1, 1)
insert into @Students values ('0002A', 'Odinaev Komron', '2-240101', '2020/21', 1, 1, 1)
insert into @Students values ('0003A', 'Nasimov Shohrukh', '2-240101', '2020/21', 1, 1, 1)
insert into @Students values ('0004A', 'Abdurahmonov Muhammad', '2-240101', '2020/21', 1, 1, 1)
insert into @Students values ('0005A', 'Davlatov Jumakhon', '2-240101', '2020/21', 1, 1, 1)
insert into @Students 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 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 values (1, 2000, '2-240101', '0001A')
insert into @Cash values (2, 3000, '2-240101', '0003A')
insert into @Cash values (3, 1500, '2-240101', '0002A')
insert into @Cash values (4, 1000, '2-240101', '0001A')
insert into @Cash values (5, 1500, '2-240101', '0002A')
insert into @Cash values (6, 1500, '2-240101', '0006A')
SELECT IdStd,LastName,MAX(Money)Money,SUM(payed)payed,MAX(Money)-SUM(payed) remain
FROM (
	SELECT DISTINCT st.IdStd,LastName,c.SMoney as payed,
	case st.TypeStud when 1 then a.SMoney when 2 then a.Corresp end 'Money'
	FROM @Students st
	INNER JOIN @Agrement a ON st.IdSpec=a.Spec
	INNER JOIN @Cash c ON c.IdStd = st.IdStd
	GROUP BY st.IdStd,LastName,st.TypeStud,a.SMoney,c.SMoney,a.Corresp
) t
GROUP BY IdStd,LastName
ORDER BY IdStd