T-SQL not Between - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

T-SQL not Between

Author

Topic

wormz666
Posting Yak Master

110 Posts

Posted - 2009-03-11 : 04:54:31

let say that i have a table of ff:

dtr
------
empid
timein
timeout
logdate

leave
--------
leaveid
empid
dateto
datefrom

i've tried a statement but only one employee i can filter to get his/her absents...

i want to filter all the employee has been absent without leave and not between dateto and datefrom...

thank you in advance....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 05:04:21

Do you have some sample data and expected output?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 13:37:37

whats the master table that contain employee details? as i undersatnd dtr will only contain present employees details and leave the employees leave details.

wormz666
Posting Yak Master

110 Posts

Posted - 2009-03-11 : 22:37:24

i want to view the all employee who has not login in my biometric in a working days
in a month to be able to decide by management. i have only leave table containing the leave duration of a employee.. which will not count on ON MY t-sql....here is my stored procedure filtering all absent within a specific month by a single employee,which will not include my leave duration..

CREATE PROCEDURE SP_AWOL
@empid VARCHAR(20)
AS
DECLARE @MonthDate DATETIME
DECLARE @WORKING INT
SET @MonthDate=DATEADD(mm,DATEDIFF(mm,0,getdate()),0)

Select [last name],[first name],[middle name],[designation],department, datename(dw,dateval) as wekdy,datename(mm,dateval) as Months,datename(dd,dateval) as days, datename(yy,dateval) as Years from (select * from
(select * from (
SELECT DATEADD(dd,v.number,@MonthDate) AS DateVal FROM master..spt_values v LEFT JOIN [Holliday Information] h
ON DATEADD(dd,DATEDIFF(dd,0,h.[Dates]),0)= DATEADD(dd,v.number,@MonthDate) WHERE v.type='p'
AND DATEADD(dd,v.number,@MonthDate)<DATEADD(mm,1,@MonthDate) AND h.[Dates] IS NULL
AND DATEPART(dw,DATEADD(dd,v.number,@MonthDate)) NOT IN(1)
)working cross join [Employee Information])monthdtr full outer join leave on leave.empid=monthdtr.[Employee ID]
where dateval not between leavefrom and leaveto)xx full outer join dtr on xx.[Employee ID]=dtr.empid
WHERE MONTH(xx.dateval)=month(dtr.logdate) and year(xx.dateval)=year(dtr.logdate) and [eMPLOYEE ID]=@empid
and xx.dateval!=dtr.logdate ORDER BY xx.dateval

I WANT TO FILTER ALL EMPLOYEES.....IT TRY SOMETHING BUT IT DOESN'T WORK..

THANK YOU IN ADVANCE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 12:51:56

you still didnt answer my question. Which table contain details on employees of organisation?

wormz666
Posting Yak Master

110 Posts

Posted - 2009-03-12 : 23:28:02

on the Employee Detail... all the information about the employee on the organization..

wormz666
Posting Yak Master

110 Posts

Posted - 2009-03-12 : 23:33:10

Tables:
Employee_Master --contain the personal information
Employee_Detail --contain the employee information on the company
Leave --contain information on Leave of an Employee
Holliday --contain information about holliday.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 13:25:37

[code]
;With CTE (Date)
AS
(
SELECT '19000101' AS Date
UNION ALL
SELECT DATEADD(dd,1,Date)
FROM Date_CTE
WHERE DATEADD(dd,1,Date)<=GETDATE()
)

SELECT m.empid,m.Date
FROM
(
SELECT ed.empid,c.Date
FROM EMployee_Detail ed
CROSS JOIN Date_CTE c
)m
LEFT JOIN dtr d
ON d.EmpID=m.EmpID
LEFT JOIN leave l
ON l.empid=m.empid
AND m.Date BETWEEN l.dateto AND l.datefrom
LEFT JOIN Holiday h
ON h.Date=c.Date
WHERE d.empid IS NULL
AND l.empid IS NULL
AND h.Date IS NULL

OPTION (MAXRECURSION 0)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources