This website is not affiliated with, sponsored by, or approved by SAP AG.

Refining results of query to show only one entry per user

All postings related to Business One only

Moderators: Snowy, thx4allthefish

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

Postby 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
--CODE BY SBOHUB.COM
WITH summary AS (
   SELECT T1.U_NAME,
   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,
   CAST(ROW_NUMBER() OVER(PARTITION BY T0.UserCode
                     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')
SELECT S.*
   FROM summary S
   WHERE Row = 1
pcanale52
 
Posts: 1
Joined: Fri Dec 06, 2013 8:37 pm
Location: Minneapolis, MN.

Return to Business One

Who is online

Users browsing this forum: No registered users and 1 guest





loading...


This website is not affiliated with, sponsored by, or approved by SAP AG.