Find similer entries - 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)

Find similer entries

Author

Topic

learntsql

524 Posts

Posted - 2011-07-21 : 02:39:57

Hi All,

DECLARE @Sample TABLE
(
ID INT,
Person VARCHAR(10),
date DATE,
status VARCHAR(10)
)
INSERT @Sample
VALUES (1, 'p1', '10-jul-2011', 'A'),
(2, 'P1', '11-jul-2011', 'NA'),
(3, 'p1', '12-jul-2011', 'NA'),
(4, 'P1', '13-jul-2011', 'A'),
(5, 'p1', '14-jul-2011', 'P'),
(6, 'P1', '15-jul-2011', 'B'),
(7, 'p1', '16-jul-2011', 'B'),
(8, 'P1', '17-jul-2011', 'NA'),
(9, 'p1', '18-jul-2011', 'B'),
(10, 'P1','19-jul-2011', 'P'),
(11, 'p1', '20-jul-2011', 'A'),
(12, 'P1', '21-jul-2011', 'NA'),
(13, 'p1', '22-jul-2011', 'NA'),
(14, 'p1', '23-jul-2011', 'NA'),
(15, 'P1', '24-jul-2011', 'C')
select * from @Sample

Now I need to find the similer entries between two common status (one of A,B,C).

i.e, in sample data
we have to identify and update those NAs with status

A-NA-NA-A
B-NA-B

but not
A-NA-NA-NA-C

becoz it is bounded beteen two different categories A and C

Plz guide me.
TIA.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-21 : 03:37:19

[code]--- SwePeso
SELECT -- This is the FROM part
s.*,
-- Just a dummy column to enhance visual
NULL AS Dummy,
-- The is the TO part
f.*
FROM @Sample AS s
CROSS APPLY (
SELECT TOP(1) w.ID,
w.Person,
w.dt,
w.sts
FROM @Sample AS w
WHERE w.Person = s.Person
AND w.dt > s.dt
AND w.sts IN ('A', 'B', 'C')
) AS f(ID, Person, dt, sts)
CROSS APPLY (
SELECT COUNT(*) AS Items
FROM @Sample AS e
WHERE e.Person = s.Person
AND e.dt > s.dt
AND e.dt < f.dt
AND e.sts = 'NA'
) AS q(Items)
WHERE s.sts = f.sts
AND q.Items > 0[/code]

N 56°04'39.26"
E 12°55'05.63"

learntsql

524 Posts

Posted - 2011-07-21 : 04:48:48

Thanks a lot SwePeso.

Could u please tell me how to understand this query.
TIA.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 04:59:39

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

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

learntsql

524 Posts

Posted - 2011-07-21 : 05:56:11

quote:

Originally posted by SwePeso

--- SwePeso
SELECT		-- This is the FROM part
		s.*,
		-- Just a dummy column to enhance visual
		NULL AS Dummy,
		-- The is the TO part
		f.*
FROM		@Sample AS s
CROSS APPLY	(
			SELECT TOP(1)	w.ID,
					w.Person,
					w.dt,
					w.sts
			FROM		@Sample AS w
			WHERE		w.Person = s.Person
					AND w.dt > s.dt
					AND w.sts IN ('A', 'B', 'C')
		) AS f(ID, Person, dt, sts)
CROSS APPLY	(
			SELECT	COUNT(*) AS Items
			FROM	@Sample AS e
			WHERE	e.Person = s.Person
				AND e.dt > s.dt
				AND e.dt < f.dt
				AND e.sts = 'NA'
		) AS q(Items)
WHERE		s.sts = f.sts
		AND q.Items > 0

N 56°04'39.26"
E 12°55'05.63"

Sorry One more query here
in between same status if i put otherthan NA then also we are getting those actually shoud not get.
for eg;
A-NA-P-A

then this should not get in final output.

TIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 06:05:16

slight amendment


SELECT		-- This is the FROM part
		s.*,
		-- Just a dummy column to enhance visual
		NULL AS Dummy,
		-- The is the TO part
		f.*
FROM		@Sample AS s
CROSS APPLY	(
			SELECT TOP(1)	w.ID,
					w.Person,
					w.dt,
					w.sts
			FROM		@Sample AS w
			WHERE		w.Person = s.Person
					AND w.dt > s.dt
					AND w.sts IN ('A', 'B', 'C')
		) AS f(ID, Person, dt, sts)
CROSS APPLY	(
			SELECT	COUNT(CASE WHEN e.sts = 'NA' THEN 1 ELSE NULL END) AS NAItems,
                                COUNT(CASE WHEN e.sts <> 'NA' THEN 1 ELSE NULL END) AS OthItems
			FROM	@Sample AS e
			WHERE	e.Person = s.Person
				AND e.dt > s.dt
				AND e.dt < f.dt
				AND 		) AS q(Items)
WHERE		s.sts = f.sts
		AND q.NAItems > 0
                AND q.OthItems =0

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

learntsql

524 Posts

Posted - 2011-07-21 : 06:28:21

Thanks Visakh16.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources