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