SELECT STATEMENT NOT WORKING - 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

SELECT STATEMENT NOT WORKING

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2013-04-11 : 12:57:06

I have a table CLAIM_FNCL_TRANS that has the following fields

OCCUR_NUMBER ACCOUNT_CD
18 E
18 I
18 I
18 E
18 E
18 E
18 E
18 E

And I run the following script

DROP TABLE #TEMP1
SELECT C.OCCUR_NUMBER
INTO #TEMP1---,O.OCCUR_STATUS_CD
FROM CLAIM_FNCL_TRANS C
GROUP BY C.OCCUR_NUMBER HAVING MAX (C.ACCOUNT_CD) = 'I'
SELECT * FROM #TEMP1

and Occur_number 18 does not get selected..

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-11 : 13:51:57

Say what?

DECLARE @CLAIM_FNCL_TRANS TABLE (OCCUR_NUMBER INT, ACCOUNT_CD CHAR(1))
INSERT @CLAIM_FNCL_TRANS VALUES
(18, 'E'),
(18, 'I'),
(18, 'I'),
(18, 'E'),
(18, 'E'),
(18, 'E'),
(18, 'E'),
(18, 'E')


SELECT C.OCCUR_NUMBER
FROM @CLAIM_FNCL_TRANS C
GROUP BY C.OCCUR_NUMBER HAVING MAX (C.ACCOUNT_CD) = 'I'

-- Result
OCCUR_NUMBER
------------
18

(1 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-12 : 04:37:51

do you've any other values also present for ACCOUNT_CD which you've not shown us?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources