DISPLAY IN-OUT COMBINATIONS - 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 2005 Forums

Transact-SQL (2005)

DISPLAY IN-OUT COMBINATIONS

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2012-05-31 : 01:35:05

HI
BELOW IS MY DATA
1 1 BALA IN
1 2 BALA OUT
1 3 BALA IN
1 4 BALA OUT
1 5 BALA OUT
1 6 BALA IN
1 7 BALA IN
1 8 BALA OUT

I SHOULD DISPLAY ONLY IN/OUT COMBINATIONS LIKE BELOW..
1 1 BALA IN
1 2 BALA OUT
1 3 BALA IN
1 4 BALA OUT
1 7 BALA IN
1 8 BALA OUT

HOW TO DO IT...

Solutions are easy. Understanding the problem, now, that's the hard part

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2012-05-31 : 02:03:00

DECLARE @TAB TABLE(ID INT,NAME VARCHAR(25),DATE DATETIME,ENTRYFLAG VARCHAR(10))
DECLARE @TAB1 TABLE(ID INT,RNO INT,NAME VARCHAR(25),DATE DATETIME,ENTRYFLAG VARCHAR(10))

INSERT INTO @TAB
SELECT 1,'BALA',GETDATE(),'IN' UNION ALL
SELECT 1,'BALA',GETDATE(),'OUT' UNION ALL
SELECT 1,'BALA',GETDATE(),'IN' UNION ALL
SELECT 1,'BALA',GETDATE(),'OUT' UNION ALL
SELECT 1,'BALA',GETDATE(),'OUT' UNION ALL
SELECT 1,'BALA',GETDATE(),'IN' UNION ALL
SELECT 1,'BALA',GETDATE(),'IN' UNION ALL
SELECT 1,'BALA',GETDATE(),'OUT' UNION ALL
SELECT 2,'GANESH',GETDATE(),'IN' UNION ALL
SELECT 2,'GANESH',GETDATE(),'IN' UNION ALL
SELECT 2,'GANESH',GETDATE(),'OUT' UNION ALL
SELECT 2,'GANESH',GETDATE(),'IN' UNION ALL
SELECT 2,'GANESH',GETDATE(),'OUT' UNION ALL
SELECT 2,'GANESH',GETDATE(),'IN' UNION ALL
SELECT 2,'GANESH',GETDATE(),'OUT'

INSERT INTO @TAB1
SELECT ID,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) RNO,NAME,DATE,ENTRYFLAG FROM @TAB

SELECT * FROM @TAB1

SELECT * FROM (
SELECT I.ID,I.RNO,I.NAME,I.ENTRYFLAG FROM @TAB1 I
WHERE I.ENTRYFLAG = 'IN'
AND EXISTS (SELECT 1 FROM @TAB1 O WHERE O.ID = I.ID AND O.RNO = (I.RNO+1) AND O.ENTRYFLAG = 'OUT')
UNION ALL
SELECT I.ID,I.RNO,I.NAME,I.ENTRYFLAG FROM @TAB1 I
WHERE I.ENTRYFLAG = 'OUT'
AND EXISTS (SELECT 1 FROM @TAB1 O WHERE O.ID = I.ID AND O.RNO = (I.RNO-1) AND O.ENTRYFLAG = 'IN')) A
ORDER BY ID,RNO

Solutions are easy. Understanding the problem, now, that's the hard part

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-31 : 03:22:45

[code]DECLARE @Sample TABLE
(
ID INT NOT NULL,
Seq INT NOT NULL,
Name VARCHAR(25) NOT NULL,
EntryFlag VARCHAR(10) NOT NULL
)

INSERT @Sample
VALUES (1, 1,'BALA', 'IN'),
(1, 2,'BALA', 'OUT'),
(1, 3,'BALA', 'IN'),
(1, 5,'BALA', 'OUT'),
(1, 6,'BALA', 'OUT'),
(1, 7,'BALA', 'IN'),
(1, 8,'BALA', 'IN'),
(1, 9,'BALA', 'OUT'),
(2, 1,'GANESH', 'IN'),
(2, 2,'GANESH', 'IN'),
(2, 5,'GANESH', 'OUT'),
(2, 6,'GANESH', 'IN'),
(2, 7,'GANESH', 'OUT'),
(2, 8,'GANESH', 'IN'),
(2, 9,'GANESH', 'OUT')

-- SwePeso
;WITH cteSource(ID, Seq, Name, EntryFlag, Grp)
AS (
SELECT ID,
Seq,
Name,
EntryFlag,
Seq - ROW_NUMBER() OVER (PARTITION BY ID, EntryFlag ORDER BY Seq) AS Grp
FROM @Sample
)
SELECT ID,
Seq,
Name,
EntryFlag
FROM (
SELECT ID,
Seq,
Name,
EntryFlag,
ROW_NUMBER() OVER (PARTITION BY ID, EntryFlag, Grp ORDER BY Seq DESC) AS rn
FROM cteSource
) AS d
WHERE rn = 1
ORDER BY ID,
Seq[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources