[AccessD] SQL Time

Thanks everyone for your assistance.

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

I'm not able to test the query now, but I would suggest that you replace
INT(StatusDate) with DATEPART(dd, StatusDate). Also the DISTINCT appears redundant to me since a GROUP BY is actually implying a distinct operation - but this is a minor point.
Asger

----- Original meddelelse -----

> Fra: "Kaup, Chester" <Chester_Kaup at kindermorgan.com>
> Til: Access Developers discussion and problem solving 
> <accessd at databaseadvisors.com>
> Dato: Tir, 11. dec 2012 23:13
> Emne: 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
> 
> 
> --
> 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