Author |
Topic |
divan
Posting Yak Master
153 Posts |
Posted - 2013-04-04 : 08:04:42
|
I have the following script SELECT E.CLIENT_NUMBER,P.AGENCY_ID---,A.AGENCY_NAME 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 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 < '6/1/13' AND P.POL_EXP_DATE > '4/4/13' 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'-- AND EXECUTIVE_EXPORT.STATE = M.RATING_STATE),0)ORDER BY AGENCY_IDwhich gives me a result like the following3581 1 KY3679 1 KY8584 1 TXA&A6398 1 TXA&A2030 1 TXASA14580 1 TXBIP258 1 TXCDL2669 1 TXCOM2681 1 TXCOM2692 1 TXCOM2711 1 TXCOM2745 1 TXCOMInstead of listing all the above is there a way I can get a count for each agency id |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-04 : 08:08:24
|
provide headers/column names of the result set e.g. 3581 1 KYwaht is 3581? What is 1? what is KY? additionally your select has two columns in list where as the given result shows to be of three columns. CheersMIK |
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2013-04-04 : 08:21:24
|
3581 is the E.CLIENT_NUMBER and 1 KY is P.Agency ID.. So anything with a 1 in front of it is AGENCY_id the the other E>CLIENT_NUMBER so there is only two columns listed but it was confusing sorry about that.. SO |
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2013-04-04 : 09:14:28
|
someone please help..... |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-04 : 09:37:52
|
may be this?SELECT COUNT(E.CLIENT_NUMBER),P.AGENCY_ID--, A.AGENCY_NAMEFROM 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 NULLWHERE 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 < '6/1/13'AND P.POL_EXP_DATE > '4/4/13'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'-- AND EXECUTIVE_EXPORT.STATE = M.RATING_STATE),0)GROUP BY P.AGENCY_ID--, A.AGENCY_NAMEORDER BY AGENCY_ID |
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2013-04-04 : 10:12:33
|
No this gives me total count of Client_number I need total of each different AGENCY_ID |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-04 : 10:40:28
|
What are you trying to count against an Agency_ID? is it number of clients .. or something else? eleborate it .. If possible come up with sample data (in the form of Insert statements going into some temporay table) and desired output based on that sample data. So that we can use the sample data and suggest a desired query for it.CheersMIK |
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2013-04-04 : 11:19:25
|
When I run the script I get the followingCLIENT_NUMBER AGENCY_ID3581 1 KY3679 1 KY8584 1 TXA&A6398 1 TXA&A2030 1 TXASA14580 1 TXBIP258 1 TXCDL2669 1 TXCOM2681 1 TXCOM2692 1 TXCOM2711 1 TXCOM2745 1 TXCOMso I want to know how many CLIENT_NUMBER per AGENCY_ID Based on the info I have provided I need to see a result like 2 1 KY where the 2 is the number of client number assigned to AGENCY_ID 1 KY3 1 TXA&A where the 3 is the number of client number assigned to AGENCY_ID 1 TXA&A and so on.... |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-04 : 11:41:21
|
I think then what Bandi suggested should have worked. SELECT Count(Client_Number),Agency_IDFROM Table --All joining/condition logic goes hereGroup By Agency_IDCheersMIK |
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2013-04-04 : 11:59:30
|
Thanks that worked... |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-05 : 00:57:42
|
what i posted is also same.. I think you doesn't have AGENCY_NAME per AGENCY_ID |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-05 : 01:20:32
|
quote: Originally posted by bandi may be this?SELECT COUNT(E.CLIENT_NUMBER),P.AGENCY_ID--, A.AGENCY_NAMEFROM 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 NULLWHERE 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 < '6/1/13'AND P.POL_EXP_DATE > '4/4/13'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'-- AND EXECUTIVE_EXPORT.STATE = M.RATING_STATE),0)GROUP BY P.AGENCY_ID--, A.AGENCY_NAMEORDER BY AGENCY_ID
Always try to pass date values in umambiguos formatsusing format like above can cause dates to be interpreted differently by the server depending on its language settingsfor ex '6/1/13' can mean 6th Jan 2013 or 1st June 2013 depending on server regional settingmake sure you read thishttp://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.htmlto avoid such issues make it like..P.POL_EFF_DATE < '20130601'AND P.POL_EXP_DATE > '20130404'... ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|