July 27, 2008 at 7:20 pm
The below statement works fine without the last 'HAVING' clause.
With the 'HAVING' though it gives me an error message.
I need to exclude the sum(amt) where it equals to 0 from my resultset.
Is there a workaround for it?
create table policy (policyid int, premium money, batchid int)
create table policytran (policyid int, originalamount money, batchid int)
insert into policytran 1, 25,000, 1
insert into policytran 1, 15,000, 2
insert into policytran 1, 20,000, 3
insert into policytran 2, 27,000, 1
insert into policytran 2, 19,000, 2
insert into policy 1, 68,000, 1
insert into policy 2, 59,000, 1
select PolicyID,
batchid,
sum(amt) over (partition by policyid) as tranamt
from (
select
policyid,
batchid,
premium as amt
from policy
union
select
policyid,
batchid,
-sum(originalamount) over (partition by policyid) as tranamt
from policytran
) delta
having sum(amt) <> 0
July 27, 2008 at 8:26 pm
Make this a CTE and then use a "where amt > 0" in the outer query that selects from the CTE?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 28, 2008 at 6:24 am
What are you trying to get? It looks like you have a policy table containing a default amount, and then a policytrans table containing what, a transaction specific amount? A history amount when the transaction was open? The actual amount applied to a specific policy? ...And you are trying to calculate what, specifically?
July 28, 2008 at 9:24 am
The reason you are getting this error is because you are not using a GROUP BY query, so you can't use HAVING with a SUM.
It is not clear what you are trying to do here and before we continue I would suggest that you read the following article:
Best Practices: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
If you can put together the create statements for the sample tables, insert statements for a few results and expected results you will find that someone here will have a solution for you very quickly.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 28, 2008 at 2:07 pm
I edited the post, thank you!
July 28, 2008 at 2:12 pm
Why not the following:
select PolicyID,
sum(amt) as tranamt-- over (partition by policyid) as tranamt
from (
select
policyid,
premium as amt
from policy
union
select
policyid,
-sum(originalamount) over (partition by policyid) as tranamt
from policytran
) delta
GROUP BY PolicyID
having sum(amt) <> 0
Or is this not what you are looking for?
July 28, 2008 at 2:21 pm
I have to agree with MentalWhiteNoise (great moniker by the way). That query is simpler and I am going to guess it returns the same data.
You could also use Steve's approach and CTE it. I would probably put the derived table query in CTE anyway. I find tha CTE's make reading and debugging code much easier.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 28, 2008 at 2:36 pm
Sorry, I forgot to add one more field.
I need a row value field mixed with the aggregate on the selects.
That's why it's problematic to use GROUP BY (and HAVING for this matter) in the outer select.
I edited my post, please check it out.
Thank you!
July 28, 2008 at 3:09 pm
Can you please explain what you expect for results from the test data you provided?
What is the ultimate goal? Do you want to see balance remaining? I am assuming that policy.premium is amount owed and policytrans.origianlamount is the amount paid.
Why do you need to include batchid in the output?
Here is some code that I believe gives you the results and functionality you request. Note that I had to correct your insert statements as they did not include a select or values after the table name and included a "," in the amounts which SQL considered a new column. I also used table variables so that I would not have to drop the tables later:
[font="Courier New"]DECLARE @policy TABLE (policyid INT, premium money, batchid INT)
DECLARE @policytran TABLE (policyid INT, originalamount money, batchid INT)
INSERT INTO @policytran VALUES( 1, 25000, 1)
INSERT INTO @policytran VALUES( 1, 15000, 2)
INSERT INTO @policytran VALUES( 1, 20000, 3)
INSERT INTO @policytran VALUES( 2, 27000, 1)
INSERT INTO @policytran VALUES( 2, 19000, 2)
INSERT INTO @policy VALUES( 1, 68000, 1)
INSERT INTO @policy VALUES( 2, 59000, 1)
;WITH ctePolicyPayments AS
(
SELECT
P.policyid,
T.batchId,
T.originalamount AS payment,
P.premium - SUM(T.originalamount) OVER (Partition BY T.policyid) AS owed
FROM
@policy P JOIN
@policytran T ON
P.policyid = T.policyid
)
SELECT * FROM ctePolicyPayments WHERE owed <> 0[/font]
Here are the results:
policyid batchId payment owed
----------- ----------- --------------------- ---------------------
1 1 25000.00 8000.00
1 2 15000.00 8000.00
1 3 20000.00 8000.00
2 1 27000.00 13000.00
2 2 19000.00 13000.00
If you really wanted to get fancy you could create it so that for each batchid it shows the amount remaining after that specific batch.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 28, 2008 at 5:45 pm
Thank you!
It helped a lot!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply