Geek Answers Handbook

Find posts for a user that do not touch

It’s hard for me to figure out how to start this query.

I have a table with the following columns and data:

User BeginMile EndMile 1 1 5 1 5 6 1 6 20 1 20 25 1 25 29 2 1 9 2 15 20 3 1 2 3 6 10 3 10 12

I need to first find where there are spaces for each user from the EndMile of the previous record, to the BeginMile of the next record. Then I need to return the record before and after where a space occurs for each user.

In the previous data example, I would like to return the following:

 User PrevBeginMile PrevEndMile AfterBeginMile AfterEndMile Gap 2 1 9 15 20 6 3 1 2 6 10 4

How can I do that?

+4
source share
3 answers

Given that you are working on SQL 2005, this should work:

 DECLARE @Runners TABLE (Id INT, BeginMile INT, EndMile INT) INSERT INTO @Runners VALUES (1,1,5) INSERT INTO @Runners VALUES (1,5,6) INSERT INTO @Runners VALUES (1,6,20) INSERT INTO @Runners VALUES (1,20,25) INSERT INTO @Runners VALUES (1,25,29) INSERT INTO @Runners VALUES (2,1,9) INSERT INTO @Runners VALUES (2,15,20) INSERT INTO @Runners VALUES (3,1,2) INSERT INTO @Runners VALUES (3,6,10) INSERT INTO @Runners VALUES (3,10,12) WITH OrderedUsers AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY Id ORDER BY BeginMile) RowNum FROM @Runners ) SELECT a.Id [User] , a.BeginMile PrevBeginMile , a.EndMile PrevEndMile , b.BeginMile AfterBeginMile , b.EndMile AfterEndMile , b.BeginMile - a.EndMile Gap FROM OrderedUsers a JOIN OrderedUsers b ON a.Id = b.Id AND a.EndMile <> b.BeginMile AND a.RowNum = b.RowNum - 1
+3
source

Besides using RowNumber () [as in other answers], you can use ...

 SELECT [current].User, [current].BeginMile AS [PrevBeginMile], [current].EndMile AS [PrevEndMile], [next].BeginMile AS [AfterBeginMile], [next].EndMile AS [AfterEndMile], [next].BeginMile - [current].EndMile AS [Gap] FROM myTable AS [current] CROSS APPLY (SELECT TOP 1 * FROM myTable WHERE user = [current].User AND BeginMile > [current].BeginMile ORDER BY BeginMile ASC) AS [next] WHERE [current].EndMile <> [next].BeginMile

Or maybe ...

 FROM myTable AS [current] INNER JOIN myTable AS [next] ON [next].BeginMile != [current].EndMile AND [next].BeginMile = ( SELECT MIN(BeginMile) FROM myTable WHERE user = [current].User AND BeginMile > [current].BeginMile )
0
source

What about

 WITH acte(user,beginmile,endmile) AS ( SELECT user,start,end ROW_NUMBER() OVER(PARTITION BY user ORDER BY START ASC) rownum FROM mytable ) SELECT base.user,base.beginmile,base.endmile,base.BeginMile - lead.EndMile Gap FROM acte base LEFT JOIN acte lead on base.id=lead.id AND base.rownum=lead.rownum-1 WHERE base.BeginMile - lead.EndMile > 0
0
source

More articles:


All Articles