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 :
You could leave a comment if you were logged in.