Count - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Count

Author

Topic

madscientist
Starting Member

30 Posts

Posted - 2007-12-19 : 14:40:36

Hi everyone,

I need help with a query I am trying to code. The query selects 5 fields from the OTHER_STATES_COVERAGE_RECORD table including a COUNT field:

SELECT [POLICY_NUMBER], [BOOK_FLAG_DATE], [TRANS_CODE],
COUNT([COVERAGE_STATE]) AS [COUNT], [STATE]
INTO OSC_TEMP
FROM OTHER_STATES_COVERAGE_RECORD
GROUP BY [POLICY_NUMBER], [BOOK_FLAG_DATE], [TRANS_CODE], [STATE]

It creates the table OSC_TEMP which looks like this:
[POLICY_NUMBER] [BOOK_FLAG_DATE] [TRANS_CODE] [COUNT] [STATE]
ORX-WC-0010003-0 9/25/2006 6:47:3 14 1 37
ORX-WC-0010004-0 7/16/2007 7:3:59 14 1 31
ORX-WC-0010004-0 7/6/2006 16:12:35 01 1 31
ORX-WC-0010004-0 7/6/2006 16:12:35 01 1 32

The issue is that I want the COUNT field to increase by +1 when the POLICY_NUMBER, BOOK_FLAG_DATE, TRANS_CODE FIELD are equal BUT the STATE field is different.

So for the above example I want it to look like the following:
[POLICY_NUMBER] [BOOK_FLAG_DATE] [TRANS_CODE] [COUNT] [STATE]
ORX-WC-0010003-0 9/25/2006 6:47:3 14 1 37
ORX-WC-0010004-0 7/16/2007 7:3:59 14 1 31
ORX-WC-0010004-0 7/6/2006 16:12:35 01 1 31
ORX-WC-0010004-0 7/6/2006 16:12:35 01 2 32

Thank you for the help. I really appreciate it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 15:11:29

SELECT [POLICY_NUMBER],
[BOOK_FLAG_DATE],
[TRANS_CODE],
ROW_NUMBER() OVER (PARTITION BY [POLICY_NUMBER], [BOOK_FLAG_DATE], [TRANS_CODE] ORDER BY [STATE]) AS [COUNT],
[STATE]
FROM OTHER_STATES_COVERAGE_RECORD

E 12°55'05.25"
N 56°04'39.16"

madscientist
Starting Member

30 Posts

Posted - 2007-12-19 : 16:00:54

Thank you Peso so much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources