CODEWITHSUNDEEP
sql-serverdatetimeaverage
Thomas James
Thomas James

Reputation: 57

How to Combine a query with a AVG query when using datetime SQL 2008 R2

I am trying to combine two query's together but I'm having trouble as it is a datetime column. right now I have a query that finds all the users + there slowest and fastest "Segment" time but I also need to find the avg segment time below are the two query's I am using right now which work but I need it to be in one query for a report I am making.

Thanks

Thomas James

DECLARE 
@vnuID int = 1212,
@StartDate DateTime = '30/10/2013',
@EndDate DateTime = '26/11/2013'

SELECT DISTINCT usrFullName, MIN(CAST(tblTrace.trFinish - tblTrace.trStart AS TIME)) AS FastestUserSegmentTime,  
        MAX(CAST(tblTrace.trFinish - tblTrace.trStart AS TIME)) AS SlowestUserSegmentTime 
FROM tblTrace 
    INNER JOIN tblUsers ON usrID = tr_usrID
WHERE tr_vnuID = @vnuID AND trFinish IS NOT NULL AND tr_usrID IS NOT NULL AND trObjectType LIKE 'Segment%'  
        AND trStart BETWEEN @StartDate AND @EndDate 
GROUP BY usrFullName


SELECT AVG(TotalTime) AS AvgUserSegmentTime 
FROM (SELECT DateDiff(SECOND, tblTrace.trStart , tblTrace.trFinish) as 'TotalTime' 
        FROM tblTrace
        WHERE tblTrace.trFinish IS NOT NULL AND tblTrace.trObjectType LIKE 'Segment%' AND tblTrace.tr_vnuID = @vnuID 
        AND tblTrace.trStart BETWEEN @StartDate AND @EndDate ) as SubQuery

Upvotes: 1

Views: 80

Answers (1)

Matt Whitfield
Matt Whitfield

Reputation: 6574

Functionally (i.e. without optimization) you could use something like this:

DECLARE 
@vnuID int = 1212,
@StartDate DateTime = '30/10/2013',
@EndDate DateTime = '26/11/2013'

SELECT usrFullName, FastestUserSegmentTime, SlowestUserSegmentTime,
       (SELECT AVG(DateDiff(SECOND, tblTrace.trStart , tblTrace.trFinish)) 
        FROM tblTrace
        WHERE tblTrace.trFinish IS NOT NULL AND tblTrace.trObjectType LIKE 'Segment%' AND tblTrace.tr_vnuID = @vnuID 
        AND tblTrace.trStart BETWEEN @StartDate AND @EndDate) as AvgUserSegmentTime
FROM (
    SELECT usrFullName, 
           MIN(CAST(tblTrace.trFinish - tblTrace.trStart AS TIME)) AS FastestUserSegmentTime,  
           MAX(CAST(tblTrace.trFinish - tblTrace.trStart AS TIME)) AS SlowestUserSegmentTime 
    FROM tblTrace 
        INNER JOIN tblUsers ON usrID = tr_usrID
    WHERE tr_vnuID = @vnuID AND trFinish IS NOT NULL AND tr_usrID IS NOT NULL AND trObjectType LIKE 'Segment%'  
            AND trStart BETWEEN @StartDate AND @EndDate 
    GROUP BY usrFullName
) coreData

However, I would assume that you would want tr_usrID not to be null in all cases? In which case you could just use this:

SELECT usrFullName, 
       CONVERT(TIME, DATEADD(s, MIN(SegmentTime))) as FastestUserSegmentTime,
       CONVERT(TIME, DATEADD(s, MAX(SegmentTime))) as SlowestUserSegmentTime,
       AVG(SegmentTime) as AvgUserSegmentTime
  FROM (
    SELECT usrFullName, 
           DateDiff(SECOND, tblTrace.trStart, tblTrace.trFinish) as SegmentTime
      FROM tblTrace INNER JOIN
           tblUsers  
        ON usrID = tr_usrID
     WHERE tr_vnuID = @vnuID 
       AND trFinish IS NOT NULL 
       AND tr_usrID IS NOT NULL 
       AND trObjectType LIKE 'Segment%'  
       AND trStart BETWEEN @StartDate AND @EndDate 
       ) coreData

Upvotes: 1

Related Questions