Loop to find similar data - 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 2008 Forums

Transact-SQL (2008)

Loop to find similar data

Author

Topic

atmikey
Starting Member

3 Posts

Posted - 2012-03-19 : 10:15:16

Hi,

I have some data that looks like this:


ID  Doc Name  ProcID  ServiceDate  Location  PatID  Modifer
1    Bill     35560    2-23	    10	     100	No			
2    John     35560    2-23	    10	     100	Yes
3    Jake     99215    1-16	    15	     115        No
4    Lisa     01576    1-10         12	     111        No
4    Lisa     99217    1-9          12       210        No

So basically I want to identify ID 1 & 2 (Bill & John) as having performed the same procedure on the same day, on the same patient, at the same location. How do I loop through the data to match the two together without actually knowing the exact procedure or date? For example, I need it to find the data without me doing something like where ProcID = "35560", because I won't know what procedure has been done? Does anyone have an idea how to do this?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-19 : 10:41:24

Group by all the columns for which you want to aggregate on as in:

SELECT
	DocName,
	ProcId,
	ServiceDate,
	Location,
	Patient
FROM
	YourTable
GROUP BY 
	DocName,
	ProcId,
	ServiceDate,
	Location,
	Patient
HAVING
	COUNT(*) > 1;

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-03-19 : 10:55:23

dang. i read way too much into it.

declare @yak table(ID int, [Doc Name] varchar(15), ProcID varchar(5), ServiceDate varchar(5), Location int, PatID INT, Modifier varchar(3))

insert into @yak

values (1  ,  'Bill' ,    '35560' ,   '2-23'	,    10	   , 100	,'No'	)		
,(2  ,  'John'  ,  '35560' ,  '2-23'	 ,   10	  ,   100,	'Yes')
,(3  ,  'Jake'  ,   '99215'  ,  '1-16'	  ,  15	  ,   115  ,      'No')
,(4  ,  'Lisa'  ,   '01576'  ,  '1-10'   ,     12	,     111 ,       'No')
,(4  ,  'Lisa'  ,   '99217'  ,  '1-9'    ,     12  ,     210 ,       'No')



SELECT 
	y.ID, y.[Doc Name],y.ProcID,y.ServiceDate,y.Location,y.PatID,y.Modifier
FROM
	@yak y
INNER JOIN
(select 
	ProcID,ServiceDate,Location,PatID 
	,ROW_NUMBER() OVER(PARTITION BY ProcID,ServiceDate,Location,PatID ORDER BY ID ) AS RN
FROM	
	@yak) x
ON
	y.ProcID = x.ProcID and y.ServiceDate = x.ServiceDate and y.Location = x.Location and y.PatID = x.PatID 
WHERE 
	x.RN > 1

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-19 : 11:00:19

quote:
Originally posted by DonAtWork

dang. i read way too much into it.

I read way too little into it. At the very least, OP should not be grouping by DocName, but then you don't know who these good doctors who stepped on each other are:

SELECT
	DocName,
	ProcId,
	ServiceDate,
	Location,
	Patient
FROM
	YourTable
GROUP BY 
	DocName,
	ProcId,
	ServiceDate,
	Location,
	Patient
HAVING
	COUNT(*) > 1;

atmikey
Starting Member

3 Posts

Posted - 2012-03-19 : 12:32:37

Thanks for the ideas!!!
I guess I didn't explain it completely though... What I'm really trying to do is that if John and Bill were at the same location, performed the same procedure, and had the same patient, on the same day; but the modifier is yes on one and no on the other then I would need to do some calculations on their accounts. So I would need a way to identify them somehow.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-19 : 12:36:14

I don't read...I only cut and paste


SELECT * FROM yourTable o
  WHERE EXISTS (
		  SELECT *
		    FROM YourTable i
		   WHERE o.ProcId   = i.ProcId   AND o.ServiceDate = i.ServiceDate
		     AND o.Location = i.Location AND o.Patient     = i.Patient
		GROUP BY ProcId,ServiceDate,Location,Patient
		  HAVING COUNT(*) > 1;
		)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

atmikey
Starting Member

3 Posts

Posted - 2012-03-19 : 12:47:45

I should also mention that these fields aren't coming from one table, but from many tables that are joined.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-19 : 12:49:08

Well, the for info you give us the better

Read the hint link in my sig and post what it asks for

We can only read minds so much

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-19 : 14:16:12

quote:
Originally posted by atmikey

I should also mention that these fields aren't coming from one table, but from many tables that are joined.

that doesnt make a difference
instead of single table query use query involving all table joins along with EXISTS in previous suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources