Post
Ask Question
Joginder Banger

Joginder Banger

  • 214
  • 8.7k
  • 1m

Get Credit number

Feb 20 2017 8:24 AM
  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)