using MAX Aggregate Select - 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

using MAX Aggregate Select

Author

Topic

ruci1225
Starting Member

3 Posts

Posted - 2010-12-09 : 21:13:38

Good Evening,
I have a table that has multiple Claimnumber results due to mutiple TRC and different ReportDate
I am trying to use the MAX to create an aggregate select statement to report only the latest ReportDate for a certain TRC.
The query is below.
I am getting duplicates and I cannot figure out why.

SELECT DISTINCT MemEnrollinfo_Disenroll2009_2010.mem_claimnum, dbo_tbl_memcmstrans_reply_weekly.ClaimNum, dbo_tbl_memcmstrans_reply_weekly.Surname, dbo_tbl_memcmstrans_reply_weekly.FirstName, dbo_tbl_memcmstrans_reply_weekly.ContractNum, dbo_tbl_memcmstrans_reply_weekly.TransReplyCode, dbo_tbl_memcmstrans_reply_weekly.TransTypeCode, dbo_tbl_memcmstrans_reply_weekly.EffectiveDate, Max(dbo_tbl_memcmstrans_reply_weekly.reportDate) AS MaxOfreportDate, MemEnrollinfo_Disenroll2009_2010.rediscan_effdate

FROM MemEnrollinfo_Disenroll2009_2010 INNER JOIN dbo_tbl_memcmstrans_reply_weekly ON MemEnrollinfo_Disenroll2009_2010.mem_claimnum = dbo_tbl_memcmstrans_reply_weekly.ClaimNum

GROUP BY MemEnrollinfo_Disenroll2009_2010.mem_claimnum, dbo_tbl_memcmstrans_reply_weekly.ClaimNum, dbo_tbl_memcmstrans_reply_weekly.Surname, dbo_tbl_memcmstrans_reply_weekly.FirstName, dbo_tbl_memcmstrans_reply_weekly.ContractNum, dbo_tbl_memcmstrans_reply_weekly.TransReplyCode, dbo_tbl_memcmstrans_reply_weekly.TransTypeCode, dbo_tbl_memcmstrans_reply_weekly.EffectiveDate, MemEnrollinfo_Disenroll2009_2010.rediscan_effdate

HAVING (((dbo_tbl_memcmstrans_reply_weekly.TransReplyCode)="014") AND ((dbo_tbl_memcmstrans_reply_weekly.EffectiveDate)>="20081231"));

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-09 : 23:06:22

Check datetime columns. Many times there is gap of milliseconds between the two records and in that case both records appear.

ruci1225
Starting Member

3 Posts

Posted - 2010-12-10 : 10:07:39

Thank You, I did not think of that.
I did find the issue and it was that the ContractNum field also needed a MAx as this field was causing duplicates.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-10 : 23:33:15

quote:
Originally posted by ruci1225

Thank You, I did not think of that.
I did find the issue and it was that the ContractNum field also needed a MAx as this field was causing duplicates.

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources