How to get outstanding from two tables in sql

Question

I have two Tables:

1. Sells
2. List item

Receipt

I have written Query but some of amount is added as it find in 2nd table

1) Sells Table:

Create Table Sells(
        ID int,
        Invone varchar (30),
        InvDate DateTime ,
        Year varchar (15),
        Type varchar (10) ,
        Terms varchar (20) ,
        Days int ,
        DueDate DateTime,
        Pieces int ,
        Carats Real ,
        Price Real ,
        GrossAmt Real ,
        Discount1 Real ,
        DisAmt1 Real ,
        Discount2 Real,
        DisAmt2 Real ,
        FinalAmt Real,
        Party varchar (100), 
        Party_ID int,
        Person varchar (100),
        Shape varchar (30),
        Quality varchar (30),
        StockId int,
        SalesExe varchar (50),
        Remarks varchar (200), 
        CreatedDate DateTime,
        ModifiedDate DateTime,
        Username varchar (50))

2) Receipt Table:

Create Table Receipt (
        ID int ,
        Invone varchar (30),
        RDate DateTime,
        Year varchar (15),
        Type varchar (10),
        HKD Real,
        Rate Real,
        ShortAmt Real,
        RecievedAmt Real,
        TotalRecievedAmt Real,
        FinalAmt Real,
        Party varchar (100),
        Party_ID int,
        Remarks varchar (200),
        CreatedDate DateTime,
        ModifiedDate DateTime,
        Username varchar (50)
        )

This is my Query:

    select s.Invone,s.InvDate,s.Type,s.year,s.Terms,s.DueDate,s.Party,s.Party_ID,sum(s.Pieces) AS TotalPieces,
     round(sum(s.Carats), 3) AS TotalCarats, round(sum(s.GrossAmt), 2) AS TotalGrossAmt, round(sum(s.DisAmt1), 2) AS TotalDis1,
      round(sum(s.DisAmt2), 2) AS TotalDis2, round(sum(s.FinalAmt), 2) AS TotalFinalAmt, isnull(round(sum(HKD), 2), 0) AS TotalHKD,
       isnull(round(rate, 2), 0) AS Rate, isnull(round(sum(ShortAmt), 2), 0) AS TotalShort,isnull(round(sum(RecievedAmt), 2), 0) AS RecievedAmt,
        isnull(round(sum(TotalRecievedAmt), 2), 0) AS TotalRecievedAmt, round(sum(s.FinalAmt) - isnull(sum(TotalRecievedAmt), 0), 2) AS Balance

          from Sells s
     left join Recipt r on s.Invone = r.Invone WHERE s.Party = 'Buyer'
     group by s.Invone,s.InvDate,s.Party,s.Party_ID,s.Type,s.Year,s.Terms,s.DueDate,rate
     having  (sum(s.FinalAmt) - sum(isnull(TotalRecievedAmt, 0)))  0 
     order by s.InvDate, s.Invone

This is output i am getting 1:Image of Output

I required Output Like Invone Should Come Once Only. and TotalPeices, TotalCarats,TotalGrossAmt,TotalDis1,TotalDis2,TotalFinalAmt should not get added 2 times or 3 times.

if i have Invone no. 15 two times in sells table and three Receipt of 15 no. in part payment. then in output TotalPeices, TotalCarats,TotalGrossAmt,TotalDis1,TotalDis2,TotalFinalAmt should not added 3 times. it should show sum of TotalPeices, TotalCarats,TotalGrossAmt,TotalDis1,TotalDis2,TotalFinalAmt from sells table only. from Receipt Table it should show sum of TotalHKD, TotalShort, RecievedAmt,TotalRecievedAmt

Answer 1

I got Solution

    select S.Invone,S.InvDate,S.Type,S.Year,S.Terms,S.DueDate,S.Party,S.Party_ID,S.TotalPieces,S.TotalCarats,S.TotalGrossAmt,S.TotalDis1,S.TotalDis2,S.TotalFinalAmt,isnull(R.TotalHKD,0) AS TotalHKD,isnull(R.Rate,0) AS Rate,isnull(R.TotalShort,0) AS TotalShort,isnull(R.RecievedAmt,0) AS RecievedAmt,isnull(R.TotalRecievedAmt,0) AS TotalRecievedAmt,(round(S.TotalFinalAmt - isnull(R.TotalRecievedAmt, 0), 2)) AS Balance from 
    (select Invone, isnull(round(sum(HKD), 2), 0) AS TotalHKD,isnull(round(rate, 2), 0) AS Rate, isnull(round(sum(ShortAmt), 2), 0) AS TotalShort,isnull(round(sum(RecievedAmt), 2), 0) AS RecievedAmt,
        isnull(round(sum(TotalRecievedAmt), 2), 0) AS TotalRecievedAmt from Recipt 
    group by Invone,Rate)as R 
    right outer join
    (select Invone,InvDate,Type,Year,Terms,DueDate,Party,Party_ID,sum(Pieces)as TotalPieces, round(sum(Carats), 3) AS TotalCarats, round(sum(GrossAmt), 2) AS TotalGrossAmt, round(sum(DisAmt1), 2) AS TotalDis1,
      round(sum(DisAmt2), 2) AS TotalDis2, round(sum(FinalAmt), 2) AS TotalFinalAmt from Sells
    group by Invone,InvDate,Type,Year,Terms,DueDate,Party,Party_ID )as S  
     on  S.Invone=R.Invone 
    WHERE S.TotalFinalAmt-isnull(R.TotalRecievedAmt,0)  0