CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
Bitz
Bitz

Reputation: 1148

Find SUM of DATEDIFF on distinct pairs grouped by UserID?

So I have a command that looks like this:

SELECT
UserID,
FacilityMMXID,
ScheduleDate,
StartTime,
EndTime
FROM TblPASchedule 
WHERE UserID = 244 AND MONTH(ScheduleDate) = 03 AND Year(ScheduleDate) = 2017

The output looks like this

UserID      FacilityMMXID ScheduleDate StartTime        EndTime
----------- ------------- ------------ ---------------- ----------------
244         1             2017-03-17   01:00:00         05:00:00
244         2             2017-03-17   01:00:00         05:00:00
244         3             2017-03-17   01:00:00         05:00:00
244         4             2017-03-17   01:00:00         05:00:00
244         5             2017-03-17   01:00:00         05:00:00
244         6             2017-03-17   01:00:00         05:00:00
244         7             2017-03-17   01:00:00         05:00:00
244         8             2017-03-17   01:00:00         05:00:00
244         9             2017-03-17   01:00:00         05:00:00
244         10            2017-03-17   01:00:00         05:00:00
244         11            2017-03-17   01:00:00         05:00:00
244         12            2017-03-17   01:00:00         05:00:00
244         13            2017-03-17   01:00:00         05:00:00
244         14            2017-03-17   01:00:00         05:00:00
244         15            2017-03-17   01:00:00         05:00:00
244         1             2017-03-17   05:00:00         22:00:00
244         2             2017-03-17   05:00:00         22:00:00
244         3             2017-03-17   05:00:00         22:00:00
244         4             2017-03-17   05:00:00         22:00:00
244         5             2017-03-17   05:00:00         22:00:00
244         6             2017-03-17   05:00:00         22:00:00
244         7             2017-03-17   05:00:00         22:00:00
244         8             2017-03-17   05:00:00         22:00:00
244         9             2017-03-17   05:00:00         22:00:00
244         10            2017-03-17   05:00:00         22:00:00
244         11            2017-03-17   05:00:00         22:00:00
244         12            2017-03-17   05:00:00         22:00:00
244         13            2017-03-17   05:00:00         22:00:00
244         14            2017-03-17   05:00:00         22:00:00
244         15            2017-03-17   05:00:00         22:00:00

I left out the ID row as it really isn't important in this case. Also- yes- I realize that this table is very very redundant- It isn't something I can currently fix as I am not allowed to- I can only work on getting the aforementioned summing function working.

The end goal is to pair off the distinct StartTime and EndTime pairs and then find the date difference of those- and then, for the entire month- find the sum of all the entries.

This is as far as I have gotten:

Using:

SELECT
UserID,
DATEDIFF(HOUR, StartTime, EndTime) AS 'Hours Worked'
FROM TblPASchedule WHERE UserID = 244 AND MONTH(ScheduleDate) = 03 AND Year(ScheduleDate) = 2017
GROUP BY UserId, StartTime, EndTime

I get the output to be: 

UserID      Hours Worked
----------- ------------
244         4
244         17

But I am not too sure about where I should go from here.

I eventually need to make it group these sums based on the UserIDs, but one step at a time I suppose. I am using a where clause to work with a single id for now...

Upvotes: 1

Views: 172

Answers (2)

Nikhil Goud
Nikhil Goud

Reputation: 584

This query gets all the distinct sets of UserID, Starttime and Endtime

;WITH CTE AS
 (SELECT DISTINCT UserID, StartTime, EndTime  FROM [dbo].[TblPASchedule])

SELECT SUM(DATEDIFF(MINUTE, StartTime, EndTime))/60.0 AS 'Hours Worked', UserID
FROM CTE GROUP BY UserID

RESULTS look like this

Hours Worked    UserID
1.666666        19
1.233333        37
0.500000        38

Upvotes: 1

Venu
Venu

Reputation: 475

Have you tried wrapping additional sub query on top of your groups?

SELECT UserId, SUM('Hours Worked') as 'Hours Worked' FROM (
  SELECT
   UserID,
   DATEDIFF(HOUR, StartTime, EndTime) AS 'Hours Worked'
   FROM TblPASchedule WHERE UserID = 244 AND MONTH(ScheduleDate) = 03 AND Year(ScheduleDate) = 2017
   GROUP BY UserId, StartTime, EndTime
) AS temp
GROUP BY UserId

Upvotes: 0

Related Questions