Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008
    Posts
    34

    Unanswered: Help with a query please

    I need help writing a query please.

    i have 2 tables, QUAL_POSITION and DRIVERS

    QUAL_POSITION has fields...

    QUAL_POS_DATE_TIME
    QUAL_POS_UNIT
    QUAL_POS_LOCATION

    and DRIVERS has fields...

    DRIVER_CURRENT_UNIT
    DRIVER_SSN

    I need to retrieve QUAL_POS_LOCATION, the date, the time, and DRIVER_SSN
    for the earliest record (by minute), for records dated YESTERDAY in the QUAL_POSITION table, for each QUAL_POS_UNIT, for every 4 hour period. So there are records for each unit every hour and sometimes more often than every hour. I want to show each units location every for hours, sorted by unit, and displaying the DRIVER_SSN. So if there are records for 1,2,3,4,5,6,7,8,9,10,11,and 12 oclock, I only want the records for 4, 8, and 12 oclock, and if there are more than one record for 4 oclock, say 4:15 and 4:30, I only want to see the earliest one, 4:15.

    So if I have data like so...

    Code:
    QUAL_POSITION
    QUAL_DATE_TIME   QUAL_POS_UNIT    QUAL_POS_LOCATION
    1/2/9 2:05:05          123                       Omaha,NE
    1/2/9 4:03:59          123                       Des Moines,IA
    1/2/9 4:15:01          123                       Des Moines,IA
    1/2/9 8:35:00          123                       Moline,IL
    1/2/9 8:35:05          123                       Moline,IL
    1/2/9 12:02:02         123                      Moline,IL
    ....
    and
    Code:
    DRIVERS
    DRIVER_SSN        DRIVER_CURRENT_UNIT
    456-89-0123        123
    845-78-8989        124
    454-65-6565        125
    ....
    I want the data like so...
    Code:
    RESULT
    Date       Time        Location           Driver_SSN
    1/2/9      4:03:59    Des Moines,IA    456-89-0123
    1/2/9      8:35:00    Moline,IL           456-89-0123
    1/2/9     12:02:02   Moline,IL           456-89-0123
    Hope this is clear and that someone can help me out!
    Last edited by curtmorehouse; 10-13-09 at 17:46.

  2. #2
    Join Date
    Sep 2008
    Posts
    34

    this doesn't work

    This does not work...

    Code:
    SELECT convert(varchar, QUAL_POS_DATE_TIME, 101) as QUAL_DATE,
          	convert(varchar, QUAL_POS_DATE_TIME, 8) as QUAL_TIME,
          	QUAL_POS_LOCATION, 
            DRIVER_SSN, 
            DRIVER_CURRENT_UNIT
            FROM QUAL_POSITION, DRIVER
            WHERE DRIVER_CURRENT_UNIT = QUAL_POS_UNIT 
            and datediff(dd,QUAL_POS_DATE_TIME,getdate()) = 1
            AND datepart(hh,QUAL_POS_DATE_TIME) IN (00, 04, 08, 12, 16, 20)
            and datepart(mi,QUAL_POS_DATE_TIME) IN (select MIN(datepart(mi,QUAL_POS_DATE_TIME))
            from QUAL_POSITION where datediff(dd,QUAL_POS_DATE_TIME,getdate()) = 1
            AND datepart(hh,QUAL_POS_DATE_TIME) IN (00, 04, 08, 12, 16, 20)
    	group by QUAL_POS_UNIT, datepart(hh,QUAL_POS_DATE_TIME)) 
    	ORDER BY DRIVER_SSN,QUAL_TIME;
    because if two units had a record in the same minute during any of the same 4 hour records, it will pull both records and I only want the first one for that hour.
    Last edited by curtmorehouse; 10-13-09 at 20:09.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •