Refining results of query to show only one entry per user

All postings related to Business One only

Moderators: Snowy, thx4allthefish

Post Reply
Posts: 1
Joined: Fri Dec 06, 2013 8:37 pm
Location: Minneapolis, MN.

Re: Refining results of query to show only one entry per user

Post by pcanale52 » Mon Dec 09, 2013 9:52 pm

Hi there David, I apologize for the long time to wait for an answer. I just found this forum yesterday and I thought I'd take a stab at your problem. Hopefully you still need help so here is what I came up with. Feel free to ask me any questions you might have about the code. I tested it all day in my production environment and it seems to be working well.

One thing to note is that there is a bug with the USR5 table in SAP Business One. The time is in military, but if it is before 10 o clock the colon separator gets messed up. To read the time field, all you have to do is move the colon to the left one digit. For example, if the time reads 92:23:10 then move the colon so it reads basically 09:22:31. I used a SAP Note to code the SUBSTRING specially due to this bug, best I could do.

Good luck!

Code: Select all

WITH summary AS (
	LEFT(T0.Date, 12) AS Date,
	SUBSTRING (CAST ([Time] as char), 1,2) + ':' +SUBSTRING (CAST ([Time] as char), 3,2) + ':' +SUBSTRING (CAST ([Time] as char), 5,2) AS Time,
							ORDER BY T0.Date DESC, T0.Time DESC)AS INT) AS Row
	FROM USR5 T0 INNER JOIN OUSR T1 ON T0.UserCode = T1.USER_CODE WHERE T0.Date NOT LIKE '%3913%' AND T0.Action = 'I' AND T0.UserCode <> 'B1i')
	FROM summary S
	WHERE Row = 1

Post Reply