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: Wednesday, October 23, 2013 8:28 PM
Points: 23, Visits: 154
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: Yesterday @ 8:56 AM
Points: 710, Visits: 2,205
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: Yesterday @ 1:09 PM
Points: 13,872, Visits: 9,597
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: Wednesday, October 23, 2013 8:28 PM
Points: 23, Visits: 154
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: Yesterday @ 1:09 PM
Points: 13,872, Visits: 9,597
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
Posted Wednesday, December 19, 2012 6:45 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:59 PM
Points: 1,945, Visits: 3,080
Do you know that a table has to have a key, so this is not a table at all? Do you really need all those NULLs? Do these columns really 255 characters of require Chinese Unicode? If you allow garbage data, you will get it.

Why is the target name part of a visit, when you have the target_nbr? Why did you fail to normalize the schema or use ISO-11179 data element names?

CREATE TABLE Targets
(target_nbr INTEGER NOT NULL PRIMARY KEY,
target_name VARCHAR (25) NOT NULL);

INSERT INTO Targets
VALUES (1, 'abcd'), (2, 'efgh');

Targets are entities; entities have their own tables. This is RDBMS 101. Your table was not normalized. In fact, it is a paper form written in SQL.

I hate the names “Visits”; you shoot targets, not visit them. What you are trying to capture is the status of a target over time. What you are doing is copying the clip board paper report, line by line into a non-table, exactly like it would appear on a paper form. You need correct temporal and status components in the table:

CREATE TABLE Visits
(target_nbr INTEGER NOT NULL
REFERENCES Targets(target_nbr),
prior_visit_status VARCHAR(15) NOT NULL,
current_visit_status VARCHAR(15) NOT NULL,
FOREIGN KEY (prior_visit_status, current_visit_status)
REFERENCES Visit_Status (prior_visit_status, current_visit_status),
visit_start_timestamp DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
visit_end_timestamp DATETIME2(0), --- null is current status
CHECK (visit_start_timestamp < visit_end_timestamp));

The prior/current status pair assured that I can only lose what was acquired. Google http://www.simple-talk.com/sql/t-sql-programming/state-transition-constraints/

I will let you read the article, and write the DDL (exercise for the student). Your decision is if you want to allow a ('acquired', 'acquired') state transition or catch a re-acquire attempt and disallow it when you insert new data. Quick version with ('not searched' -> 'acquired' -> 'lost') rules,

INSERT INTO Visits
VALUES(1, 'not searched', 'acquired', '2012-10-03 20:24:00', 20:46:00'),
(1, 'acquired', 'lost', '2012-10-03 20:46:01', NULL),
(2, 'not searched', 'acquired', '2012-10-03 20:51:00','2012-10-03 21:01:00').
(2, 'acquired', 'lost', '2012-10-03 21:01:00', NULL);

>> My requirement is for every target_nbr combination, I want to return the first [sic: earliest?] "target_acquired" row and then to ignore all other "target_acquired" rows until the "target_lost" record [sic: rows are not records] is hit. So in my sample table, I would want to ignore the second [sic: rows have no order] row for target_nbr = 1, and not ignore any rows for target_nbr = 2. <<

All you got here were kludges, not good SQL. THIS IS TRICKY! But if you want data integrity, you need it. The other posters sequentially number your paper sign-in sheet and missed the underlying data model.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1398726
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse