Get start date
-
Hi all, DECLARE @holidayCount AS int, @startDate AS datetime, @endData AS datetime, @empCode AS VARCHAR(20) SET @startDate='12/6/2011 00:00:00.000' SET @endDate='12/8/2011 23:59:59.997' SET @empCode='EMP001' SET @holidayCount=(SELECT COUNT(*) FROM tbl_holidays WHERE holidays BETWEEN @startDate AND @endDate) --I am assuming that 'holidays' is a datetime field/column SELECT empCode, name, (Counts + @holidayCount) As holidayCounts FROM ( SELECT a.empCode, b.name, (Count(*) as Counts FROM tbl_leaveentry a,tbl_emp b WHERE a.empCode=b.empCode AND a.empCode=@empCode AND a.entryDate @startDate and @endDate GROUP BY by a.empCode,b.name ) AS temp WHERE Counts + @holidayCount>3 From the above query i got the count but now i need to get the start date of the leave that an employee had applied. eg. 'EMP001' had applied leave from 12/1/2011 to 12/8/2011 but i have only one date option that is the end date. once you select the date option like 12/8/2011 it searches the dates from 2days before like 12/6/2011 and 12/8/2011 and returns the employees who have taken leaves continuously but i need to get the start date of the leave applied by the employee. eg. EMP001 had applied leave from 12/1/2011 to 12/8/2011 Please suggest that how can i do this task any help will be appreciated
-
Hi all, DECLARE @holidayCount AS int, @startDate AS datetime, @endData AS datetime, @empCode AS VARCHAR(20) SET @startDate='12/6/2011 00:00:00.000' SET @endDate='12/8/2011 23:59:59.997' SET @empCode='EMP001' SET @holidayCount=(SELECT COUNT(*) FROM tbl_holidays WHERE holidays BETWEEN @startDate AND @endDate) --I am assuming that 'holidays' is a datetime field/column SELECT empCode, name, (Counts + @holidayCount) As holidayCounts FROM ( SELECT a.empCode, b.name, (Count(*) as Counts FROM tbl_leaveentry a,tbl_emp b WHERE a.empCode=b.empCode AND a.empCode=@empCode AND a.entryDate @startDate and @endDate GROUP BY by a.empCode,b.name ) AS temp WHERE Counts + @holidayCount>3 From the above query i got the count but now i need to get the start date of the leave that an employee had applied. eg. 'EMP001' had applied leave from 12/1/2011 to 12/8/2011 but i have only one date option that is the end date. once you select the date option like 12/8/2011 it searches the dates from 2days before like 12/6/2011 and 12/8/2011 and returns the employees who have taken leaves continuously but i need to get the start date of the leave applied by the employee. eg. EMP001 had applied leave from 12/1/2011 to 12/8/2011 Please suggest that how can i do this task any help will be appreciated
-
How about subtracting count of leaves from endDate like
SELECT @SDate = DATEADD (day, - @holidayCount, @endDate)
Hi, select empcode,name,(counts+2) as leaves from (select a.empcode,b.name,count(*) as counts from tbl_leaveentry a,tbl_emp b where a.empcode=b.empcode and a.empcode='EMP001' and a.entrydate between DATEADD(d,-2,'12/8/2011') and '12/8/2011' group by a.empcode,b.name) as c where (counts+2) =3 The above code generates the report of the employee who have taken leave more than 3 days from the selected dates but i need to get the start date of the leave applied by the employee eg: 12/1/2011 to 12/8/2011 Thanks in advance
-
Hi all, DECLARE @holidayCount AS int, @startDate AS datetime, @endData AS datetime, @empCode AS VARCHAR(20) SET @startDate='12/6/2011 00:00:00.000' SET @endDate='12/8/2011 23:59:59.997' SET @empCode='EMP001' SET @holidayCount=(SELECT COUNT(*) FROM tbl_holidays WHERE holidays BETWEEN @startDate AND @endDate) --I am assuming that 'holidays' is a datetime field/column SELECT empCode, name, (Counts + @holidayCount) As holidayCounts FROM ( SELECT a.empCode, b.name, (Count(*) as Counts FROM tbl_leaveentry a,tbl_emp b WHERE a.empCode=b.empCode AND a.empCode=@empCode AND a.entryDate @startDate and @endDate GROUP BY by a.empCode,b.name ) AS temp WHERE Counts + @holidayCount>3 From the above query i got the count but now i need to get the start date of the leave that an employee had applied. eg. 'EMP001' had applied leave from 12/1/2011 to 12/8/2011 but i have only one date option that is the end date. once you select the date option like 12/8/2011 it searches the dates from 2days before like 12/6/2011 and 12/8/2011 and returns the employees who have taken leaves continuously but i need to get the start date of the leave applied by the employee. eg. EMP001 had applied leave from 12/1/2011 to 12/8/2011 Please suggest that how can i do this task any help will be appreciated
What other fields are in tbl_leaveentry besides empcode,entrydate? Do you have a field that indicates how may leave days were taken? There should be a field that indicates how many days were taken for leave. Just at glance, it seems to me that your database was poorly designed. I think start date should have been included in the first place.