need help with a select cursor
-
I am trying to pull some data from two tables that share a foreign key. I am not having much success. I thought the best way to do this was via a cursor but I can't get it to work.
I want the cursor to go through my tables one time and pull the employees status at the date checks were cut, and the aggregate of the checks.
it passes syntax but runs forever with no data returned. I realize I am missing something here, but not sure what it is.
The foreign key is emplid
the date fields are the check_dt when the check(S) were cut, and effdt which is the effective date of the employees status which can change over time.
Here is my sql:
declare @emplid char(11)
declare emplid_cursor cursor read_only for
select distinct(emplid)
from ps_job
open emplid_cursor
fetch next from emplid_cursor
into @emplid
while @@fetch_status=0
begin
select ps_job.empl_status, ps_job.reg_temp, @emplid, ps_job.effdt, sum(ps_al_chk_hrs_ern.earnings)
from ps_job, ps_al_chk_hrs_ern
where ps_job.emplid=@emplid
and ps_job.emplid=ps_al_chk_hrs_ern.emplid
and ps_al_chk_hrs_ern.check_dt='03/25/04'
and ps_job.effdt=(select max(effdt) from ps_job where effdt <='03/25/04' and ps_job.emplid=@emplid
group by ps_job.empl_status, ps_job.reg_temp, ps_job.emplid, ps_job.effdt
fetch next from emplid_cursor
into @emplid
end
close emplid_cursor
deallocate emplid_cursor
-
Never mind I figured it out... here is the completed sql:
declare @emplid char(11)
declare emplid_cursor cursor read_only for
select distinct(ps_al_chk_hrs_ern.emplid)
from ps_al_chk_hrs_ern where check_dt='date'
open emplid_cursor
fetch next from emplid_cursor
into @emplid
while @@fetch_status=0
begin
select ps_job.empl_status as #empl_status, ps_job.reg_temp as #reg_temp, ps_job.emplid as #emplid, ps_job.effdt #effdt, ps_al_chk_hrs_ern.check_dt as #check_dt, sum(ps_al_chk_hrs_ern.earnings) as #aggregate_earnings
from ps_job, ps_al_chk_hrs_ern
where @emplid=ps_job.emplid
and @emplid=ps_al_chk_hrs_ern.emplid
and ps_job.emplid=ps_al_chk_hrs_ern.emplid
and ps_al_chk_hrs_ern.check_dt='date'
and ps_job.effdt=(select max(effdt) from ps_job where effdt <='date' and ps_job.emplid=@emplid)
group by ps_job.empl_status, ps_job.reg_temp, ps_job.emplid, ps_job.effdt, ps_al_chk_hrs_ern.check_dt
order by ps_job.emplid
fetch next from emplid_cursor
into @emplid
end
close emplid_cursor
deallocate emplid_cursor
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply