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?
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://www.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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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