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

You are viewing reply posted by: dharmendr 4 months ago.
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