Post reply

How do I group the column with these condiitions

  • July 14, 2008 at 10:48 pm

    #209844

    Hi All,

    I have a table named gpsdata_history which consists of the fields gps_datetime and registrationno among various others.

    I want to find the min(gps_datetime) of each vehicle at a particular time span and the max(gps_datetime) of each vehicel at a particular timespan, all these in one single query.

    So it would be like, I want the latest time the vehicle was sending signals on January 1st and the earliest time at which the vehicle started sending signals on January 2 2008

    min(gps_datetime) registrationno max(gps_datetime)

    01/01/2008 3:00:03 abc 01/02/2008 23:00:00

    i gave the query as below

    select max(gps_datetime), registrationno from gpsdata_history where gps_datetime >'07/13/2008' and gps_datetime<'07/14/2008' and analog2='stepii' group by registrationno

    union

    select min(gps_datetime), registrationno from gpsdata_history where gps_datetime >'07/14/2008' and gps_datetime<'07/15/2008' and analog2='stepii' group by registrationno

    But it did not group by registrationno, where am i supposed to rectify

    Kindly help.

    Thanks in advance

    Regards

    cmrhema

  • July 14, 2008 at 11:14 pm

    #842805

    try this query it may works.

    Select * , (select max(gps_datetime) as maxdate from gpsdata_history where gps_datetime >'07/13/2008' and gps_datetime<'07/14/2008' and analog2='stepii' and registrationno =data.registrationno ) from

    (select min(gps_datetime) as mindate, registrationno from gpsdata_history where gps_datetime >'07/14/2008' and gps_datetime<'07/15/2008' and analog2='stepii' group by registrationno)as data

    Regards

    swamy.

  • July 14, 2008 at 11:17 pm

    #842806

    Brilliant Swamy, thanks it works.

  • July 15, 2008 at 1:45 am

    #842865

    Swamy seems to be very professional for T-SQL! This code is very profi!

    :w00t::w00t::w00t::w00t::w00t:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • July 15, 2008 at 2:06 am

    #842870

    Try this for speed

    SELECTRegistrationNo,

    MIN(gps_datetime) AS minTime,

    MAX(gps_datetime) AS maxTime

    FROMgpsdata_history

    WHEREgps_datetime >= '20080713'

    AND gps_datetime < '20080715'

    AND analog2 = 'stepii'

    GROUP BYRegistrationNo

    ORDER BYRegistrationNo

    N 56°04'39.16"
    E 12°55'05.25"

  • July 15, 2008 at 1:57 pm

    #843388

    HI Peso

    any specific reason for having ORDER BY cluase in the query?

    Thanks -- Vj

    Thanks -- Vijaya Kadiyala
    www.dotnetvj.com
    SQL Server Articles For Beginers


  • July 15, 2008 at 10:52 pm

    #843507

    No.

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply