Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with Grouping and max() Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 2:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 6:23 AM
Points: 8, Visits: 26

Basically the data is access data to a building. I want to establish if the person is in or out of the building. There are two access points one for IN and one for OUT. the SQL I have so far is as follows which is the select from a view called AD_in_OUT LOG

SELECT Username, thumbnailPhoto, [Display Name], Department, USERID, email, BADGENUMBER, NAME, CHECKTYPE, CHECKTIME
FROM dbo.[AD_in_Out LOG]

So I need to group by USERID and get the latest time for each USERID from CHECKTIME. I then need to establish if the latest time was an OUT or IN from the CHECKTYPE column. It seems simple, but I am not getting the required results. What is the best way to do this as the view looks up from 3 different tables as well so server overhead needs to be minimal.

thanks
Post #1363853
Posted Tuesday, September 25, 2012 2:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550

Try using ROW_NUMBER instead

WITH CTE AS (
SELECT Username, thumbnailPhoto, [Display Name], Department, USERID, email, BADGENUMBER, NAME, CHECKTYPE, CHECKTIME,
ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY CHECKTIME DESC) AS rn
FROM dbo.[AD_in_Out LOG])
SELECT Username, thumbnailPhoto, [Display Name], Department, USERID, email, BADGENUMBER, NAME, CHECKTYPE, CHECKTIME
FROM CTE
WHERE rn=1;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1363857
Posted Tuesday, September 25, 2012 3:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 104, Visits: 405
SELECT * FROM [AD_in_Out LOG] as tLog
INNER JOIN
(SELECT USERID, MAX(CHECKTIME) as LastTime
GROUP BY [userid]) as tREs
ON tLog.Userid = tRes.Userid
AND tLog.CHECKTIME = tREs.LastTime

Post #1364322
Posted Wednesday, September 26, 2012 1:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:37 PM
Points: 1,945, Visits: 2,921
>> Basically the data is access data to a building. I want to establish if the person is in or out of the building. There are two access points one for IN and one for OUT. The SQL I have so far is as follows which is the SELECT from a VIEW called Ad_In_Out_Log <<

Where is the DDL for the three tables? Do you often write code without it?

>> so I need to group by user_id and get the latest time for each user_id from check_time. I then need to establish if the latest time was an out or in from the check_type column. <<

This is the wrong way to model status attributes. They are the state of being over a temporal interval. That means they need a known start timestamp and a NULL-able ending timestamp. The NULL means that status is current.

Your check_type is a violation of First Normal Form (1NF) because check_time holds two different types of values. Its meaning is controlled by another column!

CREATE TABLE Roster
(badge_nbr CHAR(10) NOT NULL,
login_timestamp DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
logout_timestamp DATETIME2(0),
PRIMARY KEY (badge_nbr, login_timestamp),
CHECK (login_timestamp < logout_timestamp));

Show us the DDL and we can fix it easily. Then you might want to read the Rick Snodgrass on temporal queries in SQL; it is free as as download from the University of Arizona. 80-95% of the work in SQL is done in the DDL; screw it up and nothing can save you.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1364481
Posted Thursday, September 27, 2012 6:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 6:23 AM
Points: 8, Visits: 26
Thank You, this seemed to work as required.
Post #1365196
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse