CURSOR virgin...Help? 1

[antiskeptic]

I have never written a cursor from scratch...so I'm completely befuddled as to where to start, can anyone help me?
I started this thread a couple days ago looking for a solution...and realized, and was told, that a LOOP or Cursor would be my only option...so here goes...

I am working on a employee time database. I have to make a report to get the time off of people who have 3 or more consecutive days of PTO (Paid time off). Now consecutive days can be 1,2,3...or if there is a holiday or weekend, it could end up being 3,5,6... If there is a timecode on the day, I don't care if 4 days have passed since the last one...it is the next "consecutive" day. See the output of my table below for an example of what I'm looking for.

Name TimeCode ItemDate
George Jetson PTO 2004-08-09
George Jetson WT 2004-08-10
George Jetson WT 2004-08-11
George Jetson WT 2004-08-12
George Jetson WT 2004-08-13
George Jetson PTO 2004-08-18
George Jetson PTO 2004-08-19
George Jetson PTO 2004-08-20
Fred Flintstone WT 2004-08-11
Fred Flintstone PTO 2004-08-12
Fred Flintstone PTO 2004-08-13
Fred Flintstone PTO 2004-08-16
Fred Flintstone WT 2004-08-17
Fred Flintstone WT 2004-08-18
Fred Flintstone WT 2004-08-19
Fred Flintstone WT 2004-08-20
Johnny Quest WT 2004-08-11
Johnny Quest PTO 2004-08-12
Johnny Quest PTO 2004-08-13
Johnny Quest WT 2004-08-16
Johnny Quest PTO 2004-08-17
Johnny Quest PTO 2004-08-18
Johnny Quest WT 2004-08-19
Johnny Quest WT 2004-08-20

Now George has the 18, 19, & 20th as paid off so I would want to see him on the report for those days.

Fred would also appear on the report even though the dates are not exactly consecutive. The skip in dates from the 13th to the 16th is over a weekend, so for the purposes of this data output, this is considered consecutive.

And Johnny would not show up at all because he only takes off 2 days at a time.

So I need to figure out how to count (like a running total) the codes when they switch from WorkTime (WT) or PaidTimeOff (PTO). And be able to take into consideration skipped over days. The only way the PTO "breaks" is if a WT timecode interrupts the "flow". (If that makes any sense) ANd I don't care about any of the PTO if it's not 3 days or more in a row.

Can anyone see of a way I can do this?

From the above example...my final output needs to be like this:

Name TimeCode Min Date Max Date
George Jetson PTO 2004-08-18 2004-08-20
Fred Flintstone PTO 2004-08-12 2004-08-16

Thanks in advance for any tips, pointers, or shoves in the right direction. Thank you!

Sincerely,
Antiskeptic

 

[checkai]

My only advice, STAY A CURSOR VIRGIN!!

 

[antiskeptic]

I have no choice...I have no other way of doing this...are you telling me that no one out there knows how to wrote a cursor...anybody, please help!

I'm frightened!

 

[johnherman]

I can help but not today.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[antiskeptic]

okay I'll check back on Monday?

 

[Pattycake245]

I created this stored proc with a cursor. It seems to work ok, based on the data provided. It may be a starting point for you anyway:

CREATE PROCEDURE dbo.pto

AS

set nocount on

declare @count int, @name varchar(20), @timecode char(3), @itemdate datetime, @maxitemdate datetime,
@var_name varchar(20)

select @count=0
select @var_name=''

declare ptocursor cursor for
select [name], timecode, itemdate
from dbo.emprecs
order by [name], itemdate

open ptocursor

fetch next from ptocursor
into @name, @timecode, @itemdate

