SELECT     cm.*
FROM         CRMMetrics AS cm INNER JOIN
                      BatchRecords AS br ON cm.ApplicationJobID = br.JobName
WHERE     cm.Project = 'BMongr' AND cm.Type = 'Build' AND br.TerminationUser IS NULL AND br.EndTime IS NOT NULL), BuildRootsChildrenDuration AS
    (SELECT     br.ID AS ParentID, SUM(cm.NumSeconds) AS Duration
      FROM          CRMMetrics AS cm INNER JOIN
                             BuildRoots AS br ON cm.ParentID = br.ID
      GROUP BY br.ID), BuildRootsLeftover AS
    (SELECT     br.ID AS ID, br.NumSeconds - cd.Duration AS LeftoverDuration, cd.Duration AS ChildrenDuration
      FROM          BuildRootsChildrenDuration AS cd INNER JOIN
                             BuildRoots AS br ON (cd.ParentID = br.ID)), BuildOverheads AS
    (SELECT     cm.ApplicationID AS ApplicationID, cm.ApplicationJobID AS ApplicationJobID, 'Other' AS Type, NULL AS StartTime, 
                             LeftoverDuration AS NumSeconds
      FROM          CRMMetrics AS cm INNER JOIN
                             BuildRootsLeftover AS le ON cm.ID = le.ID)
    SELECT     ApplicationID AS BuildID, ApplicationJobID AS Jobname, Type AS Action, StartTime AS StartTime, NumSeconds AS Duration
     FROM         BuildOverheads
UNION
SELECT     br.ApplicationID AS BuildID, br.ApplicationJobID AS Jobname, cm.Type AS Action, DATEADD(SECOND, cm.StartTime, '1970-01-01') AS StartTime, 
                      cm.NumSeconds AS Duration
FROM         CRMMetrics AS cm INNER JOIN
                      BuildRoots AS br ON (cm.ParentID = br.ID OR
                      cm.ID = br.ID)

quote:

---

Originally posted by sql-programmers

Send me the table structure or could you explain the purpose of using comma sepeartor in where condtion.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

---