Query help needed. - 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 2000 Forums

Transact-SQL (2000)

Query help needed.

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-06-01 : 01:10:03

CREATE TABLE TEST
( EmpNo varchar(8),
WorkDate datetime
)

DECLARE @LASTDATE = '20060131'

INSERT INTO TEST
SELECT '1','20060101' UNION
SELECT '1','20060105' UNION
SELECT '1','20060120' UNION
SELECT '2','20060115' UNION
SELECT '2','20060125' UNION
SELECT '3','20060126'

Above is my testing table. I wanna have result like :

empNo beginDate endDate
1 '20060101' '20060105'
1 '20060105' '20060120'
1 '20060120' @LASTDATE
2 '20060115' '20060125'
2 '20060125' @LASTDATE
3 '20060126' @LASTDATE

the query will basically takes the next begindate of similar empNo
if not null, if it's null then enddate= @LASTDATE

anyone?
thx for ur kind attention

regards,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-01 : 02:01:24

[code]
~~DECLARE @LASTDATE = '20060131'~~
DECLARE @LASTDATE datetime
select @LASTDATE = '20060131'

select t1.EmpNo, t1.WorkDate as BeginDate, coalesce(t2.WorkDate, @LASTDATE) as EndDate
from TEST t1 left join TEST t2
on t1.EmpNo = t2.EmpNo
and t1.WorkDate < t2.WorkDate
and t2.WorkDate = (select min(WorkDate) from TEST x
where x.EmpNo = t1.EmpNo
and x.WorkDate > t1.WorkDate)[/code]

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-06-01 : 02:58:34

thx khtan..i have tested it.
it works perfectly
:P

... sql is fun...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources