Select all records if balance greater than zero in SQL Server

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on Sep 25, 2018 11:29 PM
Forum: SQL Server
Answer: 1
Views: 3628

AdmissionNo	NetBal	Paid	RemBal
R-01	700	400	300
R-02	700	300	400
R-02	400	400	0
R-03	700	600	100
R-04	700	700	0
R-05	700	700	0
R-06	700	700	0

Required Output

AdmissionNo	NetBal	Paid	RemBal
R-01	700	400	300
R-03	700	600	100

and my query is

SELECT fc.AdmissionNo,ReferenceNo,SName,FName,ParentID,AcademicName,ClassName,SectionName,Year,SUM(Recievable) as [Recievable],SUM(NetBal) as [NetBal],SUM(Paid) as [Paid],SUM(RemBal) as [RemBal],FPhone 
FROM tblFeesCollection as fc inner join tblStdReg as sr on sr.AdmissionNo=fc.AdmissionNo inner join tblAcademicYear as ay on sr.YearID=ay.YearID inner join tblDefClass as dc on dc.ClassID=sr.ClassID 
inner join tblDefSection as ds on ds.SectionID=sr.SectionID 
where Year='2018' and RemBal != 0 or Paid=0 and 
FeeID IN (SELECT MAX(FeeID) FROM tblFeesCollection GROUP BY AdmissionNo ) 
GROUP BY fc.AdmissionNo,SName,FName,ParentID,Year,ReferenceNo,AcademicName,ClassName,SectionName,FPhone

pandeyis...

0 2

Reply
ANSWER
User: pandeyism
Replied: on Sep 26, 2018 06:41 AM
Report

Hi smile,

Please refer below query.

SQL

DECLARE @TestDemo AS TABLE(AdmissionNo VARCHAR(5), NetBal INT, Paid INT, RemBal INT)
INSERT INTO @TestDemo VALUES('R-01',700,400,300)
INSERT INTO @TestDemo VALUES('R-02',700,300	,400)
INSERT INTO @TestDemo VALUES('R-02',400,400,0)
INSERT INTO @TestDemo VALUES('R-03',700	,600 ,100)
INSERT INTO @TestDemo VALUES('R-04',700	,700,0)
INSERT INTO @TestDemo VALUES('R-05',700,700,0)
INSERT INTO @TestDemo VALUES('R-06',700,700,0)
SELECT * FROM @TestDemo
WHERE RemBal !=0 AND AdmissionNo NOT IN (SELECT AdmissionNo FROM @TestDemo WHERE RemBal = 0)

Screenshot

AdmissionNo	NetBal	Paid	RemBal
R-01	700	400	300
R-03	700	600	100

Select all records if balance greater than zero in SQL Server

Company

Explore

Follow us

Contact Us