Results 1 to 6 of 6

Thread: Help with query

  1. #1
    Join Date
    Feb 2004
    Posts
    32

    Question Unanswered: Help with query

    Two tables:

    TBL1
    HID NAME
    --- --------
    1 C1
    2 C2
    3 C3
    4 C4

    TBL2
    CID HID CStartdate CEndDate
    --- --- ------------ ------------
    1 1 01 Jan 02 31 Dec 02
    2 1 01 Jan 03 31 Dec 03
    3 1 01 Jan 04 31 Dec 04
    4 2 01 Jan 03 31 Dec 03
    5 3 01 Jan 00 31 Dec 00
    6 3 01 Jan 01 31 Dec 01
    7 3 01 Jan 02 31 Dec 02
    8 4 01 Jan 03 31 Dec 03

    I'm looking for the query that will bring back the rows with the latest enddates for each of the HIDs, i.e

    HID Name CStartdate CEndDate
    --- -------- ----------- ------------
    1 C1 01 Jan 04 31 Dec 04
    2 C2 01 Jan 03 31 Dec 03
    3 C3 01 Jan 02 31 Dec 02
    4 C4 01 Jan 03 31 Dec 03

    Obviously a JOIN but also somewhere a group by on HID with max(Cenddate)??? I'm having no luck, would appreciate some help...

    Thanks
    Greg

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Something along these lines?

    Code:
    USE Northwind
    GO
        SELECT * 
          FROM Orders o
    INNER JOIN [Order Details] d
    	ON o.OrderId = d.OrderId
         WHERE EXISTS (SELECT * 
    		     FROM Orders x 
    		 GROUP BY OrderId 
    	           HAVING MAX(x.OrderDate) = o.OrderDate 
    		      AND x.OrderId = o.OrderId)
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Try something like this:

    select t1.HID,t1.NAME,max(t2.CStartdate), max(t2.CEndDate)
    from TBL1 t1
    join TBL2 t2 on t2.HID=t1.HID
    group by t1.HID,t1.NAME

    If CStartdate<>EndDate for record - it needs to change logic...

  4. #4
    Join Date
    Feb 2004
    Posts
    32
    I think the second query is nearly there. However, Startdate is not equal to enddate, and hypothetically for a particular HID there may be an endate entry which isn't the latest but has a startdate later than the startdate for the entry with the latest enddate. This is extremely unlikely, but I want to make sure the query is robust.

    Basically the first table is a list of individual hardware items, each referenced by a single HID.

    The second table is a list of support contracts for the hardware. Over a period of time there will be multiple support contracts for each item, each covering a specific period of time (startdate/enddate).

    I want the query to return me information about the hardware (from the first table) with information on the latest support contract. I thought a table join querying on the Max(enddate) and grouping by HID would work, but can't quite get there.........my logic generates errors :-)

    g.

  5. #5
    Join Date
    May 2003
    Posts
    26
    Something like this maybe?

    select tbl2.HID, tbl1.Name, tbl2.CStartDate, tbl2.CEndDate
    from tbl2 inner join (select HID, Max(CEndDate) as EndDate from tbl2 group by HID) as Temp1
    on tbl2.HID = Temp1.HID
    inner join tbl1 on tbl2.HID = tbl1.HID
    where tbl2.CEndDate = Temp1.EndDate

  6. #6
    Join Date
    Feb 2004
    Posts
    32

    Cool

    That looks to have done the trick.
    Cheers!

    G.

Posting Permissions

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