[AccessD] SQL Time - Problem solved

Here is the query that works

Select t1.* from dbo_DSS_StatusChanges t1 inner join (select distinct PID, CDate(INT(StatusDate)), max(StatusDate) as lastdatetime from dbo_DSS_StatusChanges
   group by PID, CDate(INT(StatusDate)))
 t2 on t2.PID = t1.PID AND t2.lastdatetime = t1.StatusDate;


-----Original Message-----
From: Kaup, Chester 
Sent: Tuesday, December 11, 2012 4:14 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] SQL Time

Other message got sent to soon please ignore

Tried this and got a select statement error

Select t1.* from dbo_DSS_StatusChanges t1 inner join (select distinct PID, INT(StatusDate), max(StatusDate) as lastdatetime 
   group by PID, INT(StatusDate))
 t2 on t2.PID = t1.PID AND t2.lastdatetime = t1.StatusDate;

I the modified the select statement and came up with this

Select t1.* from dbo_DSS_StatusChanges t1 inner join (select distinct PID, INT(StatusDate), max(StatusDate) as lastdatetime from dbo_DSS_StatusChanges
   group by PID, INT(StatusDate))
 t2 on t2.PID = t1.PID AND t2.lastdatetime = t1.StatusDate;

I now get an error of [Microsoft][ODBC SQL Server Driver][SQL Server] Disallowed implicit conversion from data type datetime to data type float, table 'dbo.DSS_StatusChanges', column 'StatusDate'. Use the CONVERT function to run this query. (#260)


Thanks for the help.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Tuesday, December 11, 2012 3:30 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] SQL Time

Something like:

 Select t1.* from mytable t1
inner join
(select distinct ParentFK, INT(rDateTime), max(rDatetime) as lasdatetime
   group by ParentFK, INT(rDateTime)) t2 on t2.ParentFK = t1.ParentFK AND t2.lastdatetime = t1.rdatetime

The inner Select should return the FK and the datetime of the last record for each day for each Parent entity.  Joining on those two fields should give you just the records you want.

--
Stuart

On 11 Dec 2012 at 20:58, Kaup, Chester wrote:

> I have a database with linked SQL server table. I need to extract some 
> records from the table. Some days there is more than one record; ie 
> two records with different times. I need to grab the last record on 
> those days. So far I have not come up with a way. Your ideas 
> appreciated. -- AccessD mailing list AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd Website:
> http://www.databaseadvisors.com
> 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com