Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

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
0
Jimmy_inc
Asked:
Jimmy_inc
  • 5
  • 2
  • 2
  • +1
1 Solution
 
wchhCommented:
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

0
 
OnALearningCurveCommented:
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

0
 
OnALearningCurveCommented:
Sorry wchh,

took so long typing up my post I did not see your suggestion.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jimmy_incAuthor 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



 
0
 
wchhCommented:
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
0
 
Jimmy_incAuthor 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
0
 
SharathData EngineerCommented:
Can you provide some sample data with expected result?
0
 
wchhCommented:
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
0
 
wchhCommented:
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
0
 
wchhCommented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now