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 - 2009-03-10 : 11:54:02

Hai,
I'm using SQL Server 2000 and this is basically a absentees table of employees where L denotes a Leave taken,O is Loss of Pay,Null value is Holiday.

Ecode Absdt LLFlag
E001 24/01/2009 L
E001 25/01/2009 Null
E001 26/01/2009 Null
E001 27/01/2009 L

E002 20/01/2009 O
E002 01/02/2009 Null
E002 02/02/2009 L
E002 03/02/2009 L

E003 17/01/2009 L
E003 18/01/2009 Null
E003 19/01/2009 L

E004 17/01/2009 L
E004 18/01/2009 Null
E004 19/01/2009 0

E005 24/01/2009 0
E005 25/01/2009 Null
E005 26/01/2009 Null
E005 27/01/2009 0

If Holiday is preceeded and succeeded by Leave then Holidays are considered as Leave else it is Loss of Pay.
In E002's case when the previous date data is not found it means that the person was present for work.

Result Expected.

Ecode Absdt Leave_LOP
E001 25/01/2009 L
E001 26/01/2009 L
E002 01/02/2009 L
E003 18/01/2009 L
E004 18/01/2009 O
E005 25/01/2009 O
E005 26/01/2009 O

Thanks in advance

Nirene

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-10 : 12:20:03

Any primary key in table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 13:05:58

[code]
SELECT t.Ecode,t.Absdt,
CASE WHEN t.LLFlag IS NOT NULL
THEN t.LLFlag
WHEN t.LLFlag IS NULL AND prev.LLFlag='L' AND nxt.LLFlag='L'
THEN 'L'
ELSE 'O'
END AS Leave_LOP
FROM Table t
OUTER APPLY (SELECT TOP 1 LLFlag
FROM Table
WHERE Ecode=t.Ecode
AND Absdt<t.Absdt
AND LLFlag IS NOT NULL
ORDER BY Absdt DESC) prev
OUTER APPLY (SELECT TOP 1 LLFlag
FROM Table
WHERE Ecode=t.Ecode
AND Absdt>t.Absdt
AND LLFlag IS NOT NULL
ORDER BY Absdt) nxt
[/code]

nirene
Yak Posting Veteran

98 Posts

Posted - 2009-03-11 : 00:09:02

quote:
Originally posted by sodeep

Any primary key in table?

No Primary Keys

nirene
Yak Posting Veteran

98 Posts

Posted - 2009-03-11 : 00:51:09

Hello Visakh,
I forgot to mention that I use SQL Server 2000,sorry about that.Now I've edited the post.

Nirene

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-11 : 02:39:28

Are u looking for this ,

declare @temp table (Ecode varchar(32),Absdt varchar(32) ,LLFlag varchar(32))
insert into @temp select 'E001','24/01/2009', 'L'
insert into @temp select 'E001','25/01/2009',Null
insert into @temp select 'E001','26/01/2009',Null
insert into @temp select 'E001','27/01/2009','L'

insert into @temp select 'E002','01/02/2009',Null
insert into @temp select 'E002','02/02/2009','L'
insert into @temp select 'E002','03/02/2009','L'

insert into @temp select 'E003','17/01/2009','L'
insert into @temp select 'E003','18/01/2009',Null
insert into @temp select 'E003','19/01/2009','L'

insert into @temp select 'E004','17/01/2009','L'
insert into @temp select 'E004','18/01/2009',Null
insert into @temp select 'E004','19/01/2009','0'

insert into @temp select 'E005','24/01/2009','0'
insert into @temp select 'E005','25/01/2009',Null
insert into @temp select 'E005','26/01/2009',Null
insert into @temp select 'E005','27/01/2009','0'

select distinct t.ecode,t1.absdt,t.llflag from (
select ecode,llflag from @temp where llflag is not null ) t
inner join (select ecode,absdt from @temp where llflag is null) t1 on t.ecode = t1.ecode

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-11 : 03:04:40

slight change to nageswar code


SELECT DISTINCT t1.ecode,t1.absdt,t.llflag FROM 
(SELECT ecode,absdt FROM @temp WHERE llflag is null) t1 
INNER JOIN 
(SELECT ecode,absdt,llflag FROM @temp WHERE llflag is not null ) t on t.Ecode=t1.Ecode AND t.Absdt>=t1.Absdt

nirene
Yak Posting Veteran

98 Posts

Posted - 2009-03-11 : 03:58:04

quote:

Originally posted by bklr

slight change to nageswar code


SELECT DISTINCT t1.ecode,t1.absdt,t.llflag FROM 
(SELECT ecode,absdt FROM @temp WHERE llflag is null) t1 
INNER JOIN 
(SELECT ecode,absdt,llflag FROM @temp WHERE llflag is not null ) t on t.Ecode=t1.Ecode AND t.Absdt>=t1.Absdt

Hello,
Thanks for your reply. Able to get the expected result, but when a data is added like

insert into @temp select 'E002','20/01/2009','O'

In this case E002 is present on 31/01/2009 so his entry will not be there.

E001 25/01/2009 L
E001 26/01/2009 L
E002 01/02/2009 L
E002 01/02/2009 O
E003 18/01/2009 L
E004 18/01/2009 0
E005 25/01/2009 0
E005 26/01/2009 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 13:13:43

[code]
SELECT t.Ecode,t.Absdt,
CASE WHEN t.LLFlag IS NOT NULL
THEN t.LLFlag
WHEN t.LLFlag IS NULL AND (SELECT TOP 1 LLFlag
FROM Table
WHERE Ecode=t.Ecode
AND Absdt<t.Absdt
AND LLFlag IS NOT NULL
ORDER BY Absdt DESC)='L' AND (SELECT TOP 1 LLFlag
FROM Table
WHERE Ecode=t.Ecode
AND Absdt>t.Absdt
AND LLFlag IS NOT NULL
ORDER BY Absdt)='L'
THEN 'L'
ELSE 'O'
END AS Leave_LOP
FROM Table t
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources