SQL Group By / Having Syntax

Last Post 13 Dec 2012 05:26 AM by Mike Givens. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Mike Givens
New Member
New Member

--
13 Dec 2012 05:26 AM
I have two tables:



Users (columns UserID (PK), UserFullname, and others not in this query)
UserActionLog (columns logDate (datetime), logUser (FK), logAction, and others not in this query)



My query:



SELECT Users.UserFullname, ual.logDate AS TheDate,
(SELECT MIN(logDate) AS TimeIn
FROM UserActionLog
WHERE (logAction = 'Logged in') AND (CONVERT(varchar, logDate, 101) <= '12/13/2012')) AS TimeIn,
(SELECT MAX(logDate) AS TimeOut
FROM UserActionLog AS UserActionLog_1
WHERE (logAction = 'Closed Database') AND (CONVERT(varchar, logDate, 101) <= '12/13/2012')) AS TimeOut, ROUND(DATEDIFF(second,
(SELECT MAX(logDate) AS TimeOut
FROM UserActionLog AS UserActionLog_5
WHERE (logAction = 'Logged in') AND (CONVERT(varchar, logDate, 101) <= '12/13/2012')),
(SELECT MAX(logDate) AS TimeOut
FROM UserActionLog AS UserActionLog_4
WHERE (logAction = 'Closed Database') AND (CONVERT(varchar, logDate, 101) <= '12/13/2012'))) / 3600, 1) - ROUND(DATEDIFF(second,
(SELECT MAX(logDate) AS TimeOut
FROM UserActionLog AS UserActionLog_3
WHERE (logAction LIKE 'Time Out%') AND (logAction <> 'Time Out') AND (CONVERT(varchar, logDate, 101) <= '12/13/2012')),
(SELECT MAX(logDate) AS TimeIn
FROM UserActionLog AS UserActionLog_2
WHERE (logAction = 'Time In') AND (CONVERT(varchar, logDate, 101) <= '12/13/2012'))) / 3600, 1) AS HoursWorked, Users.UserID,
(SELECT logDate AS ClockOut
FROM UserActionLog AS UserActionLog_6
WHERE (logAction LIKE 'Time Out%') AND (logAction <> 'Time Out') AND (CONVERT(varchar, logDate, 101) <= '12/13/2012')) AS ClockOut,
(SELECT logDate AS ClockIn
FROM UserActionLog AS UserActionLog_7
WHERE (logAction = 'Time In') AND (CONVERT(varchar, logDate, 101) <= '12/13/2012')) AS ClockIn,
(SELECT logAction AS TypeOfAbsence
FROM UserActionLog AS UserActionLog_8
WHERE (logAction LIKE 'Time Out%') AND (logAction <> 'Time Out') AND (CONVERT(varchar, logDate, 101) <= '12/13/2012')) AS TypeOfAbsence
FROM Users INNER JOIN
UserActionLog AS ual ON ual.logUser = Users.UserID
GROUP BY Users.UserFullname, ual.logDate, Users.UserID, ual.logAction
HAVING (Users.UserID = 1) AND (CONVERT(varchar, ual.logDate, 101) >= '12/13/2012' OR
CONVERT(varchar, ual.logDate, 101) <= '12/13/2012') AND (ual.logAction = 'Logged in')
ORDER BY TheDate



My results:



UserFullname TheDate TimeIn TimeIn Hours

Mike Givens 12/13/2012 8:00:00 AM 12/13/2012 8:00:00 AM 12/13/2012 5:00:00 PM 8 1 12/13/2012 12:00:00 PM 12/13/2012 1:00:00 PM Time Out Lunch


Mike Givens 12/14/2012 7:59:00 AM 12/13/2012 8:00:00 AM 12/13/2012 5:00:00 PM 8 1 12/13/2012 12:00:00 PM 12/13/2012 1:00:00 PM Time Out Lunch



The 2nd row is not showing the values that are needed because of my query inadequacy.



Here's the data in table, UserActionLog:


logID logDate logUser logAction

1 12/13/2012 8:00:00 AM 1 Logged in
2 12/13/2012 12:00:00 PM 1 Time Out Lunch
3 12/13/2012 1:00:00 PM 1 Time In
4 12/13/2012 5:00:00 PM 1 Closed Database
5 12/14/2012 7:59:00 AM 1 Logged in
6 12/14/2012 11:45:00 AM 1 Time Out Lunch
7 12/14/2012 12:48:00 PM 1 Time In
8 12/14/2012 5:15:00 PM 1 Closed Database



Here's the data in table, Users:



UserID UserFullname

1 Mike Givens



This is the query results I would like for a query to return:



UserFullname TheDate TimeIn TimeOut

Mike Givens 12/13/2012 8:00:00 AM 12/13/2012 8:00:00 AM 12/13/2012 5:00:00 PM 8 1 12/13/2012 12:00:00 PM 12/13/2012 1:00:00 PM Time Out Lunch


Mike Givens 12/14/2012 7:59:00 AM 12/14/2012 7:59:00 AM 12/14/2012 5:15:00 PM 8 1 12/14/2012 11:45:00 AM 12/14/2012 12:48:00 PM Time Out Lunch



Any help would be very appreciated.


Acceptable Use Policy
---