SELECT STATEMENT - 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 STATEMENT

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-10-22 : 07:36:58

Ok friends

I am really confused with the following and your help is very much appreciated..

I have written a select statement

SELECT POLICY_NUMBER,SEQUENCE_NUMBER,CLIENT_NUMBER,POLICY_DATE_TIME FROM MPL_EXPOSURE WHERE CLIENT_NUMBER = 1084 AND POLICY_NUMBER NOT LIKE 'Q%' ORDER BY POLICY_DATE_TIME DESC

the following result

TX 11727 3 1084 2012-05-03 10:38:26.000
TX 11727 3 1084 2012-01-26 11:47:08.000
TX 11727 3 1084 2012-01-23 10:20:25.000
TX 11727 3 1084 2011-12-05 13:40:13.000
TX 11727 3 1084 2011-12-05 11:13:24.000
TX 11727 3 1084 2011-11-28 13:51:55.000
TX 11727 3 1084 2011-05-10 08:06:43.000
TX 11727 3 1084 2011-04-27 08:59:20.000
TX 11727 3 1084 2011-02-07 13:16:48.000

then I wrote this script

SELECT M.POLICY_NUMBER,M.CLIENT_NUMBER,M.POLICY_DATE_TIME,M.SEQUENCE_NUMBER,P.SEQUENCE_NUMBER,P.WRITTEN_PREMIUM,P.TERMINATION_DATE
FROM MPL_EXPOSURE M
LEFT OUTER JOIN PREMIUM_DETAIL P ON M.POLICY_NUMBER = P.POLICY_NUMBER AND M.SEQUENCE_NUMBER = P.SEQUENCE_NUMBER
AND P.POLICY_DATE_TIME = (SELECT MAX (POLICY_DATE_TIME) FROM PREMIUM_DETAIL P1 WHERE P.POLICY_NUMBER = P1.POLICY_NUMBER AND P.TERMINATION_DATE IS NULL)
WHERE M.POLICY_NUMBER = 'TX 11727'
AND M.POLICY_DATE_TIME = (SELECT MAX (POLICY_DATE_TIME) FROM MPL_EXPOSURE M2 WHERE M.POLICY_NUMBER = M2.POLICY_NUMBER)
AND M.CLIENT_NUMBER = 1084

and it does not generate anything please note that I have used both INNER JOIN as well and the same result but if I comment out the last line
AND M.CLIENT_NUMBER = 1084

then I get the following

TX 11727 9036 2012-10-22 05:50:29.000 11 11 6303.00 NULL
TX 11727 9036 2012-10-22 05:50:29.000 11 11 .00 NULL
TX 11727 9036 2012-10-22 05:50:29.000 11 11 500.00 NULL
TX 11727 1384 2012-10-22 05:50:29.000 10 10 6303.00 NULL
TX 11727 1384 2012-10-22 05:50:29.000 10 10 .00 NULL
TX 11727 1384 2012-10-22 05:50:29.000 10 10 500.00 NULL
TX 11727 1866 2012-10-22 05:50:29.000 8 8 6303.00 NULL
TX 11727 1866 2012-10-22 05:50:29.000 8 8 .00 NULL
TX 11727 1866 2012-10-22 05:50:29.000 8 8 500.00 NULL
TX 11727 1258 2012-10-22 05:50:29.000 6 6 6303.00 NULL
TX 11727 1258 2012-10-22 05:50:29.000 6 6 .00 NULL
TX 11727 1258 2012-10-22 05:50:29.000 6 6 500.00 NULL
TX 11727 2624 2012-10-22 05:50:29.000 2 2 2721.00 NULL

so what is going on?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-22 : 07:52:26

Include client number also in the subquery in the where clause:

