select max when joining three tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select max when joining three tables

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-23 : 15:33:16

I have a query that joins across three tables. Table policy has one record to many records in policy transaction. I need the maximum value of the column sequence to increment it by one. Eventually this select will be used in an insert select statement.

Currently the query returns 2 for the Sequence column, when it should be 6 for the record I checked.

Thanks for any help!

SELECT Policy.Policy_Base, Policy.Policy_Suffix, Policy.Endorsement, Policy.Company, Policy.Subcompany, MAX(policy_transaction.Sequence)
+ 1 AS Sequence
FROM policy_transaction INNER JOIN
Policy ON policy_transaction.Policy_Base = Policy.Policy_Base AND policy_transaction.Policy_Suffix = Policy.Policy_Suffix INNER JOIN
policy_transaction_fee ON policy_transaction.Policy_Base = policy_transaction_fee.policy_base AND
policy_transaction.Policy_Suffix = policy_transaction_fee.policy_suffix AND policy_transaction.Sequence = policy_transaction_fee.sequence
WHERE (policy_transaction.Entered_Date >= '2008-08-28') AND (policy_transaction.Transaction_Type IN ('rn', 'np')) AND (Policy.Status <> 'ca') AND
(Policy.EFT_Flag = 'y') AND (Policy.Current_Flag = 'y') AND (Policy.Installment_Fees >= 10) AND (policy_transaction_fee.fee_code = 'ins')
GROUP BY Policy.Policy_Base, Policy.Policy_Suffix, Policy.Endorsement, Policy.Company, Policy.Subcompany

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-24 : 12:08:21

Still stuck on this. I am trying the HAVING clause but no luck... Can anyone at least point me in the right direction? Is what I am trying to do even possible?

I have considered creating a function for this but do not know if that would work and would like to avoid it because of how this will be used.

If I need to provide more information or explain better please let me know.

If this is not possible please let me know and I will look at using a cursor for the solution, though I would like to avoid that.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-24 : 12:34:35

Have not understand what you want.
Maybe you can provide Sample Tables and Sample Data?
Here ist a more readable form of your query posted so far


SELECT   policy.policy_base, 
         policy.policy_suffix, 
         policy.endorsement, 
         policy.company, 
         policy.subcompany, 
         Max(policy_transaction.SEQUENCE) + 1 AS SEQUENCE 
FROM     policy_transaction 
         INNER JOIN policy 
           ON policy_transaction.policy_base = policy.policy_base 
              AND policy_transaction.policy_suffix = policy.policy_suffix 
         INNER JOIN policy_transaction_fee 
           ON policy_transaction.policy_base = policy_transaction_fee.policy_base 
              AND policy_transaction.policy_suffix = policy_transaction_fee.policy_suffix 
              AND policy_transaction.SEQUENCE = policy_transaction_fee.SEQUENCE 
WHERE    (policy_transaction.entered_date >= '2008-08-28') 
         AND (policy_transaction.transaction_type IN ('rn','np')) 
         AND (policy.status <> 'ca') 
         AND (policy.eft_flag = 'y') 
         AND (policy.current_flag = 'y') 
         AND (policy.installment_fees >= 10) 
         AND (policy_transaction_fee.fee_code = 'ins') 
GROUP BY policy.policy_base, 
         policy.policy_suffix, 
         policy.endorsement, 
         policy.company, 
         policy.subcompany

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-24 : 13:18:34

Is there a tool that automatically formats? I was cutting and pasting from management studio so I thought it was formatted, but yours looks much better... Did you do it by hand?

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-24 : 13:21:25

policy has one record to many records in policy_transaction. I need the information selected from policy as well as the highest value of sequence from policy_transaction so I can increment it by one. This will be the select portion of an insert select when i get it working.

The table are large but I could post the create table scripts if that would help. They would just be rather large.

Thank you for the reply.

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-24 : 17:00:49

I think i have it working, no thanks to you guys.

I have manually verified a few records and it seems to work. I did not know you could do a second select in the select columns. Neat stuff.

Could someone look over this and tell me if it looks ok as far as proper sql. I don't want it to run improperly as this will be used to update production data.

SELECT policy.policy_base,
policy.policy_suffix,
policy.endorsement,
policy.company,
policy.subcompany,
(
SELECT MAX(policy_transaction.sequence)
FROM policy_transaction
WHERE policy_transaction.policy_base = policy.policy_base
) + 1 as sequence
FROM policy_transaction
INNER JOIN policy
ON policy_transaction.policy_base = policy.policy_base
AND policy_transaction.policy_suffix = policy.policy_suffix
INNER JOIN policy_transaction_fee
ON policy_transaction.policy_base = policy_transaction_fee.policy_base
AND policy_transaction.policy_suffix = policy_transaction_fee.policy_suffix
WHERE (policy_transaction.entered_date >= '2008-08-28')
AND (policy_transaction.transaction_type IN ('rn','np'))
AND (policy.status <> 'ca')
AND (policy.eft_flag = 'y')
AND (policy.current_flag = 'y')
AND (policy.installment_fees >= 10)
AND (policy_transaction_fee.fee_code = 'ins')
GROUP BY policy.policy_base,
policy.policy_suffix,
policy.endorsement,
policy.company,
policy.subcompany

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-25 : 04:23:53

quote:
Originally posted by chedderslam

Is there a tool that automatically formats? I was cutting and pasting from management studio so I thought it was formatted, but yours looks much better... Did you do it by hand?

just look in google for sql formatter...
http://www.dpriver.com/pp/sqlformat.htm

and place your code between [ code ]here comes your code[ /code] but without blank space in brackets.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-25 : 10:44:29

quote:
Originally posted by webfred

quote:
Originally posted by chedderslam

Is there a tool that automatically formats? I was cutting and pasting from management studio so I thought it was formatted, but yours looks much better... Did you do it by hand?

just look in google for sql formatter...
http://www.dpriver.com/pp/sqlformat.htm

and place your code between [ code ]here comes your code[ /code] but without blank space in brackets.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Great tip! Thanks!

baburk
Posting Yak Master

108 Posts

Posted - 2009-04-27 : 01:04:08

quote:
Originally posted by chedderslam

Is there a tool that automatically formats? I was cutting and pasting from management studio so I thought it was formatted, but yours looks much better... Did you do it by hand?

USE http://www.sqlinform.com/ For formatting your query

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-04-27 : 03:15:56

quote:
I need the maximum value of the column sequence to increment it by one. Eventually this select will be used in an insert select statement.

Very bad idea and will not actually work in a multi-user environment.
Try using an identity column - that's what they are for. (Yes you will get gaps, no you can't prevent that without serialising every transaction, meaning zero scalability).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources