I'm having an issue trying to write a query to determine the status of users on our website. When a user logs in, every action is tracked within an action table. This includes logging in, logging out, and selecting a campaign as far as things we're concerned about.
I'm attempting to determine which users have logged in (done by comparing the last login and logout actions) and then show how long they've logged into a particular campaign. In an attempt to keep this to one query, I've been stumbling on writing a query to return all of the users that are logged in. I thought I was on to something when I came up with this query:
SELECT Emp.LastName, Emp.FirstName, Campaign.cpnname, ActionInfo.SD
FROM (
SELECT unqEmpID, LastName, FirstName
FROM Employees
WHERE [Type] = 2
) AS Emp
INNER JOIN (
SELECT TOP 1 actiontypeid, actionid, unqEmpID
FROM actions
WHERE actiontypeid IN (1, 2)
ORDER BY actionid DESC
) AS Actions
ON Emp.UnqEmpID = Actions.unqEmpID
INNER JOIN (
SELECT TOP 1 unqEmpID, actionID, actioninfo, DATEDIFF(ss,actionwhen,get
date()) AS SD
FROM actions
WHERE actiontypeid=20
ORDER BY actionid DESC
) AS ActionInfo
ON ActionInfo.unqEmpID = Emp.unqEmpID
AND ActionInfo.actionid > Actions.actionid
INNER JOIN (
SELECT cpnname, cpnid
FROM campaigns
) AS Campaign
ON Campaign.cpnid = ActionInfo.actioninfo
ORDER BY lastname ASC, firstname ASC
For reference, actiontype 1 is logging in and actiontype 2 is logging out. Selecting a new campaign is actiontypeid 20. Lastly, employees of Type = 2 are the ones I'm concerned about.
To summarize, I need to pull all users of type 2 out of the database where actiontypeid 1 has occurred after actiontypeid 2. My query is programmatically however I know the first inner join is where my problem is.
Start Free Trial
