Group by/Having Clause - 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

Group by/Having Clause

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2011-09-08 : 09:32:43

Hi,

I have been trying to figure out this formula for a couple of days now and I can’t.

I need to be able to pull todays data, but then match it with the other data in the system (clm) to see if there are any duplicates field (clm_84b), but it has to have a different claim numbers associated with them (clm_id1).

Example

clm_id1 clm_84b clm_batch clm_rcvd
03143749 201107131133 00160354 08/10/2011
03177734 201107131133 00162808 09/06/2011

This is what I have so far. This is pulling claims data that is not in my table #orgclaim.

drop table #orgclaim

Select clm_id1 as ClaimID, clm_84b as OrgClaimID,clm_batch as Batch INTO #OrgClaim from impact.dbo.clm
Where clm_clir = '00001674'and clm_rcvd = ‘09/06/2011’

select clm_id1, clm_84b, clm_batch from impact.dbo.clm
where clm_84b in (

Select
clm_84b

From impact.dbo.clm
Where clm_clir = '00001674'
group by clm_84b
HAVING COUNT (clm_84b) > 1)

ORDER BY clm_84b

Thanks for you help in advance!

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2011-09-08 : 10:15:23

I was able to figure it out....

drop table #orgclaim

Select clm_id1 as ClaimID, clm_84b as OrgClaimID,clm_batch as Batch INTO #OrgClaim from impact.dbo.clm
Where clm_clir = '00001674'and clm_rcvd = '09/06/2011'---'09/06/2011'
----(clm_rcvd > GETDATE() - 2)---yesterdays date

select clm_id1, clm_84b, clm_batch,clm_rcvd from impact.dbo.clm Right Join #orgclaim on clm_84b = OrgClaimID
where clm_clir = '00001674' and clm_84b in (

Select
clm_84b
---OrgClaimID

From impact.dbo.clm ----#orgclaim
Where clm_clir = '00001674'
group by clm_84b --OrgClaimID

HAVING COUNT (clm_84b) > 1) ---OrgClaimID

ORDER BY clm_84b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources