Query-restrict output

Question

Query-restrict output

Peter D-251663

Ten Centuries

Points: 1010
More actions
November 14, 2012 at 5:11 pm

#266961

I have a system that logs users who use the system daily. Some user may log in several times during the day which creates additional entries into the logging table in a date format as "'2012-06-01 00:50:59.000' "
What I'm trying to do is query and return back just one entry for the user (s) even if they are captured in the logging table more than once per day
(i.e. jbaker .'2012-06-01 00:50:59.000'
'2012-06-01 12:50:59.000'
'2012-06-01 15:50:59.000' )
I tried selecting distinct user (oprid) from the logging table (psaccesslog)but still came back with multiple entries for the day.
SELECT * FROM PSACCESSLOG
WHERE LOGINDTTM BETWEEN '2012-06-01 00:50:59.000' AND '2012-07-01 00:50:59.000'
ORDER BY OPRID, LOGINDTTM
Appreciate insight

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

When there are multiples which one do you want? It be far more helpful if you could post ddl and sample data with your problem. See the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

I don't know the whole structure of your table, but this might give you something to start.

You might need to add some fields or add the extra time to get the 00:59:50.

Be aware that BETWEEN is inclusive for both values, so you might have values that get in two days.

CREATE TABLE #PSACCESSLOG(

opridvarchar(6),

logindttmdatetime)

INSERT INTO #PSACCESSLOG

SELECT 'jbaker', '2012-06-01 00:50:59.000' UNION ALL

SELECT 'jbaker', '2012-06-01 12:50:59.000' UNION ALL

SELECT 'jbaker', '2012-06-01 15:50:59.000'

SELECT oprid,

DATEADD( dd, DATEDIFF( dd, 0, LOGINDTTM), 0) AS LOGINDT,

COUNT(*) AS entries

FROM #PSACCESSLOG

WHERE LOGINDTTM BETWEEN '2012-06-01 00:50:59.000' AND '2012-07-01 00:50:59.000'

GROUP BY oprid, DATEADD( dd, DATEDIFF( dd, 0, LOGINDTTM), 0)

ORDER BY OPRID, LOGINDT

DROP TABLE #PSACCESSLOG

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Peter D-251663 Ten Centuries Points: 1010 More actions · Answer 3

Thanks looks like this will work. Thanks everyone for your input. Evena 50yr can still learn things..:cool: