×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Query Problem

Query Problem

Query Problem

(OP)
Hi

I am trying to get the last date a Product as been delivered. I have the code like this but this returns all 134 times it as been delivered. I have used a specific Product code to limit the return of rows. I am expecting just the latest date back. I have a feeling I may need a sub query but no idea how to achieve this. Could someone please advise or assist. Thanks


CODE --> sql

SELECT  dbo.Product.ProductID, dbo.Product.ProductCode, MAX(dbo.JourneyHeader.JourneyDate) AS DelDate, dbo.JourneyHeader.JourneyNumber
FROM         dbo.OrderLine INNER JOIN
                      dbo.Product ON dbo.OrderLine.ProductID = dbo.Product.ProductID INNER JOIN
                      dbo.JourneyLine INNER JOIN
                      dbo.JourneyHeader ON dbo.JourneyLine.JourneyID = dbo.JourneyHeader.JourneyID ON dbo.OrderLine.OrderID = dbo.JourneyLine.OrderID
GROUP BY dbo.Product.ProductID, dbo.Product.ProductCode, dbo.JourneyHeader.JourneyNumber
HAVING dbo.Product.ProductCode = 'AM015044CR' 

RE: Query Problem

Try this:

CODE

; With TempData As
(
  SELECT  dbo.Product.ProductID, 
          dbo.Product.ProductCode, 
          Row_Number() Over (Partition By Product.ProductCode Order By JourneyHeader.JourneyDate DESC) As RowId,
          dbo.JourneyHeader.JourneyDate AS DelDate, 
          dbo.JourneyHeader.JourneyNumber
  FROM    dbo.OrderLine 
          INNER JOIN dbo.Product 
            ON dbo.OrderLine.ProductID = dbo.Product.ProductID 
          INNER JOIN dbo.JourneyLine 
            ON dbo.OrderLine.OrderID = dbo.JourneyLine.OrderID
          INNER JOIN dbo.JourneyHeader 
            ON dbo.JourneyLine.JourneyID = dbo.JourneyHeader.JourneyID 
  WHERE   dbo.Product.ProductCode = 'AM015044CR' 
)
Select  ProductId,
        ProductCode,
        DelDate,
        JourneyNumber
From    TempData
Where   RowId = 1; 

If this works for you, and you want me to explain, please let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Query Problem

(OP)
Thanks appears to be spot on and an explanation would be useful for future reference.

Thanks

RE: Query Problem

I've been partial to using TOP 1 with CROSS/OUTER APPLYs. Is this less performant or just a matter of style preference?

CODE

SELECT p.ProductID, p.ProductCode, jhdr.JourneyDate, jhdr.JourneyNumber
  FROM Product p
  JOIN OrderLine odtl
    ON p.ProductID = odtl.ProductID
 CROSS
 APPLY (SELECT TOP 1 jhdr.JourneyNumber, jhdr.JourneyDate
          FROM JourneyHeader jhdr
          JOIN JourneyLine jdtl
            ON jhdr.JourneyID = jdtl.JourneyID
         WHERE jdtl.OrderID = odtl.OrderID
         ORDER BY jhdr.JourneyDate DESC) j
 WHERE p.ProductCode = 'AM015044CR' 

RE: Query Problem

The approach I used contains a common table expression. The parts in red make up the common table expression.

CODE

; With TempData As
(
  -- query here
)
Select  Columns
From    TempData
 

A common table expression allows you to separate different parts of a much larger query.

The other part of the "Magic" is with this line.

Row_Number() Over (Partition By Product.ProductCode Order By JourneyHeader.JourneyDate DESC) As RowId,


This line creates an incrementing number within the data. The number starts at one and adds one for each subsequent row. This part, "Partition By Product.ProductCode" causes the numbering to restart at 1 every time the product code changes value. This part "{!]Order By JourneyHeader.JourneyDate DESC[/!]" causes the row with the oldest date to be numbered 1, second oldest would be 2, etc...

By including Where RowId = 1 in the common table expression part, the only row (for each product code) with the value 1 for rowid is returned. In other words, you only get rowid = 1 which is the last date for each product code.

Does this make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Query Problem

(OP)
Hi

Thanks Gmmastros, understand the logic and good explanation.

DaveInIowa you code fails on the first line

SELECT p.ProductID, p.ProductCode, jhdr.JourneyDate, jhdr.JourneyNumber

The error message is

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "jhdr.JourneyDate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "jhdr.JourneyNumber" could not be boun

Any ideas why < thanks

RE: Query Problem

It should be j.JourneyDate, j.JourneyNumber.

RE: Query Problem

(OP)
Hi Again

Gmmastros how would you do it the other way, so I would want the oldest date instead of the most recent.

Thanks

RE: Query Problem

Did you try simply:

CODE

...
... Order By JourneyHeader.JourneyDate DESC) ...
... 


---- Andy

There is a great need for a sarcasm font.

RE: Query Problem

(OP)
Hi

Ok great, I will give it a go, thanks for the reply

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close