Bank Statement Problem - another question page 2

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

General SQL Server Forums

New to SQL Server Programming

Bank Statement Problem - another question page 2

Author

Topic

Mickybee
Starting Member

29 Posts

Posted - 2007-07-23 : 17:16:21

Hi all,

Im new so apologies in advance if this is an easy one.

I have a single table which holds:
ID (PK)
EnteredDate
MonetaryValue
REF_ValueType

It's basically used to record income and expenditure and the records look something like:

ID EnteredDate MonetaryValue REF_ValueType
1 21/01/07 12.34 Received
2 22/01/07 -8.34 Paid
3 23/02/07 100.28 Received

What Im trying to do is to write sql that will return all the records in the form of a bank statement with received values in one column and paid values in another e.g.
Date Income Expenditure
21/01/07 12.34
22/01/07 -8.34
23/02/07 100.28

Ive tried all ways but I still can't get it to work. Is there a simple way.

All advice most definitely appreciated, it's sending me crazy. Many thanks in advance

Mike

Mickybee
Starting Member

29 Posts

Posted - 2007-07-23 : 17:18:33

Sorry, the message did not come out very well. Im trying to get all positive (received) values under the Income column and all negative values (Paid) in the expenditure column. All sorted by the date

Thanks again.

Mike

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 17:21:16

SELECT EnteredDate, SUM(CASE WHEN REF_ValueType = 'Received' THEN MonetaryValue ELSE 0 END) AS Income,
SUM(CASE WHEN REF_ValueType = 'Paid' THEN MonetaryValue ELSE 0 END) AS Expenditure
FROM Table1
GROUP BY EnteredDate ORDER BY EnteredDate

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-23 : 17:21:48

[code]
select
date,
Income = case when REF_ValueType = 'Received' then MonetaryValue else null end,
Expenditure = case when REF_ValueType = 'Paid' then MonetaryValue else null end
from
MyTable

[/code]

CODO ERGO SUM

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 17:21:53

[code]
SELECT
Income = CASE WHEN MonetaryValue > 0 THEN MonetaryValue ELSE 0 ENd
,Expenditure = CASE WHEN MonetaryValue < 0 THEN MonetaryValue ELSE 0 ENd
, --other columns
FROm
yourtable
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 17:22:44

Holy cow!!!

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 17:28:13

37 seconds...

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 17:47:24

Yea.. Are you sitting with a loaded gun with "shoot at sight" orders?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 17:48:46

Some people seem to think I have a bot !

Peter Larsson
Helsingborg, Sweden

Mickybee
Starting Member

29 Posts

Posted - 2007-07-24 : 13:19:07

Hi all,

Many thanks for all the comments, it really has helped. Im sorry but Im not an expert so really do appreciate your responses.

Many thanks,
Mike

Mickybee
Starting Member

29 Posts

Posted - 2007-07-24 : 15:14:40

Hi again,

Im asking for a lot I know....

Ive got it working, well almost.

It's pulling back records OK using:

SELECT ContraDate, CASE WHEN REF_TRANSACTION_STATE = 6 THEN GrossMonetaryValue ELSE NULL END AS Income,
CASE WHEN REF_TRANSACTION_STATE = 4 THEN GrossMonetaryValue ELSE NULL END AS Expenditure
FROM dbo.[TRANSACTION]

as follows:
25/06/2007 00:00:00 125.00 NULL
25/06/2007 00:00:00 625.00 NULL
NULL NULL NULL
26/06/2007 00:00:00 NULL -100.00

Have to say that's kind a cool.

One thing I did forget was that there are in fact other STATE types which I want to ignore (these are proposed income types e.g. purchase orders). At the moment, the query is finding these and basically setting null values and (because I dont have a real received/paid date the date is null too. Is there any way I can simply ignore these from the query completely and just return the rows which match a received (6) or paid type (4)

Finally, im really not sure whether this is possible. I do want this to look like a bank statement so when I do the query I will be asking it to return all values between a certain period (that bit Im ok with) but what would be really cool is (in the first line of the result to pull back the opening balance type stuff (i.e. everything added together (receipts and payments) before that date. Please tell me if this is something that can't really be done in SQL.

thanks again.

Mike

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 16:50:38

SELECT ContraDate, CASE WHEN REF_TRANSACTION_STATE = 6 THEN GrossMonetaryValue ELSE NULL END AS Income,
CASE WHEN REF_TRANSACTION_STATE = 4 THEN GrossMonetaryValue ELSE NULL END AS Expenditure
FROM dbo.[TRANSACTION]
WHERE REF_TRANSACTION_STATE IN (4, 6)

E 12°55'05.76"
N 56°04'39.42"

Mickybee
Starting Member

29 Posts

Posted - 2007-07-26 : 03:17:13

Cool, thanks Peso

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-26 : 08:18:05

quote:
Originally posted by Peso

Some people seem to think ~~I have~~ you are a bot !

Peter Larsson
Helsingborg, Sweden

KH
[spoiler]Time is always against us[/spoiler]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-26 : 09:24:58

quote:
Originally posted by khtan

quote:
Originally posted by Peso

Some people seem to think ~~I have~~ you are a bot !

Peter Larsson
Helsingborg, Sweden

KH
[spoiler]Time is always against us[/spoiler]

I wish I had a Peso bot. I'd let it write all my code.

CODO ERGO SUM

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-07-27 : 09:31:20

Wot's a bot ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-27 : 09:44:36

http://en.wikipedia.org/wiki/Internet_bot

KH
[spoiler]Time is always against us[/spoiler]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-27 : 10:02:39

I wish Peso could solve all of my Bank Statement problems! More INSERT statements!

- Jeff
http://weblogs.sqlteam.com/JeffS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-27 : 10:32:51

You will also need MVJ's help.

INSERT INTO myBankStatement(transaction_type, amount)
SELECT 	'DEPOSIT', 1000000000
FROM	F_TABLE_NUMBER_RANGE(1, 1000000000)

-- F_TABLE_NUMBER_RANGE is from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=F_TABLE_NUMBER_RANGE

KH
[spoiler]Time is always against us[/spoiler]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-27 : 15:10:27

quote:
Originally posted by khtan

You will also need MVJ's help.

INSERT INTO myBankStatement(transaction_type, amount)
SELECT 'DEPOSIT', 1000000000
FROM F_TABLE_NUMBER_RANGE(1, 1000000000)

-- F_TABLE_NUMBER_RANGE is from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=F_TABLE_NUMBER_RANGE

KH
[spoiler]Time is always against us[/spoiler]

Sorry, F_TABLE_NUMBER_RANGE can only return 16,777,216 rows.


INSERT INTO myBankStatement(transaction_type, amount)
SELECT 	'DEPOSIT', $1000000000
FROM
	F_TABLE_NUMBER_RANGE(1, 16777216)

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-27 : 20:25:05

Not a problem. Jeff can resolve that himself with cross join

INSERT INTO myBankStatement(transaction_type, amount)
SELECT 	'DEPOSIT', $1000000000
FROM
	F_TABLE_NUMBER_RANGE(1, 16777216)
CROSS JOIN
        F_TABLE_NUMBER_RANGE(1, 16777216)

KH
[spoiler]Time is always against us[/spoiler]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources