Please help with SQL statement.

• Tassilo Burger

  Valued Member

  Points: 66

  More actions

  May 13, 2005 at 11:32 am

  #558805

  As I can read in your question you want to have ALL customer missing at least one record and how I understand the P.S. note is, that you have to send a date range to get the records, right?

  All suggestions are working if there is a record missing in the middle of the date range, not if there is one record missing at the beginning or ending of the date range.

  So, my suggestion is:

  Create Procedure pro_MissingRecords

  @FromDate

  datetime = '2003/05/01',

  @ToDate datetime = '2004/03/01'

  As

  SELECT

  CustomerID

  FROM

  <your tablename>

  WHERE

  Convert(DateTime, Convert(varchar(4), [Year])+'/'+Convert(varchar(4), [Month])+'/1') BETWEEN @FromDate AND @ToDate

  GROUP BY

  CustomerID

  HAVING

  Count(*) < DateDiff(m, @FromDate, @ToDate) + 1

  return

  To test it add records from a second customer missing only Mar/2004 and change the second parameter to '2004/02/01'

  I set the parameters default value only for test purposes!

  Tassilo