Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision 		Previous revision
control_sql_-_version_history [2019/01/27 11:29]
127.0.0.1 external edit 		control_sql_-_version_history [2019/08/21 17:29] (current)
dsummers
Line 1: Line 1:
-======  ====== +======   ======
- +
  
 ===== Explanation of SQL ===== ===== Explanation of SQL =====
- 
- 
  
 This query will show the version history of Control based on the user login records. This query will show the version history of Control based on the user login records.
  
 +There are 3 columns returned:
  
- 
-There are 3 columns returned: 
   * FirstLogin - The first login record for the version of Control.   * FirstLogin - The first login record for the version of Control.
   * LastLogin - The last login record for the version of Control.   * LastLogin - The last login record for the version of Control.
   * VersionNumber - 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.:
  
- 
-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   * @StartDate
   * @EndDate   * @EndDate
- 
- 
  
 ===== Risk of Data Corruption if Run Improperly ===== ===== Risk of Data Corruption if Run Improperly =====
  
 **None**. This is a selection query and no data is modified in the running of it. **None**. This is a selection query and no data is modified in the running of it.
- 
- 
  
 ===== Query for SQL 2005 or newer ===== ===== Query for SQL 2005 or newer =====
- 
- 
  
 <code sql> <code sql>
Line 58: Line 46:
 WHILE @@FETCH_STATUS = 0 WHILE @@FETCH_STATUS = 0
 BEGIN BEGIN
-  IF @CurrentVersion  @MyVersion+  IF @CurrentVersion <> @MyVersion
     SET @CurrentSeries = @CurrentSeries+1;     SET @CurrentSeries = @CurrentSeries+1;
   SET @CurrentVersion = @MyVersion;   SET @CurrentVersion = @MyVersion;
Line 69: Line 57:
 DEALLOCATE MyCursor DEALLOCATE MyCursor
 ; ;
-SELECT Min(LoginDateTime) FirstLogin, Max(LoginDateTime) LastLogin, VersionNumber+SELECT MIN(LoginDateTime) FirstLogin, MAX(LoginDateTime) LastLogin, VersionNumber
 FROM @VersionList T FROM @VersionList T
 GROUP BY Series, VersionNumber GROUP BY Series, VersionNumber
 ORDER BY Series DESC ORDER BY Series DESC
 </code> </code>
- 
- 
  
 ===== Query for SQL 2000 Only ===== ===== Query for SQL 2000 Only =====
- 
- 
  
 <code sql> <code sql>
Line 89: Line 73:
                             VersionNumber VARCHAR(15), Series INT);                             VersionNumber VARCHAR(15), Series INT);
 INSERT INTO @VersionList (ID, LoginDateTime, VersionNumber) INSERT INTO @VersionList (ID, LoginDateTime, VersionNumber)
-SELECT ID, StartDateTime, +SELECT ID, StartDateTime,
        SUBSTRING(Notes, PATINDEX('%Version:%', Notes)+10, 15) AS VersionNumber        SUBSTRING(Notes, PATINDEX('%Version:%', Notes)+10, 15) AS VersionNumber
 FROM Journal FROM Journal
Line 104: Line 88:
 WHILE @@FETCH_STATUS = 0 WHILE @@FETCH_STATUS = 0
 BEGIN BEGIN
-  IF @CurrentVersion  @MyVersion+  IF @CurrentVersion <> @MyVersion
     SET @CurrentSeries = @CurrentSeries+1;     SET @CurrentSeries = @CurrentSeries+1;
   SET @CurrentVersion = @MyVersion;   SET @CurrentVersion = @MyVersion;
Line 115: Line 99:
 DEALLOCATE MyCursor DEALLOCATE MyCursor
 ; ;
-SELECT Min(LoginDateTime) FirstLogin, Max(LoginDateTime) LastLogin, VersionNumber+SELECT MIN(LoginDateTime) FirstLogin, MAX(LoginDateTime) LastLogin, VersionNumber
 FROM @VersionList T FROM @VersionList T
 GROUP BY Series, VersionNumber GROUP BY Series, VersionNumber
Line 121: Line 105:
 </code> </code>
  
 +===== Version Information =====
  
- 
-===== Version Information ===== 
   * Entered : 01/13/2010   * Entered : 01/13/2010
   * Version :   * Version :
- 
-