Solved

SQL Syntax help required with last date and next date

Posted on 2011-09-06
10
209 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
  • 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
 

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 40

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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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 …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now