SQL to return distinct IDs matching criteria

[Deutz]

Hi, and thanks in advance.

I am using Access 2010 and have a single table (tblData) with about 100,000 rows with an ID field (ID) and a date/time field (Tmst).
Each ID can appear one or more times with one of two dates. In the example provided, the date can be either 23/11/2015 or 1/05/2015. I want to return the ID if it has at least one instance of 23/11/2015 and no instances of 1/05/2015. I also want the IDs returned to be distinct.

ID	Tmst
123	23/11/2015
123	1/05/2015
123	23/11/2015
111	23/11/2015
123	1/05/2015
111	23/11/2015
002	1/05/2015
111	23/11/2015
008	23/11/2015

From the data in the example the SQL should return: IDs: 111, 008
I tried an EXISTS subquery but it took forever to run on our slow machines so I gave up with that.

I also tried this left join SQL which ran really fast but for some reason included some IDs that have both dates, which is not what I want.

SELECT DISTINCT X.ID FROM (SELECT DISTINCT tblData.ID FROM tblData WHERE int([Tmst])=#23/11/2015#) AS X LEFT JOIN (SELECT DISTINCT tblData.ID FROM tblData WHERE int([Tmst])=#1/05/2015#) AS Y ON X.ID = Y.ID WHERE (((Y.ID) Is Null));

Regards
Deutz

 

[The_Doc_Man]

My question is to tell us in ENGLISH (not in computerese) what you want to do. Are you seeking those index values that only have one date associated with them regardless of the one date and regardless of how many times they appear? Is it a specific date only?

What, in ENGLISH, are the selection criteria? Your description left me confused.

 

[MarkK]

What about something like...

SELECT ID
FROM Table1
GROUP BY ID
HAVING -Sum(tmst = #11/23/2015#) >= 1 
   AND -Sum(tmst = #5/1/2015#) = 0;

What we're doing is summing the value of the equality of whether tmst equals the date to test for. In this way we can count the occurrence of a particular date for a particular id.

 

[Deutz]

My question is to tell us in ENGLISH (not in computerese) what you want to do. Are you seeking those index values that only have one date associated with them regardless of the one date and regardless of how many times they appear? Is it a specific date only?

What, in ENGLISH, are the selection criteria? Your description left me confused.

Sorry for the confusion. English is my second language after Computerese.
I want the ID values that have a specific date (23/11/2015) associated with them but not if they also have another specific date (1/05/2015) associated with them. It does not matter how many times they appear.

Thanks
Deutz

 

[Galaxiom]

I also tried this left join SQL which ran really fast but for some reason included some IDs that have both dates, which is not what I want.

SELECT DISTINCT X.ID FROM (SELECT DISTINCT tblData.ID FROM tblData WHERE int([Tmst])=#23/11/2015#) AS X LEFT JOIN (SELECT DISTINCT tblData.ID FROM tblData WHERE int([Tmst])=#1/05/2015#) AS Y ON X.ID = Y.ID WHERE (((Y.ID) Is Null));

SQL Dates need to #mm/dd/yyyy# format

 

[Galaxiom]

I assume you are using Int() to get the date from DateTime.

If the datetime fields are indexed you can probably improve performance by changing to this kind of condition:

WHERE [Tmst] Between #5/1/2015# AND  #5/1/2015 23:59:59#

 

[Deutz]

What about something like...

SELECT ID
FROM Table1
GROUP BY ID
HAVING -Sum(tmst = #11/23/2015#) >= 1 
   AND -Sum(tmst = #5/1/2015#) = 0;

What we're doing is summing the value of the equality of whether tmst equals the date to test for. In this way we can count the occurrence of a particular date for a particular id.

Thanks MarkK, I used your SQL and it worked as expected. Also, thanks to Galaxiom for your format dates tip which I had not implemented correctly.

 

[Galaxiom]

Your original query with the date format corrected (and the unnecessary Int() removed since the dates apparently do not include a time component) would vastly outperform Mark's suggestion which processes and groups every record in the table before testing for the dates.

The Where clauses in your subqueries are applied at the start and immediately eliminate any records that don't have the dates of interest.