selecting MAX - 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

selecting MAX

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-08-22 : 10:59:24

I have written the following script to extract certain records from various tables.. The script is working fine except when I add the following two lines

1) LEFT OUTER JOIN TABLE4 E4 ON M.C_NUMBER = E4.NAME_AND_ADDRESS AND E4.IDEN= '01Y' AND E4.NUMBER NOT LIKE 'Q%'

2) OR (E4.DATE_TIME = (SELECT MAX (DATE_TIME) FROM TABLE4 E5 WHERE E5.NUMBER = E4.NUMBER) AND E4.IDENTIFIER = '01Y' AND E4.DATE_5 BETWEEN '1/1/12' AND '7/31/12' AND E4.NUMBER NOT LIKE '%Q%')

what happens when I add these two lines one it doe not seleCt the max record TABLE4 and two it does not exclude the records that have their numbers starting with Q

SELECT DISTINCT P.NUMBER,
P.EFF_DATE,
P.EXP_DATE,
M.NUMBER,
M.SPECIALTY,
C.FNAME1,
C.LNAME1,
E3.DESCRIPTION AS EXPOSURE_TYPE,
M.RETRO_DT,
E1.DESCRIPTION AS RATING_COUNTY,
E2.DESCRIPTION AS LIMITS,
CASE WHEN M._TYPE = 3 THEN P.PREMIUM ELSE M.PREM END AS PREMIUM,
CASE WHEN P.NUMBER LIKE 'T%' OR P.NUMBER LIKE 'C%' THEN 'Texas' ELSE 'Mississippi' END AS ISSUED_STATE

FROM TABLEP P
INNER JOIN TABLEM M ON P.NUMBER = M.NUMBER AND P.ATE_TIME = M.DATE_TIME
INNER JOIN TABLEC C ON M.C_NUMBER = C.C_NUMBER
INNER JOIN TABLEE1 E1 ON E1.TBNAME = 'COUNTY' AND E1.NAME = 'COUNTY_CODE' AND E1.CODE = M.RATING_COUNTY
INNER JOIN TABLE2 E2 ON E2.TBNAME = 'EXPOSURE' AND E2.NAME = 'LMT' AND E2.CODE = M.LIAB_LMT
INNER JOIN TABLE3 E3 ON E3.TBNAME = 'EXPOSURE' AND E3.NAME = 'TYPE' AND E3.CODE = M.TYPE
LEFT OUTER JOIN TABLE4 E4 ON M.C_NUMBER = E4.NAME_AND_ADDRESS ----AND E4.IDEN= '01Y' AND E4.NUMBER NOT LIKE 'Q%'
WHERE P.DATE_TIME = (SELECT MAX (DATE_TIME) FROM TABLEP P2 WHERE P.NUMBER = P2.NUMBER) AND
M.TYPE NOT IN (3,5,7) AND
P.EXP_DATE >= '20120101'
AND P.EXP_DATE < '20120701' AND P.NUMBER NOT LIKE 'Q%'
AND NOT EXISTS
(
SELECT * FROM TABLEP P1
WHERE P.NUMBER = P1.NUMBER
AND P1.EXP_DATE >= '20120701'
)

OR (P.DATE_TIME = (SELECT MAX (DATE_TIME) FROM TABLEP P2 WHERE P.NUMBER = P2.NUMBER) AND P.TERM_DATE BETWEEN '1/1/12' AND '7/31/12' AND P.NUMBER NOT LIKE 'Q%')
OR (E4.DATE_TIME = (SELECT MAX (DATE_TIME) FROM TABLE4 E5 WHERE E5.NUMBER = E4.NUMBER) AND E4.IDENTIFIER = '01Y' AND E4.DATE_5 BETWEEN '1/1/12' AND '7/31/12' AND E4.NUMBER NOT LIKE '%Q%')
ORDER BY M.CLIENT_NUMBER, P.POLICY_NUMBER

your help is very much appreciated...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 11:03:39

very hard to tell reason without understanding whats going on and what you're trying to do with query

post some sample data and explain what you're trying to do. then we might be able to suggest

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

divan
Posting Yak Master

153 Posts

Posted - 2012-08-22 : 11:13:30

There are two tables TABLEP AND TABLE4 that have data that need to be extracted based on the criteria in the where clause... before I add the two lines that i have mentioned above the result is the first two lines below. But when I add the two lines which pertain to TABLE4 I get the duplicates and the numbers that start with Q

