[jimmyo88]

Okay, so I have a table of each workers health and safety records. Each worker has completed two different health and safety applications, one in each year. Below is an example of the health and safety records ordered by worker_id. In a seperate table, I have a list of dates which all of the workers have accessed the site. What I am trying to is to create a query which will see if any employees had accessed the site while their expiry date had expired.

hsid    wID        hsref          hsStartDate               hsExpiryDate
86	1	ADG249	2010-10-14 00:00:00.000	2011-10-14 00:00:00.000
186	1	QJX043	2009-10-14 00:00:00.000	2010-10-14 00:00:00.000
167	2	UQC444	2009-05-03 00:00:00.000	2010-05-03 00:00:00.000
67	2	UFZ687	2010-05-03 00:00:00.000	2011-05-03 00:00:00.000
8	3	QUM886	2010-02-14 00:00:00.000	2011-02-14 00:00:00.000
108	3	JOG187	2009-02-14 00:00:00.000	2010-02-14 00:00:00.000
112	4	SKY246	2009-03-01 00:00:00.000	2010-03-01 00:00:00.000
12	4	WTO718	2010-03-01 00:00:00.000	2011-03-01 00:00:00.000

This is what I have come up wih..#

		(SELECT 			  
			   worker_hs_card.worker_id,
			   worker_hs_card.hs_start_date, 
			   worker_hs_card.hs_expiry_date,
			   access_transaction.access_date		
			  
		FROM worker_hs_card
		INNER JOIN access_transaction 
		 ON  worker_hs_card.hs_expiry_date <  access_transaction.access_date
		AND   worker_hs_card.worker_id = access_transaction.worker_id  AND worker_hs_card.worker_id = 1 )
		ORDER BY worker_hs_card.worker_id	

While this technically works, because each employee has two health and safety records, it checks the access date against both the old and new records. E.G if we look at worker_id 1

hsid    wID      hsref          hsStartDate               hsExpiryDate
86	1	ADG249	2010-10-14 00:00:00.000	2011-10-14 00:00:00.000
186	1	QJX043	2009-10-14 00:00:00.000	2010-10-14 00:00:00.000

worker_id 1 has an expiry date of 2010 and 2011. This means the results that are given when running the query are...

wID            hsStartDate             hsExpiryDate               accessDate
1	2009-10-14 00:00:00.000	2010-10-14 00:00:00.000	2011-05-29 07:19:17.000
1	2009-10-14 00:00:00.000	2010-10-14 00:00:00.000	2011-05-29 14:30:28.000
1	2009-10-14 00:00:00.000	2010-10-14 00:00:00.000	2011-05-30 12:18:43.000
1	2009-10-14 00:00:00.000	2010-10-14 00:00:00.000	2011-05-30 14:51:17.000
1	2009-10-14 00:00:00.000	2010-10-14 00:00:00.000	2011-05-31 12:29:15.000
1	2009-10-14 00:00:00.000	2010-10-14 00:00:00.000	2011-05-31 16:07:52.000
1	2009-10-14 00:00:00.000	2010-10-14 00:00:00.000	2011-06-01 13:04:09.000

Its doing what its supposed to do by showing accesses which are beyond the expiry date but it is also showing results I do not want to see because each user has two expiry dates. I need to select the maximum expiry date for each user.

Ive tried adding clauses such as hsExpiryDate = '2011%' AND accessDate = '2011%' so that it only matches accesses with expirys of that year but have had no luck. Is anyone able to help me fix this problem?

Many thanks to anyone who can help.

[fromTheSprawl]

Use MAX?
Max

Select MAX(hsExpiryDate) as theExpiryDate

Just make sure that you create your query properly that it only gets the max per workerID, not the max of the whole column of hsExpiryDate itself.

[jimmyo88]

thanks fromTheSprawl, I wasn't aware you could do that with MAX. How would I go about doing this? Would I need to use a sub-query to get the highest date for each worker and then use the sub query with my original query?

[fromTheSprawl]

I'm not really good with SQL queries, but yeah, the general idea is to get the larger of them.

In a simple database where theoretically we have a table with columns workerID and expiryDate and values can double with this example:
workerID expiryDate
1 November 10, 2012
1 December 10, 2012
2 September 10, 2012
2 October 10, 2012
3 April 10, 2012
3 July 10, 2012

I'd do this simple query:

SELECT workerID, MAX(expiryDate) group by workerID;

In fact, you can test it now just by using wID, hsExpiryDate and yourTableName instead. Now to get the query the way you wanted with the extra columns, try it the way you said you'd do it. Alas, my skills on SQL are lacking.

[jimmyo88]

thanks for the help managed to get the results I was looking for.

Many thanks

[fromTheSprawl]

No problem dude.