SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select MAX not cooperating
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divan
Posting Yak Master

153 Posts

Posted - 11/09/2012 :  09:06:06  Show Profile  Reply with Quote
I have this script.. When I run it as is I do not get any results, but when I comment out the

"AND E.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME) FROM ENDORSEMENT E1 WHERE E1.NAME_AND_ADDRESS = E.NAME_AND_ADDRESS) "

I get the result I get one record.. What am I missing in this


SELECT DISTINCT
POLICY_NUMBER,
NAME_AND_ADDRESS,
LTRIM(ISNULL(C.FNAME1,'') + ' '+ISNULL(C.INIT1,'') + ' ' + ISNULL(C.LNAME1,'') + ',' + ISNULL(C.SUFFIX1,'')) AS INSURED,
CASE WHEN C.CLIENT_TYPE = 'C' THEN 'GROUP' ELSE 'INDIVIDUAL' END AS GROUP_INDIVIDUAL,
EFFECTIVE_DATE,
TERMINATION_DATE,
DATE_1,
E3.DESCRIPTION AS TAIL_CODE,PREMIUM_1
FROM ENDORSEMENT E
INNER JOIN EDIT_SHORT_CODE E3 ON E3.TBNAME = 'E0025_0604' AND E3.NAME = 'TAIL_COV' AND E3.CODE = SHORT_CODE_1
INNER JOIN CLIENT C ON E.NAME_AND_ADDRESS = C.CLIENT_NUMBER
WHERE IDENTIFIER IN ('002','01g')
AND E.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME) FROM ENDORSEMENT E1 WHERE E1.NAME_AND_ADDRESS = E.NAME_AND_ADDRESS)
AND POLICY_NUMBER NOT LIKE 'Q%'
AND NAME_AND_ADDRESS = 178
ORDER BY NAME_AND_ADDRESS

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  09:54:08  Show Profile  Reply with Quote
The part you are commenting out is less restrictive than the outer query. For example, the max policy_date_time in the table ENDORSEMENT for a given NAME_AND_ADDRESS may have a different SHORT_CODE_1.

You can change the query like shown below:

select * from (

SELECT DISTINCT 
       POLICY_NUMBER,
       NAME_AND_ADDRESS,
       LTRIM(
           ISNULL(C.FNAME1, '') + ' ' + ISNULL(C.INIT1, '') + ' ' + ISNULL(C.LNAME1, '') 
           + ',' + ISNULL(C.SUFFIX1, '')
       )               AS INSURED,
       CASE 
            WHEN C.CLIENT_TYPE = 'C' THEN 'GROUP'
            ELSE 'INDIVIDUAL'
       END             AS GROUP_INDIVIDUAL,
       EFFECTIVE_DATE,
       TERMINATION_DATE,
       DATE_1,
       E3.DESCRIPTION  AS TAIL_CODE,
       PREMIUM_1,
       ROW_NUMBER() OVER (PARTION BY POLICY_NUMBER,POLICY_NUMBER,NAME_AND_ADDRESS
       	ORDER BY POLICY_DATE_TIME DESC) AS RN
FROM   ENDORSEMENT E
       INNER JOIN EDIT_SHORT_CODE E3
            ON  E3.TBNAME = 'E0025_0604'
            AND E3.NAME = 'TAIL_COV'
            AND E3.CODE = SHORT_CODE_1
       INNER JOIN CLIENT C
            ON  E.NAME_AND_ADDRESS = C.CLIENT_NUMBER
WHERE  IDENTIFIER IN ('002', '01g')
       --AND E.POLICY_DATE_TIME = (
       --        SELECT MAX(POLICY_DATE_TIME)
       --        FROM   ENDORSEMENT E1
       --        WHERE  E1.NAME_AND_ADDRESS = E.NAME_AND_ADDRESS
       --    )
       AND POLICY_NUMBER NOT LIKE 'Q%'
       AND NAME_AND_ADDRESS = 178
) s where RN = 1
ORDER BY
       NAME_AND_ADDRESS
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 11/09/2012 :  09:59:05  Show Profile  Reply with Quote


Sunitabeck i am getting the following "Incorrect syntax near 'PARTION'." and since I am not very familiar with this syntax can you please correct..
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
797 Posts

Posted - 11/09/2012 :  13:45:51  Show Profile  Reply with Quote
PARTION should be PARTITION...it is a typo
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  13:57:35  Show Profile  Reply with Quote
Thanks Jeff!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000