1,105,644 Community Members

Sql query statement producing null int value :(

Member Avatar
SyncMaster170
Junior Poster
110 posts since Sep 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
1
 

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
Member Avatar
SyncMaster170
Junior Poster
110 posts since Sep 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
SyncMaster170
Junior Poster
110 posts since Sep 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
urtrivedi
Posting Virtuoso
1,900 posts since Dec 2008
Reputation Points: 249 [?]
Q&As Helped to Solve: 409 [?]
Skill Endorsements: 27 [?]
 
0
 

can you put sample data from table a and b

Member Avatar
SyncMaster170
Junior Poster
110 posts since Sep 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
BitBlt
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]
Featured
 
1
 

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!

Question Answered as of 1 Year Ago by urtrivedi, BitBlt and adam_k
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article