day calculations

[ang90]

I have a table as shown below.

The table is:

EmpId Week Year Day0 Day1 ..... Day14

100 20 2005 8 8 8
200 22 2003 0 0 8
300 25 2004 8 8 0
400 06 2005 0 8 8
500 08 2002 8 0 8

I am trying to select emp records as follows:

EmpId, Date of the day, Hours worked per day


Firstly, I have to calculate date of the day of a record using
week of the year and year. Then I have to increment the day by 1, 2 ...14
to get the hours worked for each particular date

Example: If week 20 of 2005 is 05/07/2005, then

05/07/2005 has 8 hrs. 05/08/2005 hrs 8 hrs and so on as given in the table.

Finally I have to print individual rows like this:

EmpId, Date of the day, Hours worked per day

Can someone help? Thank you.

 

[SantaMufasa]

Yes, Ang, we can help, but before we do, can you confirm for us your status as an Oracle user? Are you employed as an Oracle professional, are you a student, or some other status?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ang90]

I am employed as a developer. Thank you.

 

[SantaMufasa]

Ang,

Please don't take offense at my enquiry. Your scenario looked very much like a classroom scenario and we are proscribed on Tek-Tips from providing help on classroom assignments.

Also, Ang, another reason I ask is because your problem is actually reverse from typical business situations: The detail time records for an employee should already appear in your table as individual time records by DATE. But, as your data appear now, they absolutely are non-compliant with Relational First Normal Form since the values are not on individual rows...multiple values (14 days-worth) are "batched up" together on a single row. Specifically, the design is incorrect and someone should fix it by splitting the data out into individual rows with specific dates attached to each row.

The poor database design also compounds your problem because of the ambiguity of "What does WEEK 20 mean?" First of all, presuming that your example date of "05/07/2005" means "May 7, 2005", then we have ambiguity:

select to_char(to_date('05/07/2005','mm/dd/yyyy'),'WW-yyyy') from dual;

TO_CHAR
-------
19-2005

1 row selected.

As you can see, above, Oracle thinks that '05/07/2005' (Saturday, May 7, 2005) is the beginning of Week 19 of the year...And I cannot come up with any way to justify its being Week 20.

Is "Saturday, January 1, 2005" at the end of Week 52 of 2004, or is it the beginning of Week 1 of 2005? Do you want "Week Determination" to follow Oracle's perception of Weeks, or do you want some other algorithm.

You can see the "rat's nest" that derives from abrogating First Normal Form in this case.

Again, we can assert a solution to your need, but first, you must resolve the ambiguities for us.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ang90]

Hi. Here week 20 of 2005 means the first day of week 20 of year 2005 (assuming that it is 05/07/2005. I think the actual date comes from a date function). That corresponds to Day0 column in the same record. Next Day1 column corresponds to the next day which is 05/08/2005. Day2 becomes 05/09/2005 and so on ...

Next, the records should be printed (selected) as

Empid, Date of the day, Hours

100 05/07/2005 8
100 05/08/2005 8
...
100 05/21/2005 8
200 05/22/2003 0 (assuming week 22 of 2003 is 05/22/2003)

and so on for all empids ...

My first reaction after looking at the Day0 ... Day14 structure in each record is that the design is complicated. I think I should write a function to perform this. Any other ideas? Thanks.

 

[SantaMufasa]

Ang,

First, please consider this code:

col a heading "Proof for Ang" format a60
select '"'||to_char(to_date('05/07/2005','mm/dd/yyyy'),'fmMonth ddth, YYYY')||'" is the '||
to_char(to_date('05/07/2005','mm/dd/yyyy'),'fmdth "day of the" wwth "week of" YYYY') a
from dual;

Proof for Ang
-------------------------------------------------------
"May 7th, 2005" is the 7th day of the 19th week of 2005

1 row selected.

Since you are from the US, I presume that you use the same calendar that I use. Since any solution would be using Oracle's date calculations, this is an issue that you must resolve before spending any time on any type of solution.

Another issue is, Do you want for each employee a row for each business day in the company (i.e, 365-(Weekends+Holidays)) or do you want 365 rows to appear since some employees log time on weekends and holidays?

Since we still haven't come to terms with your terms, I do not feel comfortable building code for you (as I usually try to do for posters if I understand all their issues). So, I can say that your building a function to extract hours per day for an employee would be a strategy I would probably use if I were solving your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.