CODEWITHSUNDEEP
c#sqlaccounting
Lisa
Lisa

Reputation: 65

Get credit and debit in a single row sql

i am stuck in a situation to find accounts related report i need to get credit account and debit account in a single row..with amount

my table is like this

Id  VoucherId   AccountId   Amount  AccountName IsDebit
1   1           26          100     Sales       0
2   1           10          100     Cash        1
3   2           26          200     Sales       0
4   2           10          200     Cash        1
5   3           10          150     Cash        0
6   3           20          150     Expense A   1
7   4           10          240     Cash        0
8   4           21          240     Expense B   1

and i need to get the result like this VoucherId DebitName DebitID CreditName CreditID Amount
1 Cash 10 Sales 26 100 2 Cash 10 Sales 26 200 3 Expense a 10 Cash 26 150 4 Expense b 10 Cash 26 240 i tried this

select vc.Id,
am.Name,vc.AccountName,vd.[Description],
    case when vd.isdebit=1 then vd.amount else 0 end  as C6,
    case when vd.isdebit=1 then vd.AccountId else 0 end as A,
    case when vd.isdebit=0 then vd.amount else 0 end as C7,
    case when vd.isdebit=0 then vd.AccountId else 0 end as B
    from VoucherDetails vd 
    inner join Voucher Vc on vc.Id=vd.VoucherId and vc.IsDeleted=0
    inner join AccountsMaster Am on am.Id=vd.AccountId

and many other query, but not getting the above result

pls help.. thanks in Advance

Upvotes: 1

Views: 1762

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You need aggregation:

select vc.Id, 
       sum(case when vd.isdebit = 1 then vd.amount else 0 end)  as C6,
       sum(case when vd.isdebit = 1 then vd.AccountId else 0 end) as A,
       sum(case when vd.isdebit = 0 then vd.amount else 0 end) as C7,
       sum(case when vd.isdebit = 0 then vd.AccountId else 0 end) as B
from VoucherDetails vd join
     Voucher Vc 
     on vc.Id = vd.VoucherId and vc.IsDeleted = 0 join
     AccountsMaster Am
     on am.Id = vd.AccountId
group by vc.Id

Upvotes: 1

Gnyasha
Gnyasha

Reputation: 684

This will work if you are doing double entry. That is for every debit entry there should be also a credit entry.

select DrVoucherId as VoucherId,DebitName,DebitId,CreditName,CreditId,Amount from (
select VoucherId as DrVoucherId,AccountName as DebitName ,AccountId as DebitId from VoucherDetails where IsDebit=1
) a
left join
(select VoucherId as CrVoucherId,AccountName as CreditName ,AccountId as CreditId,Amount from VoucherDetails where IsDebit=0) b on  a.DrVoucherId=b.CrVoucherId

Upvotes: 1

Tongle
Tongle

Reputation: 36

Like this?:

select VoucherId,
  max(case when IsDebit = 1 then AccountID end) DebitID,
  max(case when IsDebit = 1 then am.Name end) DebitName,
  max(case when IsDebit = 0 then AccountID end) CreditID,
  max(case when IsDebit = 0 then am.Name end) CreditName,
  Amount
from VoucherDetails vd
  join AccountsMaster am on am.Id = AccountID
group by VoucherId, Amount
order by VoucherId

Tested here: http://sqlfiddle.com/#!18/25162/13

Upvotes: 1

Related Questions