Iterating through records and ignoring unwanted rows

  • 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.

  • 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

  • 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

  • Although I didn't specify, the re-acquire method is exactly what I was looking for. Thanks much for posting!

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply