asked on

SQL Syntax help required with last date and next date

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

wchh

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

OnALearningCurve

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

OnALearningCurve

Sorry wchh,

took so long typing up my post I did not see your suggestion.

Jimmy_inc

ASKER

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

wchh

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

Jimmy_inc

ASKER

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

Sharath S

Can you provide some sample data with expected result?

wchh

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

wchh

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

ASKER CERTIFIED SOLUTION

wchh

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer