September 21, 2012 at 8:59 am
I need to write a query to add up the bills sent to each client in a specific time period. Each client may have more than one bill but I only want to see the total billed for that client, so I want to use the SUM function.
Information about customers is stored on the Matters table, and information about bills is on the ac_billbook table.
The data structure is
Matters
clientID
number
description
representative
mattertype
chargetype
UFN
ac_Billbook
clientID
billdate
costsnet
My query as it stands is below. Currently the '... sum(ac_billbook.costsnet,0) ...' is at the end of the select statement, but to make sure I'm totalling up the right bills (rows from the ac_billbook table where the client IDs match) I think the solution is to use a nested select statement, but I'm unsure where to put the '... in (select sum ...)' part of the statement.
SELECT
matters.clientID as Client_Code, Number, description as Matter_Description, matters.representative as Rep, matters.mattertype as Matter_type, chargetyperef as Charge_Type, UFN, ac_billbook.billdate, SUM(ac_billbook.costsnet,0) as Costs_Billed
FROM matters INNER JOIN ac_billbook on matters.clientID = ac_billbook.clientID
WHERE ac_billbook.billdate >='2011-06-30' and ac_billbook.billdate <='2012-07-01'
and matters.mattertype = 1 and ac_billbook.costsnet <> 0
GROUP BY matters.clientID, description, matters.representative, chargetyperef, UFN, matters.mattertype, ac_billbook.billdate, ac_billbook.costsnet
HAVING matters.representative like '[reps name]'
ORDER BY matters.clientID
Any help greatefully received
Thanks
September 21, 2012 at 9:18 am
I am not exactly sure I understand your question but the SUM function takes only 1 parameter and you are passing it two. If you can post some ddl(create table scripts) and sample data (insert statements) along with desired output we can help with specific syntax. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 21, 2012 at 9:37 am
It's hard to understand what you're trying to do, I hope that you can help us to help you.
As Sean said, SUM() will only allow one parameter.
I've made some changes to your query, try to look at the comments on it to see if that's what you need.
SELECT matters.clientID as Client_Code,
Number,
description as Matter_Description,
matters.representative as Rep,
matters.mattertype as Matter_type,
chargetyperef as Charge_Type,
UFN,
--You can't leave ac_billbook.billdate without an aggregate function or costsnet will never aggregate correctly
MIN(ac_billbook.billdate) min_bildate, --Get the first billdate
MAX(ac_billbook.billdate) max_bildate, --Get the last billdate
ISNULL( SUM(ac_billbook.costsnet),0) as Costs_Billed --with all the clients it gives 0 to those who haven't been billed
FROM matters
LEFT --Get all the clients
OUTER JOIN ac_billbook on matters.clientID = ac_billbook.clientID
WHERE ac_billbook.billdate >='2011-06-30'
and ac_billbook.billdate <='2012-07-01'
and matters.mattertype = 1
and ac_billbook.costsnet <> 0
GROUP BY matters.clientID,
description,
matters.representative,
chargetyperef,
UFN,
matters.mattertype --,
--If you're aggregating these two fields you don't need to include them
--in the group by or it won't never work correctly
--ac_billbook.billdate,
--ac_billbook.costsnet
HAVING matters.representative like '[reps name]' --What's this for? Why isn't it on the WHERE clause?
ORDER BY matters.clientID
September 21, 2012 at 9:41 am
There are solutions using a nested select, but I think this should be simpler:
SELECT
matters.clientID as Client_Code,
Number,
description as Matter_Description,
matters.representative as Rep,
matters.mattertype as Matter_type,
chargetyperef as Charge_Type,
UFN,
ac_billbook.billdate,
SUM(ac_billbook.costsnet) OVER (PARTITION BY ac_billbook.clientID) as Costs_Billed
FROM matters
INNER JOIN ac_billbook
on matters.clientID = ac_billbook.clientID
AND ac_billbook.billdate >='2011-06-30'
and ac_billbook.billdate <='2012-07-01'
WHERE matters.mattertype = 1 and ac_billbook.costsnet <> 0
--GROUP BY matters.clientID, description, matters.representative, chargetyperef, UFN, matters.mattertype, ac_billbook.billdate, ac_billbook.costsnet
AND matters.representative like '[reps name]'
ORDER BY matters.clientID
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply