Another 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)

Another query help needed

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-06-01 : 23:34:12

declare @startdate datetime, @enddate datetime
set @startdate = '20060101'
set @postdate = '20060131'

create table Employee
(
empNo varchar(3)
)

insert into Employee
select '100' union
select '101' union
select '102'

create table Scheme
(
empNo varchar(3),
empSch varchar(2),
wefDate datetime
)

insert into Scheme
select '100','1', '20060101' union
select '100','2', '20060110' union
select '100','3', '20060125' union
select '101','4', '20051215' union
select '101','3', '20051230' union
select '101','6', '20060125' union
select '102','7', '20060110'

my program is currently using 2 while loops to :
1st loop :
for each employee In table Employee
select top 1 * from Scheme where wefDate <= @startdate order by wefDate desc
union
select * from Scheme where wefDate between @startdate and @enddate
next

this should give me access to:
'100','1', '20060101'
'100','2', '20060110'
'100','3', '20060125'
'101','3', '20051230'
'101','6', '20060125'
'102','7', '20060110'

My qst is how am i going to have these 6records shown if i wanna use SQL query
instead of using program. From those 2tables, i wanna have :
'100','1', '20060101'
'100','2', '20060110'
'100','3', '20060125'
'101','3', '20051230'
'101','6', '20060125'
'102','7', '20060110'

anyone? Thx

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-02 : 00:12:55

[code]select s.*
from Employee e inner join Scheme s
on e.empNo = s.empNo
where s.wefDate between @startdate and @enddate
or s.wefDate = (select max(wefDate) from Scheme x
where x.empNo = s.empNo
and x.wefDate <= @startdate)

Result:
empNo empSch wefDate
----- ------ -----------------------
100 1 2006-01-01 00:00:00.000
100 2 2006-01-10 00:00:00.000
100 3 2006-01-25 00:00:00.000
101 3 2005-12-30 00:00:00.000
101 6 2006-01-25 00:00:00.000
102 7 2006-01-10 00:00:00.000[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources