SQL Two Column Comparing Error

Last Reply 15 days ago By pandeyism

Posted 15 days ago

Please am not getting the desire result using the following SQL statments  

SELECT distinct RTRIM(Sn),RTRIM(Name),RTRIM(Type),SUM(TotalPaid) as TotalPaid ,month, TotalFee , Status from PaymentRpt Group by Sn,Name, Type, Month, TotalFee, Status order by 2
update FeePaymentRpt set Status = 'paid' where TotalFee = TotalPaid
update PaymentRpt set Status = 'owing' where TotalFee != TotalPaid

This is the result i get

 

Sn Name Type TotalPaid Month Totalfee Status
1 Agnes K.P.D 3500 July 3500 owing
2 Agnes K.P.D 14900 June 14900 paid
3 Rita K.P.D 3500 July 3500 owing
4 Rita K.P.D 14900 June 14900 paid
5 Rita K.P.D 1000 August 3500 owing

Row 1 and Row 3 has totalpaid to be 3500 which is equal to totalfee of 3500 but with my statment it show owing instead of paid am expecting the Result to be like this

Sn Name Type TotalPaid Month Totalfee Status
1 Agnes K.P.D 3500 July 3500 paid
2 Agnes K.P.D 14900 June 14900 paid
3 Rita K.P.D 3500 July 3500 paid
4 Rita K.P.D 14900 June 14900 paid
5 Rita K.P.D 1000 August 3500 owing

i have  tried update FeePaymentRpt set Status = 'owing' where TotalFee > TotalPaidnot success

 

 

You are viewing reply posted by: pandeyism 15 days ago.
Posted 15 days ago

Hi rickabbam,

Please refer below Query.

SQL

DECLARE @FeePaymentRpt AS TABLE(Sn INT, Name VARCHAR(20), Type VARCHAR(20), TotalPaid INT, Month VARCHAR(20), TotalFee INT, [Status] VARCHAR(20))
INSERT INTO @FeePaymentRpt VALUES(1, 'Agnes' ,'K.P.D', 3500 ,'July', 3500, 'owing')
INSERT INTO @FeePaymentRpt VALUES(2 ,'Agnes' ,'K.P.D' , 14900, 'June' ,14900 ,'paid')
INSERT INTO @FeePaymentRpt VALUES(3, 'Rita' , 'K.P.D' ,	3500 , 'July' ,	3500 , 'owing')
INSERT INTO @FeePaymentRpt VALUES(4 ,'Rita' , 'K.P.D' ,	14900,  'June' ,	14900 ,'paid')
INSERT INTO @FeePaymentRpt VALUES(5 ,'Rita' , 'K.P.D' ,	1000  ,'August', 	3500 , 'owing')

UPDATE @FeePaymentRpt SET [Status] = 'paid' WHERE TotalFee = TotalPaid
UPDATE @FeePaymentRpt SET [Status] = 'owing' WHERE TotalFee != TotalPaid
SELECT distinct RTRIM(Sn) as Sn,RTRIM(Name) AS Name,RTRIM(Type) AS Type,SUM(TotalPaid) Total,month, TotalFee , Status from @FeePaymentRpt
Group by Sn,Name, Type, Month, TotalFee, Status order by 2

Output

Sn Name Type Total month TotalFee Status
1 Agnes K.P.D 3500 July 3500 paid
2 Agnes K.P.D 14900 June 14900 paid
3 Rita K.P.D 3500 July 3500 paid
4 Rita K.P.D 14900 June 14900 paid
5 Rita K.P.D 1000 August 3500 owing