Joining to a table on the full MAX entry

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Joining to a table on the full MAX entry

Author

Topic

2400hrs
Starting Member

4 Posts

Posted - 2006-10-03 : 14:16:34

We have a really bad data output from an ancient POS (Point of Sale) system written externally:

Activity (
AssistantID char(25),
Logon datetime,
Logoff datetime,
Department char(3),
POSID char(3),
)

Sale (
AssistantID char(25),
TransactionTime datetime,
SalesID char(12),
)

This with example data basically looks like this:


AssistantID  Logon                    Logoff            Department
ABC          2006-01-01 15:20:00.000  2006-01-01 15:25  DEP1
ABC          2006-01-01 15:21:00.000  2006-01-01 15:59  DEP2
ABC          2006-01-01 16:00:00.000  2006-01-01 16:20  DEP1
DEF          2006-01-01 15:35:00.000  2006-01-01 17:00  DEP3

AssistantID  TransactionTime          SalesID
ABC          2006-01-01 15:22:00.000  X123
DEF          2006-01-01 16:21:00.000  X124

There are some problems:

Somebody can login twice. Its almost certainly because they lock one terminal without logging off, swipe in to another, and the first eventually times out.

The requirement is that for each Sale, we match it to the department is was performed in. The agreed logic, is that where there is a discripency, we take the last valid login.

For example, for sale X123, we would match it to DEP2. This cannot be guaranteed 100% accurate, but is the best the system provides us.

So what I need to do, is join from the Sale, to the logon activity, but only on the maximum logon, where the transaction time is between the logon and the logoff, and the AssistantID is the one from the Sale.

[CODE]
SELECT a.AssistantID,b.Department
FROM sale a
JOIN activity b
ON a.AssistantID = b.AssistantID
AND a.TransactionTime BETWEEN b.logon AND b.logoff
WHERE a.SaleID = 'X123'
[/CODE]

A normal join will bring back two rows for the sale X123. What I expect from the query is:


Assistant ID         Department
ABC                  DEP2

This is then used to produce the total sales per department.

The data is very large, there can be 10 000s of activity data, and lots of sales data, and so efficiency might be a problem.

[CODE]
SELECT a.AssistantID,b.Department
FROM sale a
JOIN (
SELECT max(logon),logoff,Department,AssistantID
FROM activity
GROUP BY Department,AssistantID,logoff
) b
ON a.AssistantID = b.AssistantID
AND a.TransactionTime between b.logon AND b.logoff
[/CODE]

This doesn't help because I have to group by all the fields, which still leaves 2 answers. I need to get the max entry and its associated fields, in that time range for the user.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources