[jimmyo88]

Hello, I was wondering if anyone could offer some assistance with an SQL query. I basically have a table which logs when users sign in and out of an office. I need to be able to find out all the current users who are signed in on a given day but have not signed out.

There are four columnns and this is an example of the data returned when i run the query

Select * From tbl_log
WHERE access_date Between '2011-06-27' And '2011-06-28' AND  worker_id=60;


/////RESULTS////
  user_id       Date_accessed             signed_in    Access_Id
    60	     2011-06-27 08:45:25.000	     1	       45844
    60	     2011-06-27 09:38:04.000	     0	       44936

At the moment, I am just trying to keep things simple and return a record for a specified day where the user has signed in but not signed out.

Would anyone be able to offer any advice regarding this?

thanks in advance.

This post has been edited by jimmyo88: 28 August 2012 - 03:54 PM

[modi123_1]

How does your table show someone signed in and then signed out?


Basically you would use the convert to get the date only (I am partial to style 101), and that same date with with a day added... so you get your right 'between' query..

http://msdn.microsof...y/ms187928.aspx

http://msdn.microsof...y/ms186819.aspx

From there you just plug in your condition for telling people who have checked in - aka

signed_in = 1

[e_i_pi]

Keep in mind that the BETWEEN clause uses closed boundaries. I.e., this:

DateColumn BETWEEN '2012-01-01' AND '2012-01-02'

...is equivalent to this...

DateColumn >= '2012-01-01' AND DateColumn <= '2012-01-02'

Also, worthy of note is that '2012-01-01' gets interpreted as '2012-01-01 00:00.000'.

One last gotcha, timezones. Make sure you know where the time boundaries are being generated/calculated. You may require timezone conversion if the "sign-in" program is configured to a different timezone that your reporting program.

[jimmyo88]

thanks for the reply although I should have been a bit clearer in the original post.

In the above results you'll notice the user is signed in as indicated by a 1 in the column at
2011-06-27 08:45:25.000.
He then signs out at
2011-06-27 09:38:04.000 as indicated by a 0.
I have thought more and as the query must show which users are signed in at any given time, so the time part of the datetime is essential.

This is what ive come up with so far

SELECT  *
FROM tbl_log
WHERE access_date between '2011-06-15 00:00:00.000' AND  '2011-06-15 09:55:25.000'  AND signed_in = 1 

However the problem is that the query would still show user_id 60 as being signed in as he technically did sign in between this timescale.
I hope this makes some sense..

[modi123_1]

Ah.. that does indeed.. how does that cover someone who works from say 11pm to 8am the next day, or is everything on the assumption that there will be always pairs except in the instances where the user hasn't checked out?

[jimmyo88]

looking through the database there are no 'night shifts' so to speak. The earliest anyone arrives is 6am and the latest anyone signs out is 11pm. That means I don't have to worry about shifts crossing different days. My main problem is people showing in the query as signed_in (1) when they should not be showing at all. I need to say somehow... If the person has signed out between this time frame. Do not show them in the query.

I've also noticed that each worker only signs in and out once per day. I'm not sure if this helps the puzzle?

This post has been edited by jimmyo88: 28 August 2012 - 07:25 PM

[modi123_1]

If say using this data:

ID	dtEntered		signed_in
---------------------------------------
60	2011-06-27 08:45:25.000		1
60	2011-06-27 09:38:04.000		0
70	2011-06-26 09:38:04.000		1
70	2011-06-26 13:38:04.000		0
60	2011-06-28 08:50:00.000		1
80	2011-06-28 08:30:00.000		1

.. and a query that groups by id, the day, and a specific count number for that day (being each day should have max 2 entries.. and any single entry is thought to be a sign in)

SELECT [ID]
      ,convert(varchar(8), dtEntered, 101) as days
  FROM [TestDatabase].[dbo].[jimmyo88]
group by id, convert(varchar(8), dtEntered, 101)
having COUNT(*) = 1

yields this:

ID	days
60	06/28/20
80	06/28/20

[jimmyo88]

Thanks modi, this looks closer to what I am trying to achieve. I fell asleep last night as it was around 4am here in the uk. I'll give it a go tonight after I finish work and let you know how I get on. Thanks a million.

[jimmyo88]

Okay, so i've been playing about with this since I got home from work but I still have the same problem. I need the user to be able to execute the query which shows which workers are currently signed in at that paticular time.

This has helped but still does not show the correct results. E.g if this query was executed at 4:15pm, user 60 has signed it at 8am and signed out at 10am. Even though user 60 has signed out, he is still displayed in the results as being in work.

SELECT [worker_id]
      ,convert(varchar(23), access_date, 13) as Signed_in
  FROM [ConstructionCompanyDB].[dbo].[access_transaction]
WHERE access_date between '2011-06-27 00:00:00.000' AND  '2012-06-27 16:14:25.000'   AND entering_site = 1
group by worker_id, convert(varchar(23), access_date, 13)
having COUNT(*) = 1

I'm starting to think that this is something I should maybe just do in the software. Is it even possible to do this in SQL? I'm thinking that it would be easier to just execute a query that shows all the workers between two different dates/times, store the results to an array list and then run a loop which removes all entries which have a duplicate worker_id (i.e. workers who have signed in and back out). This should leave me a list of workers which have only signed in but not signed out.

What do you think about this? Should I continue plugging with the SQL or should I go with the software approach. I really wanted to do this in SQL but its proving frustrating.

This post has been edited by jimmyo88: 29 August 2012 - 03:48 PM

[e_i_pi]

It's because you're grouping by the access date, so the count is going to be 1 in every case. Let's try an approach where a subquery is used. If we generate a subquery that simply gets the worker_id and the count for the day, then you should be right from there:

SELECT
	[worker_id]
FROM [ConstructionCompanyDB].[dbo].[access_transaction]
WHERE [access_date] BETWEEN '2011-06-27 00:00:00.000' AND  '2012-06-27 16:14:25.000'
GROUP BY [worker_id]
HAVING COUNT(*) = 1
AND MAX([entering_site]) = 1

This query will generate a set of worker_ids that have a sign-in record for the period, but no sign-out record. From there you can JOIN against this as if it were a table, so long as you wrap it in brackets and give it a name. Rather than write out the code for you, I'll give you the general format, and you can work out the rest

SELECT
	Table.Value,
	SubQuery.Value
FROM Table
// Note the brackets below
INNER JOIN (
	SELECT
		Value,
		ID
	FROM MyOtherTable
) AS SubQuery ON SubQuery.ID = Table.MyOtherTableID
// ...and that I have named it "SubQuery"

[jimmyo88]

Okay, i think i understand what your saying. The code at the top allows us to create a query which returns a list of workers who have signed in but not signed out.
Next we use an inner join within the same table which will allow us to pull up the other fields for the workers in the sub query.
Does that mean that the code we've used to get the initial list of workers should be going in the brackets as this is the initial subquery were searching for?

[e_i_pi]

That's right. The first query generates the list of workers we need. We use that query as a subquery, which is essentially like using it as a table or a view. The first query goes inside the brackets, acts in the same manner as a table, and we "filter" out the results we don't want by using the INNER JOIN.

This post has been edited by e_i_pi: 29 August 2012 - 07:59 PM

[jimmyo88]

SUCCESS!!
Thanks a lot for both of your help. I really feel I have learnt a lot from these posts.

SELECT
	access_transaction.worker_id AS TBL_ID,
	SubQuery.worker_id AS SUBQ_ID,
	access_transaction.access_date,
	access_transaction.entering_site

FROM 
	access_transaction
INNER JOIN (
	SELECT
	[access_transaction].[worker_id]
	FROM [ConstructionCompanyDB].[dbo].[access_transaction]
	WHERE [access_date] BETWEEN '2011-06-28 00:00:00.000' AND  '2011-06-28 23:14:25.000'
	GROUP BY [worker_id]
	HAVING COUNT(*) = 1
AND MAX( CAST ([entering_site ] AS INT)) =1)
AS SubQuery ON [SubQuery].[worker_id] = [access_transaction].[worker_id]
WHERE [access_date] BETWEEN '2011-06-28 00:00:00.000' AND  '2011-06-28 23:14:25.000';

I can now run a query specifing two date time constraints and know which users are currently signed in at that time. Brilliant.