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
开发者_JAVA技巧 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
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)
精彩评论