asked on

How to aggregate hours based on service type and date?

Hi,

I would like to modify the query below so that it aggregates the DailyHours based on service type and Entry Day. I would like the results for 1/21 to be one row and looks like:

Ruben       De Luna      01/21/2015      150.00      2      14970            0      2015-01-21 13:15:00.000

My current MS SQL server query is below:

            SELECT
            (U.FirstName + ISNULL(U.MiddleName, ' ')) AS FirstName,
            U.LastName,
            CONVERT(VARCHAR(15), N.StartTime, 101) AS EntryDay,
            SUM(N.Duration) AS DailyHours,
            N.ServiceType,
            N.UserId AS StudentId,
            '',
            0,
            N.StartTime,
            N.Subject
            FROM [Notes_Student] AS N WITH (NOLOCK)
            INNER JOIN [User] AS U WITH (NOLOCK) ON N.UserId = U.Id AND U.Id <> 725
            WHERE (N.LocationId = 379 AND N.[Attendance] = 1 AND N.UserId = 14970 AND N.StartTime BETWEEN '1/1/2015' AND '1/31/2015')
            GROUP BY U.LastName, U.FirstName, U.MiddleName, N.ServiceType, N.Subject, CONVERT(VARCHAR(15), N.StartTime, 101), N.StartTime, N.UserId;
Capture3.JPG

ASKER CERTIFIED SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

You need to remove some columns from the output and the group by

, N.StartTime --<< definitely
, N.Subject --<< probably

SELECT
      (U.FirstName + ISNULL(U.MiddleName, ' ')) AS FIRSTNAME
    , U.LastName
    , CONVERT(varchar(15),CAST(N.StartTime as date), 101) AS ENTRYDAY
    , SUM(N.Duration) AS DAILYHOURS
    , N.ServiceType
    , N.UserId AS STUDENTID
    , ''
    , 0
/*    , N.StartTime */
/*    , N.Subject */
FROM [Notes_Student] AS N WITH (NOLOCK)
INNER JOIN [User] AS U WITH (NOLOCK) ON N.UserId = U.Id
            AND U.Id <> 725
WHERE (N.LocationId = 379
            AND N.[Attendance] = 1
            AND N.UserId = 14970
            AND N.StartTime BETWEEN '1/1/2015' AND '1/31/2015')
GROUP BY
      U.LastName
    , U.FirstName
    , U.MiddleName
    , N.ServiceType
    , N.UserId AS STUDENTID
    , CAST(N.StartTime as date)
;

Open in new window

I would prefer to see you NOT use BETWEEN for date ranges,
and to use YYYYMMDD for date literals

this is a more reliable approach to filtering for a date range, and it won't matter what level of time precision that field stores.

AND ( N.StartTime >= '20150101' AND N.StartTime < '20150201' )