Programming

Categories

  • No categories

SEARCH

Subscribe!

 Subscribe via Feedburner

What I'm Doing...

Posting tweet...

Powered by Twitter Tools

Tags

Categories

Blogroll


Return the Earliest Date Record in SQL Server 2005

Posted in: Programming by Steve on July 15, 2008

I’ve been struggling with a query lately and I haven’t been able to find a good answer. 

What I’m trying to find is the earliest record in a detail table, then return all the master and detail records associated with it, but only that single “detail” record.

Here is the table schema:

Table Structure

So the query I am trying to write is trying to get all distinct Order Detail items, but only the earliest record of the Order Header.

When the query is executed as run above, here are the results:

sqlQuery2

However, I only want to return the earliest OrderDate Header record, so the CSR will display as well. 

Here is the required resultset:

Name TotalAmount CSR OrderDate ItemName Quantity
Fatty McGhee 322 Someone Else 2008-05-01 Widget 1 40
Fatty McGhee 322 Someone Else 2008-05-01 Widget 2 34
Fatty McGhee 322 Someone Else 2008-05-01 Widget 3 20
Fatty McGhee 322 Someone Else 2008-05-01 Widget 4 30
Fatty McGhee 343 Capt. No Normalization 2008-06-01 Widget 5 34

I would like this to be a single view/query and not a stored procedure due to customer requirements. Any help with this would be greatly appreciated.


UPDATE:

I thought I would try to explain this a little better.  So I want to get information about the order detail item, “Widget 1″.  I only want to return this record once (distinct), and I want it to include the earliest SINGLE record from the Order Header table. 

If I try to do a group by to get the MIN(OrderDate) and try to return CSR in the result set, it will be incorrect and return 2 Order Header records because CSR is different:

Name TotalAmount CSR OrderDate ItemName Quantity
Fatty McGhee 322 Someone Else 2008-05-01 Widget 1 40
Fatty McGhee 343 Capt. No Normalization 2008-06-01 Widget 1 50

So, why not do a “PARTITION BY”, RANK and add a WHERE clause returning only where RANK = 1?  Because of the size of the order table, returning the partitioned table before getting the RANK of the table without filtering down the results first by User takes too long.  Because I want to filter out for a single user, I would need to pass a variable into the subquery, MyResults before running the sub query.

SELECT [Name], [TotalAmount], [CSR], [OrderDate], [ItemName],
  [Quantity]
FROM
(
    SELECT U.[Name], OH.[TotalAmount], OH.[CSR], OH.[OrderDate],
      OD.ItemName, OD.Quantity,
      RANK() OVER (PARTITION BY OD.[ItemName]       ORDER BY OrderDate) AS RANK
    FROM OrderDetail AS OD
    INNER JOIN [OrderHeader] AS OH
    ON OD.[OrderID] = OH.[ID]
    INNER JOIN [Users] AS U
    ON OH.UserID = U.ID
) AS MyResults
WHERE RANK = 1
ORDER BY [OrderDate]

So it looks like this can’t be made into a view and must be used in a stored procedure in order to filter the RANKed results.

Thanks everyone.

Comments

6 Comments
  1. Scott

    Well, you’ve got all the joins already, can you just add a where clause that compares the OrderDate to the Max/Min OrderDate? I’m still a little fuzzy on what you are trying to filter.

    Comment by Scott on July 15, 2008 at 11:58 am

  2. Steve

    Scott: If I do a group by, I can’t include the CSR field because they are different. How would I compare the order date to the Min date of the OrderHeader table and still return the rest of the records?

    I did try to do this with a PARTITION BY clause, but the overhead is too much without putting it in a stored procedure.

    Comment by Steve on July 15, 2008 at 12:24 pm

  3. Marc Brooks

    First, if you only post images, nobody can cut/paste your SQL to help.

    Second, I’m not sure what you mean by “earliest record” in the detail table, since it has no DATETIME fields. How is it that you expect more than one header row for any particular detail record?

    Comment by Marc Brooks on July 15, 2008 at 12:54 pm

  4. Steve

    Marc:

    1. I didn’t post the SQL because you don’t have the schema to replicate, but I guess I could have just posted it. I’ll keep that in mind the next time.

    2. What I mean is the earliest record of the OrderHeader with a single OrderDetail record. So the earliest order information with the detail being distinct. So in the example above, I only want a single record being returned for “Widget 1″, based on the earliest OrderDate, returning the information that is in that earliest OrderHeader Record – then joining the OrderDetail to get that detailed information.

    Comment by Steve on July 15, 2008 at 1:06 pm

  5. Marc Brooks

    What you’re looking for is a subselect to determine that this order date is the minimum order date for this order detail line. I’m still confused as to why there would ever be more than one order header for an order detail line though…

    SELECT * FROM OrderHeader AS Later WHERE NOT EXISTS (SELECT * FROM OrderHeader AS Earlier WHERE Earlier.ID = Later.ID AND Earlier.OrderDate < Later.OrderDate)

    This will give you the earliest OrderHeader row for any matching ID value. You can then join to this as an inner query.

    SELECT U.Name, OD.*, OH.*
    FROM OrderDetail AS OD
    INNER JOIN (SELECT * FROM OrderHeader AS Later WHERE NOT EXISTS (SELECT * FROM OrderHeader AS Earlier WHERE Earlier.ID = Later .ID AND Earlier.OrderDate < Later .OrderDate)
    ) AS OH ON OD.OrderID = OH.ID
    INNER JOIN Users AS U ON U.ID = OH.UserID
    ON U.

    Comment by Marc Brooks on July 15, 2008 at 1:08 pm

  6. Steve

    I updated the post with more information.

    Comment by Steve on July 15, 2008 at 3:43 pm