....
WHERE  M.POLICY_NUMBER = 'TX 11727'
       AND M.POLICY_DATE_TIME = (
               SELECT MAX(POLICY_DATE_TIME)
               FROM   MPL_EXPOSURE M2
               WHERE  M.POLICY_NUMBER = M2.POLICY_NUMBER
				AND M.CLIENT_NUMBER = M2.CLIENT_NUMBER
           )
       AND M.CLIENT_NUMBER = 1084

divan
Posting Yak Master

153 Posts

Posted - 2012-10-22 : 09:40:24

sunitabeck what you suggested worked and here is the result

TX 11727 1084 2012-05-03 10:38:26.000 3 NULL NULL NULL NULL

but if you notice the P.SEQUENCE_NUMBER,P.WRITTEN_PREMIUM,P.TERMINATION_DATE, P.POLICY_DATE_TIME
are null and they are from the PREMIUM_DETAIL TABLE

it looks like the script is ignoring the conditions for PREMIUM_DETAIL

SELECT M.POLICY_NUMBER,M.CLIENT_NUMBER,M.POLICY_DATE_TIME,M.SEQUENCE_NUMBER,P.SEQUENCE_NUMBER,P.WRITTEN_PREMIUM,P.TERMINATION_DATE, P.POLICY_DATE_TIME
FROM MPL_EXPOSURE M
LEFT OUTER JOIN PREMIUM_DETAIL P ON M.POLICY_NUMBER = P.POLICY_NUMBER AND M.SEQUENCE_NUMBER = P.SEQUENCE_NUMBER
AND P.POLICY_DATE_TIME = (SELECT MAX (POLICY_DATE_TIME) FROM PREMIUM_DETAIL P1 WHERE P.POLICY_NUMBER = P1.POLICY_NUMBER )---AND P.TERMINATION_DATE IS NULL)
WHERE M.POLICY_NUMBER = 'TX 11727'
AND M.POLICY_DATE_TIME = (SELECT MAX (POLICY_DATE_TIME) FROM MPL_EXPOSURE M2 WHERE M.POLICY_NUMBER = M2.POLICY_NUMBER AND M.CLIENT_NUMBER = M2.CLIENT_NUMBER)
AND M.CLIENT_NUMBER = 1084

sorry I am confused.. Can you please shed some light

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-22 : 11:59:54

You probably need to join on SEQUENCE_NUMBER also. I am guessing - if you have some sample data, that would make it clearer:

SELECT M.POLICY_NUMBER,
       M.CLIENT_NUMBER,
       M.POLICY_DATE_TIME,
       M.SEQUENCE_NUMBER,
       P.SEQUENCE_NUMBER,
       P.WRITTEN_PREMIUM,
       P.TERMINATION_DATE,
       P.POLICY_DATE_TIME
FROM   MPL_EXPOSURE M
       LEFT OUTER JOIN PREMIUM_DETAIL P
            ON  M.POLICY_NUMBER = P.POLICY_NUMBER
            AND M.SEQUENCE_NUMBER = P.SEQUENCE_NUMBER
            AND P.POLICY_DATE_TIME = (
                    SELECT MAX(POLICY_DATE_TIME)
                    FROM   PREMIUM_DETAIL P1
                    WHERE  P.POLICY_NUMBER = P1.POLICY_NUMBER
			AND P.SEQUENCE_NUMBER = P1.SEQUENCE_NUMBER
                )---AND P.TERMINATION_DATE IS NULL)
WHERE  M.POLICY_NUMBER = 'TX 11727'
       AND M.POLICY_DATE_TIME = (
               SELECT MAX(POLICY_DATE_TIME)
               FROM   MPL_EXPOSURE M2
               WHERE  M.POLICY_NUMBER = M2.POLICY_NUMBER
                      AND M.CLIENT_NUMBER = M2.CLIENT_NUMBER
           )
       AND M.CLIENT_NUMBER = 1084

Don't you love the way I formatted your code? Makes it a lot more readable. You can keep white spaces if you wrap the code in [code] and [/code] tags.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources