Need Top 5 Syncs From Each Handheld

[SgtJarrow]

I have the following SQL statement:

SELECT E.RecName, H.RecKey, L.TimeStamp
FROM Employees AS E
JOIN Handhelds AS H ON H.EmpNid = E.EmpNid
JOIN Sessions AS S ON S.EmpNid = E.EmpNid
JOIN HandheldLog L ON L.SessionNid = S.SessionNid
WHERE E.PresellerFlag = 1 AND L.MethodName = 'SyncGetLatest'
AND L.TimeStamp BETWEEN '2006-08-01 00:00:00.001' AND '2006-10-04 00:00:00.001'
ORDER BY H.RecKey, E.RecName, L.TimeStamp DESC

E.RecName is the name of the sales route, H.RecKey is the handheld used to sync with that route and L.TimeStamp is the time of the sync.

My resident SQL expert is out on maternity leave. The query returns the right data, but I only want the last 5 syncs for each handheld. My SQL is not good enough to fix this to only give me that....

Anyone care to help a VB guy out? Thanks if so...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[kxramse]

I would do a select of RecKey from handhelds, and then join my results to your query above, but add in your query above a "Top 5" statement. Make sure the subselect uses the same RecKey as the driving table for the select statement. Add a grouping as well.

Like this:

select * from (SELECT top 5 E.RecName, H.RecKey, L.TimeStamp
FROM Employees AS E
	JOIN Handhelds AS H ON H.EmpNid = E.EmpNid and h.RecKey=DrivingTableHandhelds.RecKey
	JOIN Sessions AS S ON S.EmpNid = E.EmpNid
	JOIN HandheldLog L ON L.SessionNid = S.SessionNid
WHERE E.PresellerFlag = 1 AND L.MethodName = 'SyncGetLatest'
	AND L.TimeStamp BETWEEN '2006-08-01 00:00:00.001' AND '2006-10-04 00:00:00.001'
group by h.RecKey
ORDER BY H.RecKey, E.RecName, L.TimeStamp DESC
) aliasfortop5handhelds, handhelds as DrivingTableHandhelds

I don't know whether I've connected the two together right and whether I understood your business process enough to give you a SQL statement. Oh, that and whether you need to put the top command in front of the RecKey instead of the RecName

If I missed it, describe what you think I missed and I will try to re-write it. First run it and see what you get.

 

[SgtJarrow]

I guess this is not as simple as I thought....That SQL gives me a table/alias not used int query error but thanks for trying. I am trying to adapt it..

My SQL statement above produces about 5000 rows returned with my current data. It is the correct data, but I am only looking for about 300 rows or so, as I only have about 60 active salesmen right now. Perhaps it will help if I describe both what I am trying to accomplish and my data structure a bit...

We seem to be having a problem with our salesmen not syncing their handhelds on a regular basis. This is a concern, because customer service and accounting are having to correct invoices due to pricing changes. These pricing changes will be picked up by the handheld.

The Employee table has EmpNid, RecName, PresellerFlag among other fields.
The Handheld table has HandheldNid, RecKey, EmpNid among other fields.
The Sessions table has SessionNid, EmpNid, HandheldNid among other fields.
The Handheldlog table has HandheldLogNidm SessionNid, TimeStamp, MethodName among other fields.

The goal is to get the last 5 syncs for each employee where the MethodName = 'SyncGetLatest'. The SQL I first posted gets me all of those syncs during the time from I put in my WHERE clause.

As I stated, SQL is not my strongpoint. Perhaps my explanation will help. If not, that's fine as well....I can just drop the 5000 lines into an Excel spreadsheet and the Customer Service and Accouting teams can wade through the data. Unfortunately, I think they are going to be asking for a report based off this data.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[arrowhouse]

If you are using 2005 take a look at the rank function in books online. I've never used it myself but something like:

RANK() OVER (PARTITION BY E.RecName, H.RecKey order by L.TimeStamp(desc)) as RANK
looks like it might do the trick.

depending on how you want to treat equally ranked ones you might need rank_dense instead.

The restriction you have on the timestamp date will mean that you won't get anything back for handhelds that were last refreshed before '2006-08-01 00:00:00.001' is that really what you want?

 

[kxramse]

I'm not sure if you're working on mstrmage1768's idea or hoping I can rework things after your message.

If arrowhouses solution doesn't appear to work, let us know, I haven't used rank before, I wouldn't mind seeing the finished product when you are done. Otherwise I think what I gave you is within stricking distance if it can be tweaked around the error you are getting. If we return to that, include the exact SQL you tried and the exact error. Also the groupings might need to be by employee and handheld, I don't know if you tried that yet.