Need Help with Credit, Dedit and Balance !!!!!

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 2008 Forums

Transact-SQL (2008)

Need Help with Credit, Dedit and Balance !!!!!

Author

Topic

teejay8841
Starting Member

2 Posts

Posted - 2011-12-19 : 11:35:41

Hi

i have a table which stores transactions as follows

Customer Date DocNumber Debit Credit
001 [01-01-2011] 20 100 0
001 [02-01-2011] 20 0 90

i need to create a simple view which shows in a single row as follows

001 [01-01-2011] 20 100 90 10

it should pick the of the date of the debit entry always and show credit adjucent while summing up as they will be many entries for that customer. i have done many things but im stuck now. please assist....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 11:41:20

view code will be like


CREATE VIEW viewname
AS
SELECT Customer, MIN(CASE WHEN Debit> 0 THEN Date ELSE NULL END) AS Date, DocNumber,SUM(Debit) AS Debit,SUM(Credit) AS Credit
FROM Table
GROUP BY Customer,DocNumber

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-19 : 11:42:08

create view MyView
as
select Customer, Min(Date) Date, DocNumber, Sum(Debit) Debits, Sum(Credit) Credits, Sum(Debit) - Sum(Credit) Balance
from MyTable
group by Customer, DocNumber

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

teejay8841
Starting Member

2 Posts

Posted - 2011-12-19 : 11:58:00

@visakh16 your script worked thanks!!!!!!!

now lets say for example i there is also a field named TrnType and i want to show the TrnType of the debit entry on the single row not of the credit entry. how do i go about it. Or i a column Seqence number and i want to take the value that is on the debit transaction as i might want to link it to another view as welll.....

good to go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 12:02:51

quote:
Originally posted by teejay8841

@visakh16 your script worked thanks!!!!!!!

now lets say for example i there is also a field named TrnType and i want to show the TrnType of the debit entry on the single row not of the credit entry. how do i go about it. Or i a column Seqence number and i want to take the value that is on the debit transaction as i might want to link it to another view as welll.....

good to go


CREATE VIEW viewname
AS
SELECT Customer, MIN(CASE WHEN Debit> 0 THEN Date ELSE NULL END) AS Date,
MIN(CASE WHEN Debit> 0 THEN TranType ELSE NULL END) AS TrnType, DocNumber,SUM(Debit) AS Debit,SUM(Credit) AS Credit
FROM Table
GROUP BY Customer,DocNumber

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources