?
Solved

SQL Syntax help required with last date and next date

Posted on 2011-09-06
10
Medium Priority
?
213 Views
Last Modified: 2012-05-12
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
Comment
Question by:Jimmy_inc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 8

Expert Comment

by:wchh
ID: 36487382
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
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36487410
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
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 36487412
Sorry wchh,

took so long typing up my post I did not see your suggestion.
0
Industry Leaders: 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!

 

Author Comment

by:Jimmy_inc
ID: 36487440
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
 
LVL 8

Expert Comment

by:wchh
ID: 36487472
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
 

Author Comment

by:Jimmy_inc
ID: 36487500
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
 
LVL 41

Expert Comment

by:Sharath
ID: 36491549
Can you provide some sample data with expected result?
0
 
LVL 8

Expert Comment

by:wchh
ID: 36492602
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
 
LVL 8

Expert Comment

by:wchh
ID: 36492605
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
 
LVL 8

Accepted Solution

by:
wchh earned 2000 total points
ID: 36492620
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month14 days, 19 hours left to enroll

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question