Reputation: 83
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
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
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