0

How to get the data from sql table where data is not present in the between dates

I am using three table

  1. Customer Master
  2. Daily dispatch
  3. EmptyCylinderRecd

I want to find the customers who are not in the Dailydispatch and EmptyCylinderRecd tables. I have tried this query but the expected results are not showing up:

select * 
from CustomerMaster 
where 
   not EXISTS (select * 
               from DailyDispatch  
               where OrderDate between '1/22/2014' and '08/10/2014') or 
   not exists (select * 
               from EmptyCylinderRecd 
               where Date between '1/22/2014' and '08/10/2014')
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sarvan
  • 11
  • 2
  • 13

3 Answers3

0
Name is the common field

then try this:

select * 
from CustomerMaster where Name NOT IN 
(select Name from DailyDispatch  
where CAST(OrderDate as DATE) between '2014-01-22' and '2014-08-10' 
and Name is NOT NULL )

union 

select * 
from CustomerMaster where Name NOT IN 
(select Name from EmptyCylinderRecd 
where  CAST(Date as DATE)between '2014-01-22' and '2014-08-10'
and Name is NOT NULL)

Note: It will be better if You use column Something Like CustomerID with Proper indexing on it. Union will give common results from query1 and query2. Use Not Exists in the terms of performance as other suggested. NOT IN vs NOT EXISTS

Community
  • 1
  • 1
Dgan
  • 10,077
  • 1
  • 29
  • 51
  • Maybe you mean "NOT IN" instead of just "NOT"? – Joachim Isaksson Oct 11 '14 at 07:45
  • With the conversion of [OrderDate] and [Date] the arguments ar no longer SARGable, leading to a table scan, even if there's a supporting index present. Furthermore, NOT IN can return incorrect results if the column in the inner select can have NULL values. Therefore, I'd strongly vote against that solution. – Lmu92 Oct 11 '14 at 08:24
  • @Ganesh_Devlekar-thanks for your answer,but i want to check the in the both tables which data not present,i want to display the common names which is not in dailydispatch and EmptyCylreturn tables.This query is showing union. – Sarvan Oct 11 '14 at 08:30
0
SELECT * 
FROM   customermaster C 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   dailydispatch D 
                   WHERE  C.NAME = D.NAME 
                         AND D.orderdate BETWEEN '2014-22-1' AND '2014-08-10') 
        OR NOT EXISTS (SELECT 1 
                       FROM   emptycylinderrecd E 
                       WHERE  C.NAME = E.NAME 
                              AND E.date BETWEEN '2014-22-1' AND '2014-08-10')
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

If the [Name] column in DailyDispatch or in EmptyCylinderRecd can be null (column definition) I'd vote agaist the NOT IN solution since this may lead to incorrect results.

Instead, I'd use the "NOT EXISTS" approach:

SELECT cm.* 
FROM CustomerMaster cm
WHERE 
NOT EXISTS 
    (SELECT 1 
     FROM DailyDispatch dp 
     WHERE cm.YourCol1 = dp.YourCol1 AND dp.OrderDate BETWEEN '20140122' and '20140810'
     )
OR NOT EXISTS
    (SELECT 1 
     FROM EmptyCylinderRecd ecr 
     WHERE cm.YourCol1 = ecr.YourCol1 AND ecr.[Date] BETWEEN '20140122' and '20140810'
    )

Please note that I changed the date format to be ISO compliant in order to avoid wrong results or errors due to the setting of DATEFORMAT.

Lmu92
  • 952
  • 5
  • 5