SQL Syntax help required with last date and next date

Jimmy_inc
Jimmy_inc used Ask the Experts™
on
Hi,

Using: SQL Server 2005 Express Edition and using Northwind.

I would like to select the last OrderDate where the ShippedDate is Not NULL and then the Next OrderDate where the ShippedDate IS NULL.

So for instance using this join:

SELECT     TOP (100) PERCENT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderDate, dbo.Orders.ShippedDate
FROM         dbo.Customers INNER JOIN
                      dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
ORDER BY dbo.Customers.CustomerID

So for example, if When I were to filter by 'RICAR' later.. down the line(no in this e.g.) , I would see

CUSTOMERID|COMPANYNAME     |LASTORDERDATE                 |NEXTORDERDATE
RICAR           |Ricardo Adocicados|1998-02-09 00:00:00.000 |1998-04-29 00:00:00.000

I think the SQL would look something like :

SELECT   dbo.Customers.CustomerID, dbo.Customers.CompanyName, (Select Max(dbo.Orders.OrderDate) FROM dbo.Orders WHERE ShippedDate is NOT NULL) as LASTORDERDATE , (Select MIN(dbo.Orders.ShippedDate) FROM dbo.Orders WHERE ShippedDate is NULL AND > LASTORDERDATE) as NEXTORDERDATE

FROM         dbo.Customers INNER JOIN
                      dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
ORDER BY dbo.Customers.CustomerID



But not sure of the syntax..

Many Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
try code below:
SELECT   distinct dbo.Customers.CustomerID, dbo.Customers.CompanyName,
(Select Max(dbo.Orders.OrderDate) FROM dbo.Orders WHERE customerid=Customers.CustomerID and ShippedDate is NOT NULL) as LASTORDERDATE ,
(Select MIN(dbo.Orders.ShippedDate) FROM dbo.Orders WHERE ShippedDate is not NULL AND ShippedDate > (Select Max(dbo.Orders.OrderDate) FROM dbo.Orders WHERE customerid=Customers.CustomerID and ShippedDate is NOT NULL)) as NEXTORDERDATE
FROM         dbo.Customers INNER JOIN
                      dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
ORDER BY dbo.Customers.CustomerID

Hi Jimmy_inc,

Try The attached code,

Hope this helps,

Mark.
SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName, Q1.lastOrderDate, Q2.nextOrderDate 
FROM dbo.Customers 
LEFT JOIN (SELECT dbo.customers.customerID, MAX(dbo.Orders.OrderDate) AS lastOrderDate FROM dbo.Customers INNER JOIN dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID WHERE dbo.Orders.ShippedDate IS NOT NULL GROUP BY bdo.Customer.CustomerID)Q1 ON dbo.customers.CustomerID = Q1.CustomerID 
LEFT JOIN (SELECT dbo.customers.customerID, MIN(dbo.Orders.OrderDate) AS NextOrderDate FROM dbo.Customers INNER JOIN dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID WHERE dbo.Orders.ShippedDate IS NULL GROUP BY bdo.Customer.CustomerID)Q2 ON dbo.customers.CustomerID = Q1.CustomerID
ORDER BY dbo.Customers.CustomerID

Open in new window

Sorry wchh,

took so long typing up my post I did not see your suggestion.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

Commented:
Apologies -- should of ordered by the orderdate in original view

so..

SELECT     TOP (100) PERCENT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Orders.OrderDate, dbo.Orders.ShippedDate
FROM         dbo.Customers INNER JOIN
                      dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
ORDER BY dbo.Customers.CustomerID, dbo.Orders.OrderDate

this shows that the

|LASTORDERDATE is 1998-02-09 00:00:00.000

But then the NEXTORDERDATE 1998-04-29 00:00:00.000



 

Commented:
try:
SELECT   distinct dbo.Customers.CustomerID, dbo.Customers.CompanyName,
(Select Max(dbo.Orders.OrderDate) FROM dbo.Orders WHERE customerid=Customers.CustomerID and ShippedDate is NOT NULL) as LASTORDERDATE ,
(Select MIN(dbo.Orders.ShippedDate) FROM dbo.Orders WHERE ShippedDate is not NULL AND ShippedDate > (Select Max(dbo.Orders.OrderDate) FROM dbo.Orders WHERE customerid=Customers.CustomerID and ShippedDate is NOT NULL)) as NEXTORDERDATE
FROM         dbo.Customers INNER JOIN
                      dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
ORDER BY LASTORDERDATE

Author

Commented:
Hi wchh,

your LASTORDERDATE is returning the correct date but your NEXTORDERDATE isn't, returning 1998-02-10, it should be 1998-04-29

thanks
SharathData Engineer

Commented:
Can you provide some sample data with expected result?

Commented:
Try:
SELECT   distinct dbo.Customers.CustomerID, dbo.Customers.CompanyName,
(Select Max(dbo.Orders.OrderDate) FROM dbo.Orders WHERE customerid=Customers.CustomerID and ShippedDate is NOT NULL) as LASTORDERDATE ,
(Select MIN(dbo.Orders.orderDate) FROM dbo.Orders WHERE ShippedDate is NULL AND orderdate > (Select Max(dbo.Orders.OrderDate) FROM dbo.Orders WHERE customerid=Customers.CustomerID and ShippedDate is NOT NULL)) as NEXTORDERDATE
FROM         dbo.Customers INNER JOIN
                      dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
where dbo.Customers.customerid='RICAR'
ORDER BY LASTORDERDATE

Commented:
try for all:
SELECT   distinct dbo.Customers.CustomerID, dbo.Customers.CompanyName,
(Select Max(dbo.Orders.OrderDate) FROM dbo.Orders WHERE customerid=Customers.CustomerID and ShippedDate is NOT NULL) as LASTORDERDATE ,
(Select MIN(dbo.Orders.orderDate) FROM dbo.Orders WHERE ShippedDate is NULL AND orderdate > (Select Max(dbo.Orders.OrderDate) FROM dbo.Orders WHERE customerid=Customers.CustomerID and ShippedDate is NOT NULL)) as NEXTORDERDATE
FROM         dbo.Customers INNER JOIN
                      dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
ORDER BY LASTORDERDATE
Commented:
Ignore previous comment:
SELECT   distinct dbo.Customers.CustomerID, dbo.Customers.CompanyName,
(Select Max(dbo.Orders.OrderDate) FROM dbo.Orders WHERE customerid=Customers.CustomerID and ShippedDate is NOT NULL) as LASTORDERDATE ,
(Select MIN(dbo.Orders.orderDate) FROM dbo.Orders WHERE customerid=Customers.CustomerID and ShippedDate is NULL AND orderdate > (Select Max(dbo.Orders.OrderDate) FROM dbo.Orders WHERE customerid=Customers.CustomerID and ShippedDate is NOT NULL)) as NEXTORDERDATE
FROM         dbo.Customers INNER JOIN
                      dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
ORDER BY LASTORDERDATE

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial