Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Iterating through records and ignoring unwanted rows Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 9:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 1, 2015 3:46 PM
Points: 23, Visits: 156
I have table set up similar to this:

CREATE TABLE #Visits (
[TargetID] [int] NOT NULL,
[TargetName] [nvarchar](255) NULL,
[Interaction] [nvarchar](15) NULL,
[Created] [datetime] NULL
)


INSERT INTO #Visits (TargetID,TargetName,Interaction,Created)
VALUES(1,'abcd','target_acquired','2012-10-03 20:24:00.000')
INSERT INTO #Visits (TargetID,TargetName,Interaction,Created)
VALUES(1,'abcd','target_acquired','2012-10-03 20:46:00.000')
INSERT INTO #Visits (TargetID,TargetName,Interaction,Created)
VALUES(1,'abcd','target_lost','2012-10-03 20:46:00.000')
INSERT INTO #Visits (TargetID,TargetName,Interaction,Created)
VALUES(2,'efgh','target_acquired','2012-10-03 20:51:00.000')
INSERT INTO #Visits (TargetID,TargetName,Interaction,Created)
VALUES(2,'efgh','target_lost','2012-10-03 21:01:00.000')


SELECT * FROM #Visits
DROP TABLE #Visits

My requirement is for every TargetID/TargetName combination, I want to return the first "target_acquired" row and then to ignore all other "target_acquired" rows until the "target_lost" record is hit. So in my sample table, I would want to ignore the second row for TargetID/TargetName combination 1/abcd, and not ignore any rows for 2/efgh.

Any suggestions on approach would be much appreciated. Thanks.
Post #1398495
Posted Wednesday, December 19, 2012 9:27 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, June 23, 2015 2:19 AM
Points: 711, Visits: 2,222
Hi,

I'd use rownumber() to do that. Given what you've described then this should do the trick:

SELECT 
x.TargetID, x.TargetName, x.Interaction, x.Created
FROM
(
SELECT
*, ROW_NUMBER() OVER (PARTITION BY TargetName, [Interaction] ORDER BY [Created]) AS Occurrance
FROM #Visits
) AS x
WHERE
x.[Occurrance] = 1
AND [x].[Interaction] = 'target_acquired'

DROP TABLE #Visits





MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1398501
Posted Wednesday, December 19, 2012 9:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 15, 2015 6:10 AM
Points: 13,872, Visits: 9,606
This will work:

IF OBJECT_ID(N'tempdb..#Visits') IS NOT NULL 
DROP TABLE #Visits;

CREATE TABLE #Visits
([TargetID] [int] NOT NULL,
[TargetName] [nvarchar](255) NULL,
[Interaction] [nvarchar](15) NULL,
[Created] [datetime] NULL)

INSERT INTO #Visits
(TargetID, TargetName, Interaction, Created)
VALUES (1, 'abcd', 'target_acquired', '2012-10-03 20:24:00.000'),
(1, 'abcd', 'target_acquired', '2012-10-03 20:46:00.000'),
(1, 'abcd', 'target_lost', '2012-10-03 20:46:00.000'),
(1, 'abcd', 'target_acquired', '2012-10-03 20:47:00.000'),
(2, 'efgh', 'target_acquired', '2012-10-03 20:51:00.000'),
(2, 'efgh', 'target_lost', '2012-10-03 21:01:00.000');

SELECT V.TargetID,
V.Interaction,
MIN(V.Created) AS Created
FROM #Visits AS V
WHERE V.Interaction = 'target_acquired'
GROUP BY V.TargetID,
V.Interaction
UNION ALL
SELECT Losses.TargetID,
Losses.Interaction,
Losses.Created AS Lost
FROM #Visits AS Losses
WHERE Losses.Interaction = 'target_lost'
UNION ALL
SELECT Losses2.TargetID,
ReAquired.Interaction,
ReAquired.Created AS ReAquired
FROM #Visits AS Losses2
CROSS APPLY (SELECT TOP (1)
ReAquired.Created,
ReAquired.Interaction
FROM #Visits AS ReAquired
WHERE Losses2.TargetID = ReAcquired.TargetID
AND ReAcquired.Interaction = 'target_acquired'
AND ReAcquired.Created > Losses2.Created
ORDER BY ReAcquired.Created) AS ReAcquired
WHERE Losses2.Interaction = 'target_lost'
ORDER BY TargetID,
Created;

I added a row to the sample data so that it has a re-acquire in the sample. Does that do what you need?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1398510
Posted Wednesday, December 19, 2012 9:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 1, 2015 3:46 PM
Points: 23, Visits: 156
Although I didn't specify, the re-acquire method is exactly what I was looking for. Thanks much for posting!
Post #1398517
Posted Wednesday, December 19, 2012 9:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 15, 2015 6:10 AM
Points: 13,872, Visits: 9,606
You're welcome. Glad we could help.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1398519
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse