troubleshooting Question

SQL Syntax help required with last date and next date

Avatar of Jimmy_inc
Jimmy_incFlag for United Kingdom of Great Britain and Northern Ireland asked on
SQL
10 Comments1 Solution234 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros