Control SQL - Version History

Explanation of SQL

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

Risk of Data Corruption if Run Improperly

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

Query for SQL 2005 or newer

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

Query for SQL 2000 Only

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

Version Information

  • Entered : 01/13/2010
  • Version :

Related SQLs