CODEWITHSUNDEEP
sqlsql-server
Kilma
Kilma

Reputation: 35

SQL How to find the date that exists or not

I'm using Microsoft SQL Server 2014. I need to find all the orders that have been placed or not after they were requested. The process looks like that: we receive the request, then we can place the order instantly or after some period of time - 'ReminderDate'. The problem is that the orders sometimes are missed and I want to find a way to find them

I tried

SELECT 
  a.[AddedDate],
  b.[DateAdded],
  b.[ReminderDate]
FROM request a
left join order b on a.clientId=b.clientId
where a.AddedDate between '2018-11-09' and '2018-11-17'  and 
b.DateAdded>a.AddedDate  or b.clientId is null and 
a.deleted=0 and
b.deleted=0

But it only shows me the successfully placed ones and I know that some of them are missed. The issue is to find all orders that have AND haven't been placed too after request date for the given client (is it possible in one query ?). Please tell what I'm doing wrong and how to fix it.

Upvotes: 2

Views: 55

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

It seems strange to me that you would want all orders for the client after a given date. That becomes a mess if there are multiple orders. I would prefer to have just the "next" one if there is one:

select r.AddedDate, o.DateAdded, o.ReminderDate
from request r outer apply
     (select top (1) o.*
      from order o
      where o.clientId = r.clientId and o.DateAdded > r.AddedDate and
            o.deleted = 0
      order by o.DateAdded desc
     ) o
where r.AddedDate between '2018-11-09' and '2018-11-17' and
      r.deleted = 0 ;

Upvotes: 1

Matthias Meid
Matthias Meid

Reputation: 12523

Is it correct that you want to see the Request (a) even when there's no Order (b) for it?

I suggest you move all conditions for b into the ON clause of the LEFT JOIN. This way you don't need the b.clientId IS NULL check either:

SELECT 
  a.[AddedDate],
  b.[DateAdded],
  b.[ReminderDate]
FROM request a
LEFT JOIN order b ON a.clientId = b.clientId AND b.DateAdded > a.AddedDate AND b.deleted=0
WHERE a.AddedDate between '2018-11-09' and '2018-11-17' and a.deleted=0

If you prefer having the conditions in the WHERE, you need to add brackets (as pointed out in the comments) and make sure that you always check explicitly whether you found no Order, aka b.clientId is null. 

SELECT 
  a.[AddedDate],
  b.[DateAdded],
  b.[ReminderDate]
FROM request a
left join order b on a.clientId=b.clientId
where (a.AddedDate between '2018-11-09' and '2018-11-17'  and 
b.DateAdded>a.AddedDate  or b.clientId is null) and 
a.deleted=0 and
(b.deleted=0 or b.deleted is null)

Up to you which one you prefer. I recommend the first since it's shorter and has fewer different cases you need to think about.

Upvotes: 0

Related Questions