Finding a single record in a table step-by-step by different requests

Question

There is a table in a databse, let's call this table Document. This table has fields:

• MajorVersionNumber
• MinorVersionNumber
• ReleaseDate

There are rules to determine the order of versions and their expiration dates. I'll give the rule of finding expiration date of a version in C# 3 because it looks more easy to read then in English.

        var nextMinorVersion = Versions.FirstOrDefault((version) =>
            (version.majorVersionNumber == currentVersion.majorVersionNumber) &&
            (version.minorVersionNumber == currentVersion.minorVersionNumber + 1));
        if (nextMinorVersion != null) return nextMinorVersion.ReleaseDate;
        var nextMajorVersion = Versions.FirstOrDefault((version) =>
            (version.majorVersionNumber == currentVersion.majorVersionNumber + 1) &&
            (version.minorVersionNumber == 0));
        if (nextMajorVersion != null) return nextMajorVersion.ReleaseDate;
        return null;

Now this rule must be implemented in SQL for MS SQL Server 2005 and 2008. I tried and could make up only very cumbersome, inefficient and ureadable expressions. Taking into account how trivial it looks in C#, I think I can't do it as easy just because I'm not deft with SQL.

I'm looking for a way to do it in SQL with relatively the same complexity as in C#.

Sorry for such a narrow question, I don't know how to generalize this. Suggestions on generalizing the question and it's title are also very appreciated.

UPDATE For those who are not deft with C# 3 I'll try to explain the rule in pseudocode:

if exists nextMinorVersion so that
    nextMinorVersion.majorVersionNumber = currentVersion.majorVersionNumber and
    nextMinorVersion.minorVersionNumber = currentVersion.minorVersionNumber + 1
    then expirationDate = nextMinorVersion.ReleaseDate
else if exists nextMajorVersion so that
    nextMajorVersion.majorVersionNumber = currentVersion.majorVersionNumber + 1 and
    nextMinorVersion.majorVersionNumber = 0
    then expirationDate = nextMajorVersion.ReleaseDate
else expirationDate = null

Answer 1

I think I've got it - something like:

SELECT TOP 1 ReleaseDate
FROM Versions
WHERE
    (MajorVersion = @CurrentMajor AND MinorVersion = @CurrentMinor + 1)
    OR (MajorVersion = @CurrentMajor + 1 AND MinorVersion = 0)
ORDER BY MajorVersion, MinorVersion

In my test data below, any 1.x version also retrieves a 2.0 version (because it's an OR clause) which is where the TOP 1 and the ORDER BY comes in - it only selects the 1.x record if there is one.

This also works if the versions are inter-twined (for example 1.3 was released after 2.0.)

FYI here's my table definition:

CREATE TABLE [dbo].[Versions](
    [MajorVersion] [int] NOT NULL,
    [MinorVersion] [int] NOT NULL,
    [ReleaseDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Versions] PRIMARY KEY CLUSTERED 
(
    [MajorVersion] ASC,
    [MinorVersion] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert some data:

INSERT INTO Versions VALUES (1, 0, '2009-01-01')
INSERT INTO Versions VALUES (1, 1, '2009-01-10')
INSERT INTO Versions VALUES (1, 2, '2009-01-21')
INSERT INTO Versions VALUES (2, 0, '2009-02-01')
INSERT INTO Versions VALUES (2, 1, '2009-02-20')
INSERT INTO Versions VALUES (1, 3, '2009-03-01')

Try it out:

1.0 = 2009-01-10
1.1 = 2009-01-21
1.2 = 2009-03-01
1.3 = 2009-02-01
2.0 = 2009-02-20
2.1 = NULL (no rows)