Results 1 to 2 of 2

Thread: SQL puzzle

  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi there,

    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.

    Code:
    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
                           )
    I keep coming back with zero results even though I know anomalies exist. I've tried a number of variations, all to no avail. I have another method of extraction which uses a temporary table and proves the anomalies exist, but what I suppose I'm after is an explanation as to why the above doesn't work and any pointers that will make it work!

    Cheers,

    Niven

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    Niven,

    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,

    Jules

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •