Created On:  28 October 2011

Problem:

Is there a SQL query to report on Starteam Projects modified (Files or Change Requests) before a specific date?

Resolution:

The following SQL query will return:
  • Project name(s)
  • Number of modified files
  • Date file(s) last modified
This will return the same information about modified Change Requests.
DECLARE @fromdate datetime
SET @fromdate = CAST('05/12/2011' AS datetime) -- mm/dd/yyyy;
-- date above is the date the item was last modified 'before'
-- i.e. has no modifications after this date
-- recommended: use todays date
-- i.e. remove the 'comment' markers from the following line
--SET @fromdate = GETDATE()

if OBJECT_ID('tempdb..#files') is not null drop table #files
if OBJECT_ID('tempdb..#crs') is not null drop table #crs

SELECT
 p.Name,
 COUNT(f.Name) [Files],
 MAX(dbo.ConvertSTTimestamp(f.ModifiedTime)) [LastFileMod]
 INTO #files
FROM
 syn_File f, syn_view v, syn_Project p
WHERE
 (v.ProjectID = p.ID) AND
 (f.ViewID = v.ID) AND
 dbo.ConvertSTTimestamp(f.modifiedtime) <= @fromdate AND
 (f.modifiedtime <> 0 and f.endmodifiedtime = 0) -- tip revision; therefore last modification
GROUP BY p.Name

SELECT
 p.Name,
 COUNT(c.ChangeNumber) [CRs],
 MAX(dbo.ConvertSTTimestamp(c.ModifiedTime)) [LastCRMod]
 INTO #crs
FROM
 syn_Change c, syn_view v, syn_Project p
WHERE
 (v.ProjectID = p.ID) AND
 (c.ViewID = v.ID) AND
 dbo.ConvertSTTimestamp(c.modifiedtime) <= @fromdate AND
 (c.modifiedtime <> 0 and c.endmodifiedtime = 0) -- tip revision; therefore last modification
GROUP BY p.Name

SELECT
 ISNULL(#files.Name, #crs.Name) AS Project,
 #files.Files as [# Modified Files],
 #files.LastFileMod AS [Last File Modified Date],
 #crs.CRs as [# Modified CRs],
 #crs.LastCRMod AS [Last CR Modified Date]
FROM
 #files FULL JOIN #crs
ON #files.Name = #crs.Name
ORDER BY 1