Need Help: SQL Statement Needed as below Output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Help: SQL Statement Needed as below Output

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-01-20 : 21:32:36

Dear All,

I have 1 table transaction, but i need to retrieve the record and perform it in this way (Output as mentioned).

ANyone here can gv me the sql statement or stored procedure. I'm using ms sql 2003 so it can't support shape method.

Table name: eWallet (This is the table of transaction)
ID MemberCode invoiceNo Date Debit Credit
1 AB123 EW001 01/02/2009 100.00 0.00
2 CD EW001 01/02/2009 0.00 100.00
3 John EW003 02/02/2009 50.00 0.00
4 Peter EW003 02/02/2009 0.00 50.00
5 Haroon 0001SIV 03/03/2009 100.00 0.00

Output: (Need SQL Statement for below output)
Date Invoice Sender Receiver Debit Credit Balance
01/02/2009 EW001 AB123 CD 100.00 100.00 0.00
02/02/2009 EW002 John Peter 50.00 50.00 0.00
03/03/2009 0001SIV Haroon - 100.00 0.00 0.00

Please Advise.
Thank you.

Regards,
Michelle

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-01-20 : 23:55:28

try this, but ll fail if u have more than 1 transaction in single day

DECLARE		@Table TABLE
(	
			ID INT, 
			MemberCode VARCHAR(100),
			InvoiceNo VARCHAR(100),
			Date DATETIME, 
			Debit DECIMAL(18, 2),
			Credit DECIMAL(18, 2)
)

INSERT INTO	@Table
			SELECT 1, 'AB123', 'EW001', '01/02/2009', 100.00, 0.00
UNION ALL	SELECT 2, 'CD', 'EW001', '01/02/2009', 0.00, 100.00
UNION ALL	SELECT 3, 'John', 'EW003', '02/02/2009', 50.00, 0.00
UNION ALL	SELECT 4, 'Peter', 'EW003', '02/02/2009', 0.00, 50.00
UNION ALL	SELECT 5, 'Haroon', '0001SIV', '03/03/2009', 100.00, 0.00
		
SELECT		Debit.Date,
			Debit.InvoiceNo,
			Debit.MemberCode AS 'Sender',
			Credit.MemberCode AS 'Receiver',
			Debit.Debit,
			ISNULL(Credit.Credit, 0) AS 'Credit',
			ISNULL(ISNULL(Debit.Debit, 0) - Credit.Credit, 0) AS 'Balance'
FROM		(
					SELECT	*
					FROM	@Table
					WHERE	Debit <> 0
			) AS Debit 
LEFT JOIN	(
					SELECT	*
					FROM	@Table
					WHERE	Credit <> 0
			) AS Credit ON Debit.InvoiceNo = Credit.InvoiceNo
							AND Debit.Date = Credit.Date

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 09:10:19

[code]
SELECT Date,invoiceNo,
COALESCE(Sender,'-') AS Sender,
COALESCE(Receiver,'-') AS Receiver,
COALESCE(Debit,0) AS Debit,
COALESCE(Credit,0) AS Credit,
COALESCE(Debit,0)-COALESCE(Credit,0) AS Balance
FROM
(
SELECT Date,invoiceNo,
MAX(CASE WHEN Debit>0 THEN MemberCode ELSE NULL END) AS Sender,
MAX(CASE WHEN Credit>0 THEN MemberCode ELSE NULL END) AS Receiver,
MAX(CASE WHEN Debit>0 THEN Debit ELSE NULL END) AS Debit,
MAX(CASE WHEN Credit>0 THEN Credit ELSE NULL END) AS Credit
FROM Table
GROUP BY Date,invoiceNo
)t
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources