Only customer accounts with + & - balances

Question

Post reply

Only customer accounts with + & - balances

smive2003

SSC Rookie

Points: 34
More actions
July 13, 2010 at 3:52 am

#226726

I’m new to SQL.
I have to provide customer account balances and only show customers accounts that have any positive balance against any negative balance from any of up to 11 accounts per customer.
E.g.
NameA/C 1A/C 2A/C 3A/C 4
Cust 1 -£45 £80
Cust 2 -£20 £15
Cust 3 £30 -£15 -£5
Cust 4 £50 -£40
I have the balances for all customers but don’t know how to limit this to only accounts that have a + & - balance? Not sure about if a temp table would help as a where clause would be very long?
Any suggestions much appreciated

Viewing 8 posts - 1 through 7 (of 7 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

--Sample data creation

DECLARE @TABLE AS TABLE(

[ID] INT IDENTITY,

[Name] VARCHAR(50),

[A/C] MONEY)

INSERT INTO @TABLE ([Name],[A/C])

SELECT 'Cust 1', -£45

UNION ALL SELECT 'Cust 1', £80

UNION ALL SELECT 'Cust 2', -£20

UNION ALL SELECT 'Cust 2', £15

UNION ALL SELECT 'Cust 3', £30

UNION ALL SELECT 'Cust 3', -£15

UNION ALL SELECT 'Cust 3', -£5

UNION ALL SELECT 'Cust 4', £50

UNION ALL SELECT 'Cust 4', -£50

--Query

SELECT [Name],

SUM([A/C]) AS [Balance]

FROM @TABLE

GROUP BY [Name]

HAVING SUM([A/C]) <> 0

I think this is what you're after. If not, DDL please (see link in my sig).

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

--Sample data creation

DECLARE @TABLE AS TABLE(

[ID] INT IDENTITY,

[Name] VARCHAR(50),

[A/C] char(8),

Balance money)

INSERT INTO @TABLE ([Name],[A/C], [Balance])

SELECT 'Cust 1', '10000001', -£45 UNION ALL

SELECT 'Cust 1', '10000002', £80 UNION ALL

SELECT 'Cust 2', '20000001', -£20 UNION ALL

SELECT 'Cust 2', '20000002', £15 UNION ALL

SELECT 'Cust 3', '30000001', £30 UNION ALL

SELECT 'Cust 3', '30000002', -£15 UNION ALL

SELECT 'Cust 3', '30000003', -£5 UNION ALL

SELECT 'Cust 4', '40000001', £50 UNION ALL

SELECT 'Cust 4', '40000002', -£50

-- query

SELECT a.*, b.[A/C], b.Balance

FROM @TABLE a

INNER JOIN @TABLE b

ON b.Name = a.Name -- same name

AND b.[A/C] <> a.[A/C] -- different accounts

AND (b.Balance > 0 AND a.Balance < 0) -- one debit, the other credit

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 3

smive2003 (7/13/2010)
I’m new to SQL.
I have to provide customer account balances and only show customers accounts that have any positive balance against any negative balance from any of up to 11 accounts per customer.
E.g.
NameA/C 1A/C 2A/C 3A/C 4
Cust 1 -£45 £80
Cust 2 -£20 £15
Cust 3 £30 -£15 -£5
Cust 4 £50 -£40
I have the balances for all customers but don’t know how to limit this to only accounts that have a + & - balance? Not sure about if a temp table would help as a where clause would be very long?
Any suggestions much appreciated

Do you really have 4 accounts per row in the original data????

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

smive2003 SSC Rookie Points: 34 More actions · Answer 4

Yes Jeff

I have up to 11 accounts per customer some with credit balances others in debit and need to limit my data to where they have both + & - balances over their accounts.

Thanks

J

Nevyn SSChampion Points: 14210 More actions · Answer 5

So you have a customer table with 11 account fields on it?

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 6

smive2003 (7/14/2010)
Yes Jeff
I have up to 11 accounts per customer some with credit balances others in debit and need to limit my data to where they have both + & - balances over their accounts.
Thanks
J

In that case, the first thing I'd do is fix that design problem. Yeah... I already know... not possible.

So, the next best thing would be to unpivot the data, do your counts where any balance <> 0 for the given customer, and use that result to select from the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DataDog Hall of Fame Points: 3305 More actions · Answer 7

here is a couple of ways

set nocount on

create table #customers (

[cid] int identity primary key

,[ac1] money not null

,[ac2] money not null

,[ac3] money not null

)--create table

-- don't leave "include actual execution plan" on for this ...

while ((select count(*) from #customers) < 100) begin

insert into #customers ([ac1], [ac2], [ac3])

selectcast(rand() * 10000 - 5000 as money)

,cast(rand() * 10000 - 5000 as money)

end--while

create index IX_customers_ac1 on #customers(ac1)

create index IX_customers_ac2 on #customers(ac2)

create index IX_customers_ac3 on #customers(ac3)

-- full scan

select *

from #customers

where not((ac1 >= 0 and ac2 >= 0 and ac3 >= 0)

or(ac1 <= 0 and ac2 <= 0 and ac3 <= 0))

-- 3 index scans etc.

select cid

from (

select cid, sign(ac1) [sign]

from #customers

where ac1 <> 0

union all

select cid, sign(ac2)

from #customers

where ac2 <> 0

union all

select cid, sign(ac3)

from #customers

where ac3 <> 0

) dt

group by cid

having count(distinct [sign]) > 1