Query balance of account from two or more tables

Question

Query balance of account from two or more tables

hemal_301080

Say Hey Kid

Points: 677
More actions
December 25, 2012 at 5:14 am

#286527

I am having two tables as follows
voucherCr
srno vouchertype voucherprefix voucherno crparty cramount
1 PURCHASE P 1 2 55000
2 PAYMENT R 1 1 55000
voucherDr
srno vouchertype voucherprefix voucherno drparty dramount
1 PURCHASE P 1 4 54000
2 PURCHASE P 1 5 1000
3 PAYMENT R 1 2 55000
Here, in PURCHASE voucher P/1, I am purchasing goods from party 2 worth of 55000 and in PAYMENT voucher R/1, I am paying 55000 rupees to party 2.
Now If i query about party 2, I want to display information in following format
VTYPE VPRE VNO AGAINSTPARTY CREDIT DEBIT
PURCHASE P 1 4 55000 NULL
PAYMENT R 1 1 NULL 55000
So the closing balance for party 2 will be zero, as I have paid them 55000 against the purchase of 55000
Can you help me?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86908 More actions · Answer 1

Normally, most people won't answer your question because you didn't take the time to provide DDL and sample data in consumable form. There are many good links to how to do this, but since it is Christmas and I'm feeling charitable I will demonstrate and explain with comments in the code.

-- Next time - provide DDL

CREATE TABLE #voucherCr

(srno INT

,vouchertype VARCHAR(20)

,voucherprefix CHAR(1)

,voucherno INT

,crparty INT

,cramount MONEY)

CREATE TABLE #voucherDr

(srno INT

,vouchertype VARCHAR(20)

,voucherprefix CHAR(1)

,voucherno INT

,drparty INT

,dramount MONEY)

-- And sample data in consumable form

INSERT INTO #voucherCr

SELECT 1,'PURCHASE','P',1,2,55000

UNION ALL SELECT 2,'PAYMENT','R',1,1,55000

INSERT INTO #voucherDr

SELECT 1,'PURCHASE','P',1,4,54000

UNION ALL SELECT 2,'PURCHASE','P',1,5,1000

UNION ALL SELECT 3,'PAYMENT','R',1,2,55000

SELECT VTYPE=vouchertype, VPRE=voucherprefix, VNO=voucherno

,AGAINSTPARTY=crparty

,CREDIT=cramount, DEBIT=NULL

FROM #voucherCr

WHERE crparty = 2

UNION ALL

SELECT vouchertype, voucherprefix, voucherno, drparty, NULL, dramount

FROM #voucherDr

WHERE drparty = 2

-- Always use temporary tables and don't forget to drop them

DROP TABLE #voucherCr

DROP TABLE #voucherDr

This query is only meant to get you started. It does not produce the expected results in the AGAINSTPARTY column, because frankly I couldn't see a pattern for how you came up with 1 and 4.

Play around with it and see if you can figure out how to make it work to get exactly the results you were expecting.

Merry Christmas.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

hemal_301080 Say Hey Kid Points: 677 More actions · Answer 2

Thanks, dwain

Next time I will be providing DDL also, Sorry for my question

Again thanks for your answer. I will test it. And let you know