Need help on set-based expression

Question

Need help on set-based expression

Eswin

SSCrazy Eights

Points: 8320
More actions
June 24, 2009 at 12:43 am

#222950

Hi Expetrz
cursor sample
set nocount on
declare c2 cursor for
select distinct emp.employee_id,emp.User_Id,amt.user_name,
leave.leave_start_dt as START_DATE,
leave.leave_end_dt as END_DATE,
(case when leave.half_day ='Y' then leave_type+'-H/D' else leave_type end)
as item_code,leave.half_day
from employee_tbl emp,ats_map_tbl amt,tl_leave_tbl leave
where employee_status='A'
and emp.employee_id=amt.employee_id
and emp.employee_id=leave.employee_id
and amt.organization_id=@organization_id
and (leave.status='A' )
and ( ( ( leave.leave_start_dt between @start_date and @end_date)
and (leave.leave_end_dt between @start_date and @end_date) )
or leave.leave_start_dt >= @start_date and leave.leave_start_dt <=@end_date
or leave.leave_end_dt >= @start_date and leave.leave_end_dt <= @end_date
or leave.leave_start_dt <= @start_date and leave.leave_end_dt >= @end_date)
open c2
fetch c2 into
@emp_id, @User_Id,@user_name, @s_date, @e_date,@item_code,@half_day
while @@fetch_status = 0
begin
if @s_date < @start_date
begin
select @s_date = @start_date
end
if @e_date > @end_date
begin
select @e_date = @end_date
end
while (@s_date <= @e_date)
begin
select @day = datepart(weekday,@s_date)
If @day != 1
begin
If @day != 7
begin
select @count = count(*) from #temp1 where ATSDATE = @s_date and emp_id=@emp_id
If @count > 0
begin
update #temp1 set item_code=@item_code,half_day=@half_day where ATSDATE = @s_date and emp_id=@emp_id
end
If @count < 1
begin
select @exp_punchin_time=asm.shiftin_time,@exp_punchout_time=asm.shiftout_time from ATS_shift_master asm, ATS_shift_empl ase
where ase.employee_id=@emp_id and ase.shift_id=asm.shift_id
and (ase.effective_date) = (select max(effective_date) from ATS_shift_empl where employee_id=@emp_id and effective_date <= @s_date) insert into #temp1 values
(
@emp_id, @User_Id, @user_name, @s_date,
@exp_punchin_time, '00:00:00',
@exp_punchout_time, '00:00:00',
( case @item_code when 'REG' then 'null' else '00:00:00' end ),
'00:00:00', @item_code,@half_day
)
end
end
end
select @s_date = dateadd(day,1,@s_date)
end
fetch c2 into
@emp_id, @User_Id,@user_name, @s_date, @e_date, @item_code ,@half_day
end
close c2
deallocate c2
[/code]
Can anyone please help me make this into a set-based statement.
Tanx 😀

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

You've already written it. Look at the SELECT statement you built for the cursor. Just put the display in the text mode and let just that SELECT do it for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eswin SSCrazy Eights Points: 8320 More actions · Answer 2

sorry had given then wrong script

Have edited the 1st post.

Can you please help me with this.

Tanx 😀