|
spendyala
Starting Member
15 Posts |
 Posted - 2011-02-16 : 12:44:06
|
| Hi All,I need your help in figuring out this issue. I am working on the logic to see the account status of an employee at regular intervals, lets say there is an employee A and his account status are active, inactive, referral, interview and so on. i have to write a query to get his status at 30, 60, 90, 120, 150...270 days from the date of his interview. all this status is stored in one table. To get the required result, i am using dateadded and hitting the table that many times in the select statement likeselect employee,(select employeestatus where date < interviewdate + 30),(select employeestatus where date < interviewdate + 60) and so on.. The employee table is huge and is taking an hour or so to get the result...please let me know if there is a better way...Adding the Employee TAbleCREATE TABLE #Employee (EmployeeID INT, Status VARCHAR(20), STatusDAte DATETIME)INSERT #EmployeeSELECT 1,'Interview','2010-01-01' UNION ALLSELECT 1,'Applicant','2010-02-15' UNION ALLSELECT 1,'Inactive','2010-04-01' UNION ALLSELECT 1,'PendingAppr','2010-06-01' UNION ALLSELECT 1,'FInterview','2010-06-15' UNION ALLSELECT 1,'Active','2010-06-30' UNION ALLSELECT 2,'Interview','2010-01-01' UNION ALLSELECT 2,'Applicant','2010-02-15' UNION ALLSELECT 2,'Inactive','2010-04-01' UNION ALLSELECT 2,'PendingAppr','2010-06-01' UNION ALLSELECT 2,'FInterview','2010-06-15' UNION ALLSELECT 2,'Active','2010-06-30' Thank you, Pendyala |
|
SQL Optimization