SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divan
Posting Yak Master

153 Posts

Posted - 04/04/2013 :  08:04:42  Show Profile  Reply with Quote
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_ID

which gives me a result like the following
3581 1 KY
3679 1 KY
8584 1 TXA&A
6398 1 TXA&A
2030 1 TXASA
14580 1 TXBIP
258 1 TXCDL
2669 1 TXCOM
2681 1 TXCOM
2692 1 TXCOM
2711 1 TXCOM
2745 1 TXCOM

Instead of listing all the above is there a way I can get a count for each agency id

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/04/2013 :  08:08:24  Show Profile  Reply with Quote
provide headers/column names of the result set e.g.


3581 1 KY

waht 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.

Cheers
MIK

Edited by - MIK_2008 on 04/04/2013 08:11:58
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 04/04/2013 :  08:21:24  Show Profile  Reply with Quote
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
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 04/04/2013 :  09:14:28  Show Profile  Reply with Quote
someone please help.....
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 04/04/2013 :  09:37:52  Show Profile  Reply with Quote
may be this?
SELECT COUNT(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)
GROUP BY P.AGENCY_ID--, A.AGENCY_NAME
ORDER BY AGENCY_ID

Edited by - bandi on 04/05/2013 00:55:44
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 04/04/2013 :  10:12:33  Show Profile  Reply with Quote
No this gives me total count of Client_number I need total of each different AGENCY_ID
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/04/2013 :  10:40:28  Show Profile  Reply with Quote
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.

Cheers
MIK
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 04/04/2013 :  11:19:25  Show Profile  Reply with Quote
When I run the script I get the following

CLIENT_NUMBER AGENCY_ID
3581 1 KY
3679 1 KY
8584 1 TXA&A
6398 1 TXA&A
2030 1 TXASA
14580 1 TXBIP
258 1 TXCDL
2669 1 TXCOM
2681 1 TXCOM
2692 1 TXCOM
2711 1 TXCOM
2745 1 TXCOM

so 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 KY
3 1 TXA&A where the 3 is the number of client number assigned to AGENCY_ID 1 TXA&A and so on....
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/04/2013 :  11:41:21  Show Profile  Reply with Quote
I think then what Bandi suggested should have worked.

SELECT Count(Client_Number),Agency_ID
FROM Table --All joining/condition logic goes here
Group By Agency_ID

Cheers
MIK

Edited by - MIK_2008 on 04/04/2013 11:42:01
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 04/04/2013 :  11:59:30  Show Profile  Reply with Quote
Thanks that worked...
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 04/05/2013 :  00:57:42  Show Profile  Reply with Quote
what i posted is also same.. I think you doesn't have AGENCY_NAME per AGENCY_ID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/05/2013 :  01:20:32  Show Profile  Reply with Quote
quote:
Originally posted by bandi

may be this?
SELECT COUNT(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)
GROUP BY P.AGENCY_ID--, A.AGENCY_NAME
ORDER BY AGENCY_ID


Always try to pass date values in umambiguos formats

using format like above can cause dates to be interpreted differently by the server depending on its language settings

for ex '6/1/13' can mean 6th Jan 2013 or 1st June 2013 depending on server regional setting

make sure you read this

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

to avoid such issues make it like

..
P.POL_EFF_DATE < '20130601'
AND P.POL_EXP_DATE > '20130404'
...


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000