This query will show the version history of Control based on the user login records.

There are 3 columns returned:

• FirstLogin - The first login record for the version of Control.
• LastLogin - The last login record for the version of Control.
• VersionNumber - The version of Control.

There are 2 variables that can to changed to adjust the date range. Changing these variables will allow you to view the version history within a specific date range.:

• @StartDate
• @EndDate

None. This is a selection query and no data is modified in the running of it.

DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
SET @StartDate = '08/01/2009';
SET @EndDate = '01/01/2100';
DECLARE @VersionList TABLE (ID INT PRIMARY KEY, LoginDateTime DATETIME,
                            VersionNumber VARCHAR(15), Series INT);
INSERT INTO @VersionList (ID, LoginDateTime, VersionNumber)
SELECT ROW_NUMBER() OVER (ORDER BY StartDateTime),
       StartDateTime,
       SUBSTRING(Notes, PATINDEX('%Version:%', Notes)+10, 15) AS VersionNumber
FROM Journal
WHERE JournalActivityType = 30 AND StartDateTime BETWEEN @StartDate AND @EndDate
;
DECLARE MyCursor CURSOR FOR SELECT VersionNumber FROM @VersionList ORDER BY ID;
DECLARE @MyVersion VARCHAR(15);
DECLARE @CurrentVersion VARCHAR(15);
SET @CurrentVersion = '';
DECLARE @CurrentSeries INT;
SET @CurrentSeries = 0;
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @MyVersion
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @CurrentVersion <> @MyVersion
    SET @CurrentSeries = @CurrentSeries+1;
  SET @CurrentVersion = @MyVersion;
  UPDATE @VersionList
  SET Series = @CurrentSeries
  WHERE CURRENT OF MyCursor;
  FETCH NEXT FROM MyCursor INTO @MyVersion
END
CLOSE MyCursor
DEALLOCATE MyCursor
;
SELECT MIN(LoginDateTime) FirstLogin, MAX(LoginDateTime) LastLogin, VersionNumber
FROM @VersionList T
GROUP BY Series, VersionNumber
ORDER BY Series DESC

DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
SET @StartDate = '08/01/2009';
SET @EndDate = '01/01/2100';
DECLARE @VersionList TABLE (ID INT PRIMARY KEY, LoginDateTime DATETIME,
                            VersionNumber VARCHAR(15), Series INT);
INSERT INTO @VersionList (ID, LoginDateTime, VersionNumber)
SELECT ID, StartDateTime,
       SUBSTRING(Notes, PATINDEX('%Version:%', Notes)+10, 15) AS VersionNumber
FROM Journal
WHERE JournalActivityType = 30 AND StartDateTime BETWEEN @StartDate AND @EndDate
;
DECLARE MyCursor CURSOR FOR SELECT VersionNumber FROM @VersionList ORDER BY ID;
DECLARE @MyVersion VARCHAR(15);
DECLARE @CurrentVersion VARCHAR(15);
SET @CurrentVersion = '';
DECLARE @CurrentSeries INT;
SET @CurrentSeries = 0;
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @MyVersion
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @CurrentVersion <> @MyVersion
    SET @CurrentSeries = @CurrentSeries+1;
  SET @CurrentVersion = @MyVersion;
  UPDATE @VersionList
  SET Series = @CurrentSeries
  WHERE CURRENT OF MyCursor;
  FETCH NEXT FROM MyCursor INTO @MyVersion
END
CLOSE MyCursor
DEALLOCATE MyCursor
;
SELECT MIN(LoginDateTime) FirstLogin, MAX(LoginDateTime) LastLogin, VersionNumber
FROM @VersionList T
GROUP BY Series, VersionNumber
ORDER BY Series DESC

• Entered : 01/13/2010
• Version :