Issue will NULL populated field

Last Post 01 Oct 2009 09:10 AM by dmurphy09. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

01 Oct 2009 09:10 AM
Hi there,

It's my first post and i'm hoping you guys can assist me!

This query essentially lists of all the latest account balances in an accountpool where accounts begin with 777 and 888.

The accountpool lies within a company and 'id' is the primary key for all of these tables.

The problem I am having is when there is no balance for a particular a/c the query is not returning NULL for that particular account, it is completely ignoring it. I would like it to return NULL

I believe the problem lies in the WHERE part of the query when no accountid is returned.

SELECT "Company", "AccountPool", "Account",
b.balance "Balance",
CONVERT (VARCHAR(12), b.datex, 103) "Date",
PoolSide =
CASE a.poolside
WHEN -30001 THEN 'WE'
AccountPool ap WITH JOIN
account a WITH ON a.PoolId = ap.Id LEFT JOIN
accountbalance b WITH ON a.ID = b.accountID JOIN
company c WITH ON c.ID = a.companyID

WHERE b.datex =
SELECT MAX(b2.datex)
FROM accountbalance b2 WITH (NOLOCK)
WHERE accountid =

accountpool join account ON
account.poolid =
WHERE like '7777%' or like '8888%')

ORDER BY AccountPool, Account

Acceptable Use Policy