how to find cutsomer who is not billed after 31-march-2017?

• raghuldrag

  Hall of Fame

  Points: 3160

  More actions

  August 30, 2017 at 7:20 am

  #325115

  desc cust_master

  locn_no varchar(20)
  cust_no  varchar(20)
  address varchar(20)

  desc  billed_det
  cust_no varchar(20)
  order_amount numeric(5,2)
  oreder_date datetime

  expecting output:

  cust_no   locn_no

  details  who are not billed after 31_mar-2017 to till date

• pietlinden

  SSC Guru

  Points: 63436

  More actions

  August 30, 2017 at 7:32 am

  #1957428

  FFS, how about some real tables? Since you didn't provide consumable data, here's my best guess.  

  SELECT CM.cust_no
  , loc_No
FROM Cust_Master CM
WHERE NOT EXISTS
(SELECT 1
FROM desc billed_det det
WHERE det.cust_no = CM.cust_no
AND order_date > '31-Mar-2017);

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 30, 2017 at 7:50 am

  #1957432

  raghuldrag - Wednesday, August 30, 2017 7:20 AM

  desc cust_master

  locn_no varchar(20)
  cust_no  varchar(20)
  address varchar(20)

  desc  billed_det
  cust_no varchar(20)
  order_amount numeric(5,2)
  oreder_date datetime

  expecting output:

  cust_no   locn_no

  details  who are not billed after 31_mar-2017 to till date

  Use WHERE NOT EXISTS ()

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden