Solved SQL to display user events

[Kayleigh]

Hi,
I'm using a SQL query tool (not Access) on a database but not sure exactly how to write my SQL to get the desired results.
The table I'm querying is Events and it contains some of the following fields:

• EventID
• EventTime
• EventTypeID
• EventDetails
• UserID
• CardNumber

I would like to regularly find the users signed in and out in the last week. The SQL I used for this successfully is:

select * from sdk.Events
where eventTime > (select dateadd(week, -1, getdate()));

However it will show ALL events in this time period whilst I only want several results for each user (if possible first and last time signed in each day they were in otherwise just the days they were in).

Can anyone suggest how I can implement this into the query above?

 

[Minty]

You will need some subqueries , something like this will be the first one;

SELECT UserID, Min(Eventtime) as FirstLogin
FROM sdk.events
WHERE  EventTypeID = 3 -- (An arbitrary value?)
GROUP BY  UserID, Cast(EventTime as Date)

 

[Kayleigh]

That's great! And max for last login ?

 

[Minty]

Yes that would be the jist of it, you'll need to add your date clauses into the where statement for each one.

Then probably left join the results of each query from the queries to a user list to get your desired output.

 

[Kayleigh]

I'll be importing this data into my main DB so probably will do an append query with this data including a join to include both queries on one row for each user. The join will have to be on UserID and Date I guess?

 

[Minty]

If you have the data available I wouldn't import it unless it will disappear in the future.

If you have to import it do it as a single record with the event type.
This will maintain the normalised structure, if different event types are added that you need to capture you won't need to redesign your local table version of it.

 

[Kayleigh]

I will probably not be using the event types at all though. But thanks for the idea.