Help Required - 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

Help Required

Author

Topic

nirene
Yak Posting Veteran

98 Posts

Posted - 2008-08-30 : 10:32:18

Hai All,
This is Absentees Table which is in SQL Server 2000

Empcd # AbsentDate # LeaveDay
E1 # 01/08/2008 # L
E1 # 02/08/2008 #
E1 # 03/08/2008 # L
E1 # 04/08/2008 #
E1 # 10/08/2008 #
E1 # 15/08/2008 #
E1 # 16/08/2008 #
E1 # 17/08/2008 #

E2 # 01/08/2008 # L
E2 # 03/08/2008 # L
E2 # 04/08/2008 #

E3 # 01/08/2008 # L
E3 # 02/08/2008 #
E3 # 03/08/2008 # L

E8 # 01/08/2008 # L
E8 # 03/08/2008 # L
E8 # 10/08/2008 #

Now I want to fetch and update to another table those employees who are absent continuously for 2 or more days.
Where Starting absent date if marked 'L' should not be considered but inbetween & last marked records to be taken into consideration.

Output like this

Empcd # FDt # TDt # Period
E1 # 02/08/2008 # 04/08/2008 # 3
E1 # 15/08/2008 # 17/08/2008 # 3
E3 # 02/08/2008 # 03/08/2008 # 2

Awaiting your reply.

Nirene

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 11:03:40

Are you using sql 2005?

nirene
Yak Posting Veteran

98 Posts

Posted - 2008-08-30 : 23:13:54

SQL Server 2000

nirene
Yak Posting Veteran

98 Posts

Posted - 2008-09-02 : 01:08:24

Hello All,
I got the result from SQLMonster.com,I just want to share this detail so that other users if needed can use this code.

SELECT Empcd,
MIN(AbsentDate) AS start_date,MAX(AbsentDate) AS end_date,COUNT(*) AS cnt FROM
(
SELECT Empcd, AbsentDate, LeaveDay, rk,
(SELECT COUNT(*) FROM #tmp AS A WHERE A.Empcd = T.Empcd AND A.AbsentDate <= T.AbsentDate
AND rk = DATEDIFF(DAY, 0, A.AbsentDate) -
(SELECT COUNT(*) FROM #tmp AS B WHERE B.Empcd = A.Empcd AND B.AbsentDate <= A.AbsentDate)) AS rk1
FROM (
SELECT A.Empcd, A.AbsentDate, A.LeaveDay,
DATEDIFF(DAY, 0, A.AbsentDate) -
(SELECT COUNT(*) FROM #tmp AS B WHERE B.Empcd = A.Empcd AND B.AbsentDate <= A.AbsentDate) AS rk
FROM #tmp AS A) AS T) AS T
WHERE (rk1 = 1 AND COALESCE(LeaveDay, 'Z') <> 'L') OR rk1 > 1
GROUP BY Empcd, rk
HAVING COUNT(*) >= 2
ORDER BY Empcd, start_date

Nirene

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources