asked
Please help urgently - BB
I have a table name 'AccVoucher' which contains the following fields: Voucher Nbr, Voucher Amt, Voucher Type(Values are 'DB'-Debit and 'CR'-Credit), Department One Voucher Nbr can have more than one values Example of records in table Voucher Nbr Voucher Amt Voucher Type Department VC10001 1000 DB Finance VC10001 850 DB Finance VC10001 750 DB Finance VC10001 100 CR Finance VC10001 2000 DB IT VC10001 500 DB IT VC10001 50 CR IT VC10001 500 DB Purchase VC10001 50 DB Purchase VC10001 100 CR Purchase I have 2 queries Query 1: select sum(Voucher Amt)dbAmt from AccVoucher where Vouch_Nbr = 'VC10001' and Voucher Type = 'DB' group by Department Query 2: select sum(Voucher Amt)crAmt from AccVoucher where Vouch_Nbr = 'VC10001' and Voucher Type = 'CR' group by Department Using these two queries i need to find the difference of dbAmt - crAmt using group by Department. Please give me single query to get this result. Result of the query should be dbAmt crAmt Difference Department 2600 100 2500 Finance 2500 50 2450 IT 550 100 450 Purchase Please help me with this .... Its urgent
@jonlee lockwood , @blackhawk-17 now now kiddies, you play nicely together please :)
First, I'll turn your example into insert statements, and then follow that with the query. declare @vouchers table ( VoucherNbr varchar(8), VoucherAmt int, VoucherType char(2), Department varchar(15) ); insert into @vouchers(VoucherNbr, VoucherAmt, VoucherType, Department) select 'VC10001', 1000, 'DB', 'Finance' UNION ALL select 'VC10001', 850, 'DB', 'Finance' UNION ALL select 'VC10001', 750, 'DB', 'Finance' UNION ALL select 'VC10001', 100, 'CR', 'Finance' UNION ALL select 'VC10001', 2000, 'DB', 'IT' UNION ALL select 'VC10001', 500, 'DB', 'IT' UNION ALL select 'VC10001', 50, 'CR', 'IT' UNION ALL select 'VC10001', 500, 'DB', 'Purchase' UNION ALL select 'VC10001', 50, 'DB', 'Purchase' UNION ALL select 'VC10001', 100, 'CR', 'Purchase' select VoucherNbr, Department, SUM(case when VoucherType = 'DB' then 1 * VoucherAmt else 0 end) as DebitAmount, SUM(case when VoucherType = 'CR' then 1 * VoucherAmt else 0 end) as CreditAmount, SUM(case VoucherType when 'DB' then 1 when 'CR' then -1 else 0 end * VoucherAmt) as Difference from @vouchers group by VoucherNbr, Department; The [case][1] statement allows you to differentiate between debits and credits, treating them differently. That way, you can do all of your processing in one query rather than two separate queries. [1]:
http://msdn.microsoft.com/en-us/library/ms181765.aspx
Does this do it, where #Temp is your table AccVoucher: SELECT Debit.Department, Debit.dbAmt, Credit.crAmt, dbAmt - crAmt AS [Difference] FROM ( select sum(VoucherAmt)dbAmt , Department from #Temp AS T where VoucherNbr = 'VC10001' and VoucherType = 'DB' group by Department ) Debit JOIN ( select sum(VoucherAmt)crAmt , Department from #Temp AS T where VoucherNbr = 'VC10001' and VoucherType = 'CR' group by Department) Credit ON Debit.Department = Credit.Department
@jonlee lockwood - lost posting the answer and lost your edits... rough question for you :)
