Select MAX not cooperating - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select MAX not cooperating

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-11-09 : 09:06:06

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 09:54:08

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