Results 1 to 2 of 2
Thread: SQL puzzle
2010-09-17, 06:11 #1
- Join Date
- Nov 2003
- London, Gtr London, England
- Thanked 0 Times in 0 Posts
I have a total mind block on a piece of SQL I was hoping to use.
The essence is I have two tables I need to reference one called Policy the other PolicySection
Policy hold a single record for a policy whereas PolicySection holds multiple transactions (called Endorsements) for said policy.
Policy has a field called lPolicyActivityKey which along with lPolicyKey links to PolicySection.
The Activity Key gives the current status of the policy. The problem we have is that an endorsement has two states, provisional or endorsed. When an Endorsement is entered onto our main system as provisional it gets posted into policysection with a new acitivity key, but this key does not get updated in the policy table until the Endorsement is endorsed (i.e. it is not deemed to be live until endorsed).
We need to keep a check on these provisional endorsements and generate exception reports for management etc.
I was hoping to use the following code, or something like it, to extract the records from policy which don't have a matching record in policysection, because the activity key in policy does not match up with the max policy activity in policysection.
Select p.lpolicykey, p.lActivePolicyActivityKey from policy p where not exists (select max(lPolicyActivityKey), lpolicykey from dbo.PolicySection where lpolicykey = p.lpolicykey and lPolicyActivityKey = p.lActivePolicyActivityKey group by lpolicykey )
2010-09-17, 08:31 #2
- Join Date
- Sep 2002
- Hastings, Sussex, England
- Thanked 1 Time in 1 Post
the traditional way to 'find unmatched' is to link the two tables with an equals join, then set a criteria on the second table primary key of 'is null'. This returns records from the first table without a matching record in the second table. Cominig from a Microsoft Access background with a smattering of SQL Server, this is the approach I'd take. The 'not in (sql statement)' has its uses but at first glance I would not have said this was one of them. By grouping and using a max(), you seem to be muddying the waters.
Got to rush, hope this helps a bit,