while @@FETCH_STATUS = 0
begin
if @var_name=@name
begin
if @timecode='pto'
begin
if @count>=1
begin
update dbo.ptotable
set maxdate = @itemdate,
ptocnt=ptocnt+1
where [name]=@name
select @count = @count+1
end else
begin
update dbo.ptotable
set mindate=@itemdate,maxdate=@itemdate,ptocnt=1
where [name]=@name
select @count = @count+1
end
end else
begin
update dbo.ptotable
set ptocnt=0
where [name]=@name
and ptocnt<3
select @count=0
end
end else
begin
set @count=0
set @var_name=(select distinct [name] from emprecs where [name]=@name)
if @timecode='pto'
begin
if @count>=1
begin
update dbo.ptotable
set maxdate = @itemdate,
ptocnt=ptocnt+1
where [name]=@name
select @count = @count+1
end else
begin
insert into dbo.ptotable([name], timecode, mindate, maxdate, ptocnt)
values(@name,@timecode,@itemdate,@itemdate,1)
select @count = @count+1
end
end else
begin
if @count<>0
begin
update dbo.ptotable
set ptocnt=0
where [name]=@name
and ptocnt<3
select @count=0
end else
begin
insert into dbo.ptotable([name], timecode, mindate, maxdate, ptocnt)
values(@name,'pto',@itemdate,@itemdate,1)
end
end
end
fetch next from ptocursor
into @name, @timecode, @itemdate
end

close ptocursor
deallocate ptocursor

select * [name], timecode, mindate, maxdate
from ptotable
where ptocnt >= 3

Hope this gets you started,

Tim

 

[antiskeptic]

This definitely looks like enough to get me started...and You don't know how much I appreciate your help! Thank you so much for your time, and I will let you know how it comes out. Just wanted to let you know right away how wonderful you are for giving this a shot.

THank you and you'll be hearing from me soon.
Sincerely,
Brenda

 

[antiskeptic]

Okay PattyCake245..
I'm almost there, but the CURSOR is not picking up all the possible "candidates". It seems to be skipping over those people that have more than one WT (Worktime) record per day. Almost like it's looking at the records and when it doesn't advance to the next date on the next record...it dumps it off thinking there is no PTO in that reords, maybe?

Here is my code...your original code you sent , costom fit for my data, and the top part of my procedure to let you see how it all comes together.

This may be too much information...and you may not have a clue what I'm talking about...but I thought I'd give it a shot. Plus thank you again for your efforts...it is doing what it's supposed to for the majority of the data...it's just skipping the few weird exceptions.

drop table #PTOCounts 
drop table #WorkTimeCounts
drop table #allcounts
drop table #ptotable
drop table #WTtable

select distinct
employee.serial_number,
employee.first_name, 
employee.last_name,
department.department_id CommunityId,
department.description CommunityName,
locations.region,'PTO' TimeCode,
timecode_description.timecode_id,
round((process_raw.stop_time-process_raw.start_time)/60.0,2) actualtime,
convert(varchar(5), dateadd(mi, round((((process_raw.stop_time-process_raw.start_time)/60.0) * 60.0),2), 0), 8) ActualTimeHours,
convert(datetime,(dateadd(day,process_raw.item_date,'1/1/1930'))) Item_Date,
convert(datetime,(dateadd(day,process_raw.processing_pay_period,'1/1/1930')))PayPeriodEndDate

Into #PTOCounts

from 
employee,workgroup,department,
locations, process_raw,employee_types,
timecode_description,timecode_definitions

where
locations.location_sys_id = employee.location_sys_id
and locations.location_id = workgroup.workgroup_id
and employee.department_sys_id = department.department_sys_id
and employee.employee_type_sys_id = employee_types.employee_type_sys_id
and employee.employee_sys_id = process_raw.employee_sys_id
and process_raw.timecode_sys_id = timecode_description.timecode_sys_id
and timecode_description.timecode_sys_id = timecode_definitions.timecode_sys_id
and employee_types.employee_type_description in ('HOURLY FULL TIME')
and timecode_id in ('JURY','FNRL','LDSB','DISB','FLH','FHOL','LSCK','SICK','LVAC','VAC','PTO')
and convert(datetime,(dateadd(day,process_raw.processing_pay_period,'1/1/1930'))) = '08/20/2004'
--select * from #PTOCOunts
select distinct
employee.serial_number,
employee.first_name, employee.last_name,
department.department_id CommunityId,
department.description CommunityName,
locations.region, 'WT' TimeCode,
timecode_description.timecode_id,
round((process_raw.stop_time-process_raw.start_time)/60.0,2) actualtime,
convert(varchar(5), dateadd(mi, round((((process_raw.stop_time-process_raw.start_time)/60.0) * 60.0),2), 0), 8) ActualTimeHours,
convert(datetime,(dateadd(day,process_raw.item_date,'1/1/1930'))) Item_Date,
convert(datetime,(dateadd(day,process_raw.processing_pay_period,'1/1/1930')))PayPeriodEndDate

