Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
almontejr
Starting Member
5 Posts |
 Posted - 2008-03-30 : 19:47:58
|
| I created a report in sql reporting services for an access control application that keeps track of customer entries. This data is writtin into 4 columns:1)Date Time (Date & Time they Visited)2)User ID (ID of the Card Holder)3)Location ID (Location where entered the building)4)Visitors (qty OF PEOPLE ENTERING WITH THAT id)The issue i have is that sometimes the acces control application inserts the same record twice (except that the Visitor amount is always different on the second entry), (Pleas see example output below), What i would like to do is create a select statement that discards the duplicate records (select distinct doesnt seem to work since the visitor field already makes both records distinct).is there a way i can create a statement that says "if date , userid and location match, then only show me the record whtih the highest visitor count"?Fields - Date/Time ----------------------- User ID --------- Location ---- VisitorsResult 1- 3/30/2008 10:15:22 AM ---- 1234015000036477 ----- 40 -------- 1 Result 2- 3/30/2008 10:15:22 AM ---- 1234015000036477 ----- 40 -------- 5 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-30 : 20:10:12
|
| Just use a GROUP BY with the MAX aggregate function:SELECT [DateTime], UserID, Location, MAX(Visitors) AS VisitorsFROM YourTableGROUP BY [DateTime], UserID, LocationTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
renu
Starting Member
47 Posts |
Posted - 2008-05-16 : 02:40:38
|
| How can be the date same as before. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-16 : 04:30:18
|
quote:
Originally posted by renu
How can be the date same as before.
what did you mean? |
|
|
|
|
|
|
select statement issue