1
Answer

Get Credit number

Joginder Banger

Joginder Banger
7y
232
1
  1. create table #CR(id int,name varchar(max),CreditValue int,CreditDate datetime)  
  2. create table #DR(id int,name varchar(max),debitValue int,CreditDate datetime)  
  3.   
  4. INSERT into #CR VALUES(1,'Jogi',5000,GETDATE())  
  5.   
  6. INSERT into #CR VALUES(1,'Jogi',7000,GETDATE())  
  7.   
  8. INSERT into #CR VALUES(1,'Jogi',9000,GETDATE())  
  9.   
  10. insert INTO #DR VALUES(1,'Jogi',3000,GETDATE())  
  11.   
  12. insert INTO #DR VALUES(1,'Jogi',4000,GETDATE())  
  13.   
  14. insert INTO #DR VALUES(1,'Jogi',7000,GETDATE())  
  15.   
  16. ;WITH Debit(Name,ID,TotalSum) AS
    (
    select Name,ID,sum(debitValue) from #DR1 GROUP by id,Name
    ),
    Credit(Name,ID,TotalSum) AS
    (
    select Name,ID,sum(CreditValue) from #CR1 GROUP by id,Name
    )
    select Debit.Name,Credit.TotalSum as CR,Debit.TotalSum as DR,(Credit.TotalSum-Debit.TotalSum) Balance FROM Debit
    inner join Credit on Debit.Id=Credit.ID

Answers (1)
Next Recommended Forum