Into #WorkTimeCounts

from employee,workgroup,department,
locations, process_raw,employee_types,
timecode_description,timecode_definitions

where locations.location_sys_id = employee.location_sys_id
and locations.location_id = workgroup.workgroup_id
and employee.department_sys_id = department.department_sys_id
and employee.employee_type_sys_id = employee_types.employee_type_sys_id
and employee.employee_sys_id = process_raw.employee_sys_id
and process_raw.timecode_sys_id = timecode_description.timecode_sys_id
and timecode_description.timecode_sys_id = timecode_definitions.timecode_sys_id
and employee_types.employee_type_description in ('HOURLY FULL TIME')
and not(timecode_id) in ('JURY','FNRL','LDSB','DISB','FLH','FHOL','LSCK','SICK','LVAC','VAC','PTO')
and convert(datetime,(dateadd(day,process_raw.processing_pay_period,'1/1/1930'))) = '08/20/2004'


select distinct * Into #allcounts
from #WorkTimeCounts
where serial_number in 
(select distinct serial_number 
 from #PTOCounts 
 where serial_number = #WorkTimeCounts.serial_number
and communityID = #WorkTimeCounts.communityID
and PayPeriodEndDate = #WorkTimeCounts.PayPeriodEndDate)

union all 

select distinct * from #PTOCounts
where serial_number in 
(select distinct serial_number 
 from #WorkTimeCounts 
 where serial_number = #PTOCounts.serial_number
and communityID = #PTOCounts.communityID
and PayPeriodEndDate = #PTOCounts.PayPeriodEndDate)
Order By #WorkTimeCounts.last_name,#WorkTimeCounts.first_name, #workTimeCounts.item_date

--select distinct * From #AllCounts

set nocount on
 
declare 
@count int,
@SerialNumber int,
@fname varchar(20),
@lname varchar(50), 
@timecodeGroup char(3), 
@maxitemdate datetime, 
@itemdate datetime, 
@timecodeActual varchar(10),
@var_fname varchar(20), 
@var_lname varchar(50),
@var_serial_number int

select @count=0
select @var_fname=''
select @var_lname=''
select @var_serial_number=''

declare ptocursor cursor for 
select distinct serial_number,first_name, last_name,
TimeCode,timecode_id,Item_Date
from #AllCounts
--where last_name in ('Fletcher','Diaz')
order by last_name, item_date

open ptocursor 


Create table 
#ptotable
(SerialNumber int,
fname varchar(20),
lname varchar(50),
timecodegroup char(3), 
timecodeactual varchar(10),
mindate datetime, 
maxdate datetime, 
ptocnt int)

Create Table #WTtable
(SerialNumber int,
fname varchar(20),
lname varchar(50),
timecodegroup char(3), 
timecodeactual varchar(10),
mindate datetime, 
maxdate datetime, 
ptocnt int)

fetch next from ptocursor 
into @Serialnumber,@lname, @fname,
@timecodeGroup,@timecodeactual, @itemdate

while @@FETCH_STATUS = 0
begin
  if @var_fname=@fname and @var_lname=@lname and @Var_serial_number = @serialnumber
  begin
    if @timecodegroup='pto'
    begin
      if @count>=1
      begin
        update #ptotable
        set maxdate = @itemdate,
            ptocnt=ptocnt+1
    where fname = @fname and lname = @lname and serialnumber = @serialnumber
    select @count = @count+1
      end else
      begin
        update #ptotable
    set mindate=@itemdate,maxdate=@itemdate,ptocnt=1
    where fname = @fname and lname = @lname and serialnumber = @serialnumber
        select @count = @count+1
      end
    end else
    begin
      update #WTtable
      set ptocnt=0
      where fname = @fname and lname = @lname and serialnumber = @serialnumber
     -- and ptocnt<3
      select @count=0
    end
  end else 
  begin
    set @count=0
    set @var_fname=(select distinct first_name from #allcounts where first_name = @fname)
    set	@var_lname=(select distinct last_name from #allcounts where last_name = @lname)
    set @var_serial_number = (select distinct Serial_number from #allcounts where Serial_number = @serialnumber)
    if @timecodegroup='pto'
    begin
      if @count>=1
      begin
        update #ptotable
        set maxdate = @itemdate,
        ptocnt=ptocnt+1
    where fname = @fname and lname = @lname and serialnumber = @serialnumber
    select @count = @count+1
      end else
      begin
        insert into #ptotable(serialnumber,fname,lname,
	timecodegroup, timecodeactual,mindate, maxdate, ptocnt)
        values(@serialnumber,@fname,@lname,
	@timecodegroup,@timecodeactual,@itemdate,@itemdate,@count)
        select @count = @count+1
      end
    end else
    begin
      if @count<>0
      begin
        update #WTtable
        set ptocnt=0
        where fname = @fname and lname = @lname and serialnumber = @serialnumber
    --and ptocnt<3
        select @count=0
      end else
      begin
        insert into #WTtable(serialnumber,fname,lname,
	 timecodegroup, timecodeactual, mindate, maxdate, ptocnt)
        values(@serialnumber,@fname,@lname,
	@timecodegroup,@timecodeactual,@itemdate,@itemdate,@count)
      end 
    end
  end
fetch next from ptocursor 
into @serialnumber,@fname,@lname, 
 @timecodegroup,@timecodeactual, @itemdate
end 


close ptocursor
deallocate ptocursor

Let me know what questions you have to see if I can fit the CURSOR a little better so I don't miss these records. I'm just tickled that we've gotten it this far though. Thank you, thank you!

~Brenda

 

[antiskeptic]

oh, I commented out the

and ptocnt<3

just to see if it would make a differnce, but it did not, and I forgot to uncomment before posting this...

~Brenda

 

[donutman]

Well you did create the cursor on a Select Distinct. Just guessing but that could easily be the problem. You also do a Union All on Select Distinct tables. That's odd, but I didn't bother to examine it closely because it shouldn't hurt.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[antiskeptic]

why would the Select distinct cause a problem with the CURSOR? I honestly don't know enough about this to know...

And you are right about the Union All with the Select Distinct...just wasn't paying attention. Thanks for pointing it out.
Anything else? Anybody?
I don't have a clue why it would be skipping people...but when I call up the Select * from #allcounts...I get people with PTO records (4 in a row for a couple of them) yet it does not make it through the CURSOR. I'm baffled!

Brenda

 

[donutman]

select distinct serial_number,first_name, last_name,
TimeCode,timecode_id,Item_Date
from #AllCounts
order by last_name, item_date

That code will eliminate rows that have duplicate data. So the cursor will only process 1 of any duplicate rows in #AllCounts. Is that the problem?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[antiskeptic]

nope... I finally found th eprobelem...there was an extra field that was the culprit. Not an extra field that was just in there twice, but a TimeCodeDetail feild that needed to be eliminated. Everytime that code changed it would start the count over again, causeing a second row of data for the same person.
I took out that field and finally got what I wanted. I'm so excited to have learned something new...though since it is a CURSOR, it's not some thing I intend on using much if any ever again. *ha* But to get this project done...it was worth it.

My only problem now comes with my Crystal report. I ran this whole procedure with a certain Date range just for testing...but now I have to take that date range off so the report can choose what PayPeriodEndDate the user wants. And of course, the CURSOR takes FOREVER to run with more than about 60,000 rows, and there are at least 200,000+ rows to deal with at this point...and of course that will grow as time goes on. Does anyone know if I can use input parameters with this procedure to jump across to the Crystal Report? OR is this a question I would need to address in that forum?

Thank you again...Tim & Karl...I'm truly very grateful for all your help!

Sincerely,
BRenda