Query Problem 1

[Cpreston]

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

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'

 

[gmmastros]

Try this:

; 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

 

[Cpreston]

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

Thanks

 

[DaveInIowa]

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

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'

 

[gmmastros]

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

[!]; 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

 

[Cpreston]

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

 

[DaveInIowa]

It should be j.JourneyDate, j.JourneyNumber.

 

[Cpreston]

Hi Again

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

Thanks

 

[Andrzejek]

Did you try simply:

...
... Order By JourneyHeader.JourneyDate [red][s]DESC[/s][/red]) ...
...

---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

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