CODEWITHSUNDEEP
sql-serversql-server-2012
Dan
Dan

Reputation: 83

SQL Server : find rows between 2 rows

Modifying to more accurately represent the problem I am trying to solve. I over simplified the question the first time.

I have 2 SQL Server 2012 tables with data like so:

Table CommitBranch

Hash                                        HashDate                 BranchName
-----------------------------------------------------------------------------------
4191737d919e599690a8b9efd691ffc5165908f9    2013-01-26 15:24:44.000  B1
4191737d919e599690a8b9efd691ffc5165908f9    2013-01-26 15:24:44.000  B2
59503c5e22914b4926e8b5352ef254ebf67b1245    2013-01-28 19:28:12.000  B1
b3052d0c950a731d7221bab8825ba5787a9eb372    2013-01-29 17:05:02.000  B1
54752ce05b2c7784ff22c6161f9ecc33d4b1149f    2013-01-29 17:42:16.000  B2
d7c008d807d3439539b98dccf4c93ee6bc33405c    2013-01-29 21:29:32.000  B1
c890ca6ab1735ee391407ca80aa789a560632709    2013-01-31 04:39:57.000  B2
bf75a42b88782b0dc084a5642cc98876ad3ef41d    2013-01-31 04:41:30.000  B3
e54982d42d2b87e35d47536cd2017c18117afc28    2013-01-31 15:54:20.000  B1
e54982d42d2b87e35d47536cd2017c18117afc28    2013-01-31 15:54:20.000  B2    
e54982d42d2b87e35d47536cd2017c18117afc28    2013-01-31 15:54:20.000  B3

Hash and BranchName are the primary key.

Table Build

BuildID  LastCommitHash                            BranchName 
------------------------------------------------------------
1        4191737d919e599690a8b9efd691ffc5165908f9  B1
2        b3052d0c950a731d7221bab8825ba5787a9eb372  B1
3        e54982d42d2b87e35d47536cd2017c18117afc28  B1
4        bf75a42b88782b0dc084a5642cc98876ad3ef41d  B3

LastCommitHash is the Hash of the CommitBranch record used at the time the Build was created. Note that not every Commit hash will appear in the Build table - there will be gaps as the example shows (59503c5e22914b4926e8b5352ef254ebf67b1245 is missing, for example). The only reliable sorting for the CommitBranch table is by HashDate - the hashes themselves are effectively random.

I am trying to write a query to be used in a view called BuildCommit to give me all of the CommitBranch records between the LastCommitHash in the Build table and the Previous Build's LastCommitHash (but not including that build). I'm basically trying to answer the question, what Commits have been made on this branch since the last build.

The query would return the following:

BuildID CommitHash                                  BranchName
------------------------------------------------------------------
1       4191737d919e599690a8b9efd691ffc5165908f9    B1
2       b3052d0c950a731d7221bab8825ba5787a9eb372    B1
2       59503c5e22914b4926e8b5352ef254ebf67b1245    B1
3       e54982d42d2b87e35d47536cd2017c18117afc28    B1
3       d7c008d807d3439539b98dccf4c93ee6bc33405c    B1
4       bf75a42b88782b0dc084a5642cc98876ad3ef41d    B3
...

Edge conditions (like there being only 1 build for a branch and therefore no "since" record) must be dealt with.

Answers to date have been helpful and appreciated, but unfortunately not quite what I needed due to me oversimplifying the original question.

Thanks in advance!

Upvotes: 0

Views: 94

Answers (2)

eavom
eavom

Reputation: 1097

You can do something like below. Modify this query as per your need

DECLARE @StartHash char(40) = 'bf75a42b88782b0dc084a5642cc98876ad3ef41d'
DECLARE @EndHash char(40) = 'b3052d0c950a731d7221bab8825ba5787a9eb372'

    ;WITH CTE AS 
    (
        SELECT *, ROW_NUMBER() OVER(ORDER BY H.HashDate) N
        FROM HASHTABLE H
    )
    SELECT [Hash], [HashDate]
    FROM CTE
    WHERE EXISTS(SELECT 1 FROM CTE CMAX WHERE CMAX.[Hash] = @EndHash AND CMAX.N <= CTE.N)
          AND EXISTS(SELECT 1 FROM CTE CMIN WHERE CMIN.[Hash] = @StartHash AND CMIN.N >= CTE.N)

Upvotes: 1

Muthukumar
Muthukumar

Reputation: 9619

Try this

declare @minDate Datetime
declare @maxdate Datetime

select @minDate = min(hashDate), @maxdate = max(hashDate)
from table
where hash in (@starthash, @endhash)

select * from table
where hashDate > @minDate and hashDate <= @maxdate

Upvotes: 1

Related Questions