Sql query statement producing null int value :(

[SyncMaster170]

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.

[adam_k]

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

[SyncMaster170]

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.

[SyncMaster170]

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

[urtrivedi]

can you put sample data from table a and b

[SyncMaster170]

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:)

[BitBlt]

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!