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.
Author |
Topic |
divan
Posting Yak Master
153 Posts |
Posted - 2012-09-28 : 09:47:10
|
I have the following script that list rows which have county and state info.How do I modify the script that it will give me total for each county within each state.SELECT DISTINCT E.RATING_COUNTY, E.POLICY_NUMBER,E.CLIENT_NUMBER, E1.DESCRIPTION AS COUNTY, E.RATING_STATE,E.EXPOSURE_TYPE,E.SLOT FROM POLICY P INNER JOIN MPL_DETAIL M ON P.POLICY_NUMBER = M.POLICY_NUMBER AND P.POLICY_DATE_TIME = M.POLICY_DATE_TIME INNER JOIN REGISTER R ON P.POLICY_NUMBER = R.POLICY_NUMBER AND P.POLICY_DATE_TIME = R.POLICY_DATE_TIME INNER JOIN MPL_EXPOSURE E ON R.POLICY_NUMBER = E.POLICY_NUMBER AND R.POLICY_DATE_TIME = E.POLICY_DATE_TIME AND E.TERMINATION_DATE IS NULL INNER JOIN EDIT_LONG_CODE E1 ON E1.TBNAME = 'MPL_EXPOSURE' AND E1.NAME = 'RATING_COUNTY' AND E1.CODE = E.RATING_COUNTY WHERE R.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME) FROM REGISTER R2 WHERE R.POLICY_NUMBER = R2.POLICY_NUMBER AND R.PORTFOLIO_SET = R2.PORTFOLIO_SET AND R2.CHECK_OUT IS NULL) AND P.POL_EFF_DATE >= '9/1/11'-- AND P.POL_EXP_DATE > '9/1/11' AND ((E.EXPOSURE_TYPE = '1' AND E.TYPE_LMT <> '8') OR (E.EXPOSURE_TYPE = '7') OR (E.EXPOSURE_TYPE = '2' AND E.TYPE_LMT = '9')) AND R.STATUS_1 NOT IN ('4','6','7') AND LEFT(P.POLICY_NUMBER,1) <> 'Q' |
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-09-28 : 09:53:06
|
please post some of the actual results and the desired output1--------------------------Joins are what RDBMS's do for a living |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-28 : 10:13:59
|
sounds like thisSELECT E.RATING_COUNTY, E1.DESCRIPTION AS COUNTY, E.RATING_STATE,COUNT(*) AS RecordCountFROM POLICY PINNER JOIN MPL_DETAIL M ON P.POLICY_NUMBER = M.POLICY_NUMBER AND P.POLICY_DATE_TIME = M.POLICY_DATE_TIMEINNER JOIN REGISTER R ON P.POLICY_NUMBER = R.POLICY_NUMBERAND P.POLICY_DATE_TIME = R.POLICY_DATE_TIMEINNER JOIN MPL_EXPOSURE E ON R.POLICY_NUMBER = E.POLICY_NUMBERAND R.POLICY_DATE_TIME = E.POLICY_DATE_TIMEAND E.TERMINATION_DATE IS NULLINNER JOIN EDIT_LONG_CODE E1 ON E1.TBNAME = 'MPL_EXPOSURE' AND E1.NAME = 'RATING_COUNTY' AND E1.CODE = E.RATING_COUNTYWHERE R.POLICY_DATE_TIME = (SELECT MAX(POLICY_DATE_TIME)FROM REGISTER R2WHERE R.POLICY_NUMBER = R2.POLICY_NUMBERAND R.PORTFOLIO_SET = R2.PORTFOLIO_SETAND R2.CHECK_OUT IS NULL)AND P.POL_EFF_DATE >= '9/1/11'-- AND P.POL_EXP_DATE > '9/1/11'AND ((E.EXPOSURE_TYPE = '1' AND E.TYPE_LMT <> '8')OR (E.EXPOSURE_TYPE = '7')OR (E.EXPOSURE_TYPE = '2' AND E.TYPE_LMT = '9'))AND R.STATUS_1 NOT IN ('4','6','7')AND LEFT(P.POLICY_NUMBER,1) <> 'Q'GROUP BY E.RATING_COUNTY, E1.DESCRIPTION, E.RATING_STATE ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-09-28 : 10:14:04
|
Try this oneSELECT DISTINCT E.RATING_COUNTY, E.POLICY_NUMBER,E.CLIENT_NUMBER, E1.DESCRIPTION AS COUNTY, E.RATING_STATE,E.EXPOSURE_TYPE,E.SLOT,COUNT(distinct E.RATING_COUNTY) OVER( Partition by E.RATING_STATE) 'No. Of Countries'FROM POLICY P..................................--Chandu |
|
|
|
|
|
|
|