SALARY HOLD - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SALARY HOLD

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-28 : 07:02:12

Dear All,
I am looking for query where employee not coming continous for more than 3 working days to office.
plesae suggest.

regards,
vipin jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-28 : 07:24:24

How is your table designed?

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

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-28 : 07:29:06

my table strucure is
login(empid varchar(40),logintime datetime)
where logintime contains both logintime and loguttime.
minimum logintime for particular day is logintime and maximum logintime for same day is logouttime
now i need to calculate only thos employee who is not coming to office continous 3 woeking days exclude saturday,sunday

regartds,
vipin jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-28 : 07:36:35

So if logout time is 20120525 14:00:00 and next logintime is 20120530 09:00:00 it should not be returned since he or she is only away for monday and tuesday and logging in on wednesday?
But if she or he logged in on 20120531 09:00:00 he/she should be returned since the person have been missing since 20120528, 20120529 and 20120530?

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

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-28 : 08:04:44

supppose today date is 28-may-2012
u will get so many datetime for paarticular employee
eg:-

empid logindate
A123 2012-05-28 05:05:12
A123 2012-05-28 04:15:12
A123 2012-05-28 2:33:12
A123 2012-05-28 2:05:12
A123 2012-05-28 12:27:12
A123 2012-05-28 09:30:12

In above example 2012-05-28 09:30:12 is logintime and 2012-05-28 05:05:12 is logout time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-28 : 08:56:55

[code]DECLARE @Sample TABLE
(
EmpID CHAR(4) NOT NULL,
LoginDate DATETIME2(0) NOT NULL
);

INSERT @Sample
VALUES ('A123', '20120528 05:05:12'),
('A123', '20120528 04:15:12'),
('A123', '20120528 02:33:12'),
('A123', '20120528 02:05:12'),
('A123', '20120528 12:27:12'),
('A123', '20120528 09:30:12');

-- SwePeso
;WITH cteSource
AS (
SELECT EmpID,
LoginDate,
CASE DATEDIFF(DAY, '19000101', LoginDate) % 7
WHEN 0 THEN DATEDIFF(DAY, '18991228', LoginDate)
WHEN 1 THEN DATEDIFF(DAY, '18991226', LoginDate)
WHEN 2 THEN DATEDIFF(DAY, '18991226', LoginDate)
WHEN 3 THEN DATEDIFF(DAY, '18991226', LoginDate)
WHEN 4 THEN DATEDIFF(DAY, '18991226', LoginDate)
WHEN 5 THEN DATEDIFF(DAY, '18991227', LoginDate)
WHEN 6 THEN DATEDIFF(DAY, '18991228', LoginDate)
ELSE CAST(NULL AS DATETIME)
END AS NextDate
FROM @Sample
)
SELECT c.EmpID,
c.LoginDate
FROM cteSource AS c
WHERE NOT EXISTS(SELECT * FROM @Sample AS s WHERE s.EmpID = c.EmpID AND s.LoginDate > c.LoginDate AND s.LoginDate < c.NextDate)[/code]

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

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-30 : 05:51:11

uhhmm...Please correct me if I am wrong Swepeso. Can't it be done like this?


--Creating Table

Create Table Ex
(empid varchar(4),
 logindate DateTime )


--Inserting Sample Data(Added some data to your sample data)

Insert Into Ex
Select 'A123', '2012-05-28 05:05:12'
Select 'A123', '2012-05-28 04:15:12'
Select 'A123', '2012-05-28 02:33:12'
Select 'A123', '2012-05-28 02:05:12'
Select 'A123', '2012-05-28 12:27:12'
Select 'A123', '2012-05-28 09:30:12'
Select 'A124', '2012-05-28 05:05:12'
Select 'A124', '2012-05-28 09:30:12'
Select 'A124', '2012-05-27 05:05:12'
Select 'A124', '2012-05-27 09:30:12'
Select 'A124', '2012-05-22 05:05:12'
Select 'A124', '2012-05-22 09:30:12'


--Query For Your Requirement

;With CTE
As
(Select *, ROW_NUMBER() Over (Partition By empid Order By logindate) As rn From Ex)
Select a.empid From CTE As a
JOIN CTE as b ON a.empid = b.empid And b.rn = (a.rn + 1)
Where DATEDIFF(DD, a.logindate, b.logindate) >= 3

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources