Schedule daily records to week table

[johnkoh]

Hi, All

I have the Schedule daily table and Weekly table. I want to transfer the daily table to weekly table with group by Employee_Id.

The Daily table has

Emp_id	Date	Job_id
100	1/7/2013	abc
100	1/8/2013	abc
100	1/9/2013	123
200	1/7/2013	123
200	1/10/2013	xyz
200	1/11/2013	xyz
200	1/12/2013	xyz

How do you make this weekly view?


Weekly table

Emp_id	Sun	Mon	Tue	Wed	Thu	Fri	Sat
100		abc	abc	123
200		123			xyz	xyz	xyz

[Stan Mathews]

Re: Schedule daily records to week table

You could run a crosstab and wind up with

EMP_ID	_2	_3	_4	_5	_6	_7
100      	abc      	abc      	123      	         	         	         
200      	123      	         	         	xyz      	xyz      	xyz

and change the field names, but you need a record for each day whether there is activity or not.

[johnkoh]

Re: Schedule daily records to week table

Hi Stan,

I used the Crosstab operation but I have the problems.

If there are no scheduled date, specially sunday, Not able to use the Calculated Value and the result is "Error" on the reports.

[johnkoh]

Re: Schedule daily records to week table

Hi All,

I solved this problem, about 90%, without crosstab operation.
(1) I get the unique records from "Day" table with weekly date range. (2) Save the Emp_Id and Date value at "Week" table. (3) Start to Read and find the records from "Week" table with same date range, and loop it (4) Find the Appointments from "Day" table (5) I used the "Select .. Case" to make a condition and Change records, <tbl>.change_begin(), on the "Week" table.

Only my problem is remained that How to get the Week number at beginning or end of the year, example, between 12/30/2012 to 1/5/2013 week.

query.filter ="cyear(Start_date) ="+quote(cyear(vFindDate))+" .and. week(Start_date) ="+s_quote( week(vFindDate))

I have a start_date on my "Week" table and "12/31/2012" which is Monday and the Week number is "53", the year is 2012.
When I enter the "vFindDate" value as, any date of week, 12/31/12 or 1/2/13 ... then I want to find the same week range of the vFindate which from 12/31/2012 to sunday 1/6/13.

Any Idea?

or re-write the query.filter with between()?

[Stan Mathews]

Re: Schedule daily records to week table

Hi Stan,

I used the Crosstab operation but I have the problems.

If there are no scheduled date, specially sunday, Not able to use the Calculated Value and the result is "Error" on the reports.

Was there some part of

but you need a record for each day whether there is activity or not

that wasn't clear?

[johnkoh]

Re: Schedule daily records to week table

Stan,

Thank you and I found this "Eval_Valid expression does not work", http://msgboard.alphasoftware.com/al...ght=eval_valid.
It was helped me to get the direction.

After a few other way tried, I applied the "Calendar report coding, function calendr3.calendar_report " from xBasic everyone book.
Now, I can print the weekly schedule.

However, I cannot find any good idea for saving when I have two or more schedules on the same date.