SQL Server Performance

help me please

Discussion in 'SQL Server 2005 General Developer Questions' started by salikya, Aug 4, 2007.

  1. salikya New Member

    Employee Date Enquired Title Company Bryan Cole 2007-05-01 Recruiter ABC
    Bryan Cole 2007-05-15 Recruiter ABC
    Bryan Cole 2007-05-21 Recruiter ABCBryan Cole 2007-06-15 Recruiter ABC
    Bryan Cole 2007-07-01 Recruiter ABCBryan Cole 2007-07-30 Recruiter ABC
    I have to do row by row date comparisons in a date column. If the date difference is more than 30 days we keep it , otherwise we suppress it. How can we write the query without using cursor so that only the bold rows will come ?
  2. Adriaan New Member

    You have to use NOT EXISTS to get only the hightlighted rows. See if you can grasp the logic here:
    SELECT t1.* FROM MyTable t1
    WHERE NOT EXISTS
    (SELECT t2.* FROM MyTable t2
    WHERE t2.Employee = t1.Employee AND t2.Title = t1.Title AND t2.Company = t1.Company
    AND t2.DateEnquired BETWEEN DATEADD(d,-30,t1.DateEnquired) AND t1.DateEnquired)
    OR NOT EXISTS
    (SELECT t2.* FROM MyTable t2
    WHERE t2.Employee = t1.Employee AND t2.Title = t1.Title AND t2.Company = t1.Company
    AND t2.DateEnquired < t1.DateEnquired)

Share This Page