Heres my statement:

SELECT A.AccountID,
A.LastName,
(SELECT SUM(InvCurrency) FROM B
WHERE B.BillingPeriodStart Between '2012-01-01 00:00:00.000' AND '2012-12-31 23:59:59.000' AND
A.AccountID = B.AccountID AND
StatusID = 1 AND
(ServiceCode=13 OR ServiceCode=14) AND
(ServiceCode=15 OR ServiceCode=16) AND
(ServiceCode=17 OR ServiceCode=18) AND
(ServiceCode=19 OR ServiceCode=20) AND
(ServiceCode=21 OR ServiceCode=26) )
AS 'Paid'
FROM A
order by LastName

Now, to clear some things up:
1. StatusID =1 if that customer has paid that bill, it equals 0 if he/she hasnt.
2. I only want the total amount for each person for the year 2012.

Problem:
Everything that I want pops up, only the Paid column is showing up as null and I do not know why:(, hopefully its something small. Any help is much appreciated.

Try this:

SELECT A.AccountID,
A.LastName,
B.Paid
FROM A, (SELECT AccountID, SUM(InvCurrency) as 'Paid' FROM B
WHERE B.BillingPeriodStart Between '2012-01-01 00:00:00.000' AND '2012-12-31 23:59:59.000' AND
StatusID = 1 AND
(ServiceCode=13 OR ServiceCode=14) AND
(ServiceCode=15 OR ServiceCode=16) AND
(ServiceCode=17 OR ServiceCode=18) AND
(ServiceCode=19 OR ServiceCode=20) AND
(ServiceCode=21 OR ServiceCode=26) …

First off, your date criterion in your sample data is outside the bounds of the "BETWEEN". That's the most obvious thing. But that's not the real problem.

Your problem is in all the "AND"s and "OR"s. If you think about it logically, what you are saying is that the ServiceCode …

## All 6 Replies

Try this:

SELECT A.AccountID,
A.LastName,
B.Paid
FROM A, (SELECT AccountID, SUM(InvCurrency) as 'Paid' FROM B
WHERE B.BillingPeriodStart Between '2012-01-01 00:00:00.000' AND '2012-12-31 23:59:59.000' AND
StatusID = 1 AND
(ServiceCode=13 OR ServiceCode=14) AND
(ServiceCode=15 OR ServiceCode=16) AND
(ServiceCode=17 OR ServiceCode=18) AND
(ServiceCode=19 OR ServiceCode=20) AND
(ServiceCode=21 OR ServiceCode=26)
Group By AccountID ) B
on A.AccountID = B.AccountID
order by LastName

What is B.Paid? It is not an actual column in a table, it is a temp. column I assigned to put the sum of InvCurrency in. But thanks for your input and I will try this out in the morning.

SORRY I didnt clear this up in my first post, but here are the columns for table A and B:
A columns are: AccountID, LastName
B columns are: AccountID, InvCurrency, BillingPeriodStart, StatusID, ServiceCode

can you put sample data from table a and b

Sure.To recap, I am using table A to get the LastName and using the account # to get the correct invCurrency amount for each account that should be summed up after the query executes.

Table A SAMPLE DATA BELOW(The text in bold is the column names):
AccountID - 12345
LastName - Doe
FirstName - Jane

Table B SAMPLE DATA BELOW(The text in bold is the column names):
AccountID - 12345
BillingPeriodStart - 2011-03-01 00:00:00.000
StatusID - 1
InvCurrency - 180.00
ServiceCode - 14

After the query executes, the result set looks like this:

AccountID - 12345
LastName - Doe
Paid - NULL

If StatusID = 1, then that member has paid a certain bill/fee(the amount is under the InvCurrency column). I then want to sum up all the bills/fees that member has made for the year 2012 and for that number to be put in a temp column named 'Paid'.

Hope this helps you help me:)

First off, your date criterion in your sample data is outside the bounds of the "BETWEEN". That's the most obvious thing. But that's not the real problem.

Your problem is in all the "AND"s and "OR"s. If you think about it logically, what you are saying is that the ServiceCode value MUST equal two different things in order to return a row. So, for your sample data, it would satisfy that it is 13 OR 14, but not that it is 15 OR 16, etc. Re-evaluate what you're trying to accomplish, perhaps as in something like:

SELECT A.AccountID,
A.LastName,
(SELECT SUM(InvCurrency) FROM B
WHERE B.BillingPeriodStart Between '2012-01-01 00:00:00.000' AND '2012-12-31 23:59:59.000' AND
A.AccountID = B.AccountID AND
StatusID = 1 AND
ServiceCode in (13, 14, 15, 16, 17, 18, 19, 20, 21, 26))
AS 'Paid'
FROM A
order by LastName

However, if you want to return zeroes just in case your selection actually returns NULL, you could use this:

SELECT A.AccountID,
A.LastName,
isnull(                 -- <-- Notice this! -------------
(SELECT SUM(InvCurrency) FROM TableB b
WHERE B.BillingPeriodStart Between '2012-01-01 00:00:00.000' AND '2012-12-31 23:59:59.000' AND
A.AccountID = B.AccountID AND
StatusID = 1 AND
ServiceCode in (13, 14, 15, 16, 17, 18, 19, 20, 21, 26)),
0.00)                    -- <-- and this! -------------
AS 'Paid'
FROM TableA a
order by LastName

Or, you could simplify everything by using a LEFT JOIN instead of the sub-select.

SELECT A.AccountID,
A.LastName,
SUM(isnull(InvCurrency, 0.00)) as 'Paid'
FROM TableA A
left join TableB B
on A.AccountID = B.AccountID
AND B.StatusID = 1
AND B.ServiceCode in (13, 14, 15, 16, 17, 18, 19, 20, 21, 26)
AND B.BillingPeriodStart Between '2012-01-01 00:00:00.000' AND '2012-12-31 23:59:59.000'
group by A.AccountID, A.LastName
order by LastName

It'll probably perform better if you have lots of data.

Hope one of these is what you're looking for! Good luck!

commented: Thanks so much!!!!! Your are awesome! Thank you for taking the time to help others, you shark you;) that helped me tremendously and it is producing numbers in the PAID column now:) +2
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.