Select by Date Query help needed.

[blaketar]

I have a simple database that I am trying to select only distinct(uniqeid)'s from where their is a record with today’s stamp and also any record with a date from previous days.

id ¦ builddate ¦ uniqueid
22 ¦ 05-02-2006 ¦ 55214112
23 ¦ 05-02-2006 ¦ 55214112
24 ¦ 05-03-2006 ¦ 88120774
25 ¦ 05-12-2006 ¦ 55214112
26 ¦ 05-12-2006 ¦ 88120774
25 ¦ 05-12-2006 ¦ 33332110
26 ¦ 05-12-2006 ¦ 66881244

I would like it to return only the '55214112' and '88120774' records because these have dates which are stamped today and have previous stamps. The other two only have stamps today so they are not valid.

Anyway of achieving this in an sql query?

[syber]

This should give you what you want:

SELECT uniqueid 
 FROM mytable 
 WHERE uniqueid IN (SELECT uniqueid  
      FROM mytable  
      WHERE builddate = GETDATE()) 
 GROUP BY uniqueid 
 HAVING COUNT(*) > 1 

[blaketar]

Thanks for the query, unfortunately it doesnt seem to work for me, returns no rows. Will keep playing around..

[syber]

Do you get any rows when you leave the HAVING off? It could be that you don't have any duplicate uniqueid rows for a particular day.

[john_k]

GETDATE() will also return a time component with the date. So it will never equal any of your builddate values.

You don't say what type of database you are using, so the syntax may vary. For SQL Server, CONVERT(char, GETDATE(), 101) will return 05/12/2006.

If your server is configured to return dates in the format you listed in the initial post, then that may be all you need to do. Otherwise you will need to play with the formatting and/or use the YEAR, MONTH, and DAY functions to build a string from today's date.

[syber]

Good point, forgot about the time component.

How about this instead?

 SELECT uniqueid 
 FROM mytable  
 WHERE uniqueid IN (SELECT uniqueid 
      FROM mytable   
      WHERE builddate =  
        (SELECT MAX(builddate)  
         FROM mytable))  
 GROUP BY uniqueid  
 HAVING COUNT(*) > 1