T 10927 2011-06-26 00:00:00.000 2012-06-26 00:00:00.000 216 260 C Physician 2000-06-26 00:00:00.000 Jefferson $200,000 / $600,000 29815.00 Texas
T 10485 2011-01-01 00:00:00.000 2012-01-01 00:00:00.000 241 400 S Physician 2005-01-01 00:00:00.000 Brewster $200,000 / $600,000 4535.00 Texas
Q0001681 2008-01-10 00:00:00.000 2009-01-10 00:00:00.000 270 120 Jenn Dong Physician 2004-01-10 00:00:00.000 Harris $200,000 / $600,000 6224.00 Mississippi
Q0004321 2009-01-10 00:00:00.000 2010-01-10 00:00:00.000 270 120 Jenn Dong Physician 2004-01-10 00:00:00.000 Harris $200,000 / $600,000 6224.00 Mississippi
Q0006775 2010-01-10 00:00:00.000 2011-01-10 00:00:00.000 270 120 Jenn Dong Physician 2004-01-10 00:00:00.000 Harris $200,000 / $600,000 6224.00 Mississippi
Q0008361 2010-09-01 00:00:00.000 2011-09-01 00:00:00.000 270 120 Jenn Dong Physician 2010-03-31 00:00:00.000 Montgomery Not Applicable 107.00 Mississippi
Q0009283 2011-01-10 00:00:00.000 2012-01-10 00:00:00.000 270 120 Jennifer Dong Physician 2004-01-10 00:00:00.000 Harris $200,000 / $600,000 7203.00 Mississippi
Q0010736 2011-09-01 00:00:00.000 2012-09-01 00:00:00.000 270 120 Jennifer Dong Physician 2010-03-31 00:00:00.000 Montgomery Not Applicable .00 Mississippi
Q0011384 2012-01-10 00:00:00.000 2013-01-10 00:00:00.000 270 120 Jennifer Dong Physician 2004-01-10 00:00:00.000 Harris $200,000 / $600,000 7203.00 Mississippi
T 10438 2006-01-10 00:00:00.000 2007-01-10 00:00:00.000 270 120 Jennifer Dong Physician 2004-01-10 00:00:00.000 Harris $200,000 / $600,000 6613.00 Texas
T 10438 2007-01-10 00:00:00.000 2008-01-10 00:00:00.000 270 120 Jennifer Dong Physician 2004-01-10 00:00:00.000 Harris $200,000 / $600,000 6224.00 Texas
T 10438 2010-01-10 00:00:00.000 2011-01-10 00:00:00.000 270 120 Jennifer Dong Physician 2004-01-10 00:00:00.000 Harris $200,000 / $600,000 7203.00 Texas
T 10438 2005-01-10 00:00:00.000 2006-01-10 00:00:00.000 270 120 Jennifer Dong Physician 2004-01-10 00:00:00.000 Harris $200,000 / $600,000 3199.00 Texas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 11:18:10

so what should be your intended result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

divan
Posting Yak Master

153 Posts

Posted - 2012-08-22 : 11:22:05

should be the following of course this is just a sample..

T 10927 2011-06-26 00:00:00.000 2012-06-26 00:00:00.000 216 260 C Physician 2000-06-26 00:00:00.000 Jefferson $200,000 / $600,000 29815.00 Texas
T 10485 2011-01-01 00:00:00.000 2012-01-01 00:00:00.000 241 400 S Physician 2005-01-01 00:00:00.000 Brewster $200,000 / $600,000 4535.00 Texas
T 10438 2006-01-10 00:00:00.000 2007-01-10 00:00:00.000 270 120 Jennifer Dong Physician 2004-01-10 00:00:00.000 Harris $200,000 / $600,000 6613.00 Texas

the numbers that start with a q should not be listed and the number T 10438 should only be listed once (due to the fact I am using the MAX statement)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 11:26:04

the reason it gives Q records is because of OR conditions. so if any of the below condition satisfies


..

P.DATE_TIME = (SELECT MAX (DATE_TIME) FROM TABLEP P2 WHERE P.NUMBER = P2.NUMBER) AND 
M.TYPE NOT IN (3,5,7) AND 
P.EXP_DATE >= '20120101'
AND P.EXP_DATE < '20120701' AND P.NUMBER NOT LIKE 'Q%' 
AND NOT EXISTS
(
SELECT * FROM TABLEP P1
WHERE P.NUMBER = P1.NUMBER 
AND P1.EXP_DATE >= '20120701' 
)
..

it will never look for last two conditions at all

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources