any tips

EmployeeDate EnquiredTitleCompany

Bryan Cole2007-05-01RecruiterABC

Bryan Cole2007-05-15RecruiterABC

Bryan Cole2007-05-21RecruiterABC

Bryan Cole2007-06-15RecruiterABC

Bryan Cole2007-07-01RecruiterABC

Bryan Cole2007-07-30RecruiterABC

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 thebold rows will come ?

I'm confused.

Do you mean to have one entry per month,

OR,

from any starting date, the next date that is 'at least' 30 days from the last 'selected' date.

If the first row was 05-15, then ONLY 05-15, 06-15 and 07-30 would be selected.

If the first row was 05-21, then ONLY 05-21 and 07-01 would be selected

Please confirm.

|||

You are right. There can be multiple date for the employee date. It will select only the date which have subsequent differences more than 30 days. I cannot use cursor.

Thanks,

Sushil

|||

If you use sql server 2005 then the following query might help you,

Code Snippet

Create Table #todo (

[Employee] Varchar(100) ,

[DateEnquired] datetime ,

[Title] Varchar(100) ,

[Company] Varchar(100)

);

Insert Into #todo Values('BryanCole','2007-05-01','Recruiter','ABC');

Insert Into #todo Values('BryanCole','2007-05-15','Recruiter','ABC');

Insert Into #todo Values('BryanCole','2007-05-21','Recruiter','ABC');

Insert Into #todo Values('BryanCole','2007-06-15','Recruiter','ABC');

Insert Into #todo Values('BryanCole','2007-07-01','Recruiter','ABC');

Insert Into #todo Values('BryanCole','2007-07-30','Recruiter','ABC');

Insert Into #todo Values('BryanCole','2008-07-30','Recruiter','ABC');

Insert Into #todo Values('BryanCole','2008-08-30','Recruiter','ABC');

;With CTE

as

(

select * , row_number() over(order By DateEnquired) RID from #todo

)

,REC

as

(

Select DateEnquired, RID, 0 Level from CTE Where Rid=1

Union ALL

Select CTE.DateEnquired,CTE.RID,Level+1 From REC Join

CTE on

REC.RID < CTE.RID

and datediff(day,rec.DateEnquired, CTE.DateEnquired) >= 30

)

Select * From CTE Where RID in (Select Min(Rid) from REC Group By Level)

|||

Mani,

Since datediff() returns the number of day boundaries crossed, I think that datediff() >= 30 would be more accurate.

For example, with [ > 30 ], adding a row dated '2007-06-01' does NOT return what I would expect. It returns:

'2007-05-01'

'2007-06-01'

'2007-07-15'

And I would expect:

'2007-05-01'

'2007-06-01'

'2007-07-01'

|||Thank You Arnie, I fixed it.

|||

I really appreciate your help.This query works great for a single student. What changes I should make if there are multiple employees and i want to execute for every set of employee. For eg,

bryan cole 2007-05-18
bryan cole 2007-06-04
bryan cole 2007-07-14

susan wu 2007-04-04
susan wu 2007-05-05
susan wu 2007-06-08
susan wu 2007-06-18

The result will be
bryan cole 2007-05-18
bryan cole 2007-07-14

susan wu 2007-04-04
susan wu 2007-05-05
susan wu 2007-06-08|||

This is now one of those situations where if we had only been provided the entire issue up front -it would be solved by now. But we were only provided a part of the problem. And then, of course, the solution to that 'part of the problem' doesn't work for the 'real' problem.

Bummer.

|||

Here it is,

Code Snippet

Create Table #todo (

[Employee] Varchar(100) ,

[DateEnquired] datetime ,

[Title] Varchar(100) ,

[Company] Varchar(100)

);

Insert Into #todo Values('BryanCole','2007-05-18','Recruiter','ABC');

Insert Into #todo Values('BryanCole','2007-06-04','Recruiter','ABC');

Insert Into #todo Values('BryanCole','2007-07-14','Recruiter','ABC');

Insert Into #todo Values('susan wu','2007-04-04','Recruiter','ABC');

Insert Into #todo Values('susan wu','2007-05-05','Recruiter','ABC');

Insert Into #todo Values('susan wu','2007-06-08','Recruiter','ABC');

Insert Into #todo Values('susan wu','2007-06-18','Recruiter','ABC');

;With CTE

as

(

select * , row_number() over(partition by Employee order By DateEnquired) RID from #todo

)

,REC

as

(

Select Employee,DateEnquired, RID, 0 Levelfrom CTE Where Rid=1

UnionALL

Select CTE.Employee,CTE.DateEnquired,CTE.RID,Level+1 From REC Join

CTE on CTE.Employee = REC.Employee

and REC.RID < CTE.RID

and datediff(day,rec.DateEnquired, CTE.DateEnquired) >= 30

)

Select CTE.* From CTE

join (Select Min(Rid) Rid,Employee from REC Group By Level,Employee) data

on cte.RID = data.rid AND cte.Employee = data.Employee