Grouping with two Criterion and time point structure

[sqlbraindead]

The problem is if I want to group my database on the time basis, how can reconcile the grouping of two different criteria structure?

This is an example.
Two table I want to use these to get a table with each staff and calculate their total working hours.

Master Table (2 Columns)
StaffID Grade
1 A
2 A
3 B
4 C
5 D
6 E

Work Hour Table (5 Columns)
StaffID Grade HourType Date WorkingHours
1 A Holiday 11/01/2008 8
2 A Holiday 11/01/2008 8
2 A Holiday 13/01/2008 4
4 C Holiday 11/01/2008 8
4 C Regular 12/01/2008 7
4 C Regular 13/01/2008 4
6 E Holiday 11/01/2008 8
6 E Regular 12/01/2008 5
6 E Regular 13/01/2008 6
5 D Regular 11/01/2008 6
5 D Regular 12/01/2008 7
5 D Regular 13/01/2008 5


The logic is:
If Grade B or above, they will not record regular work hours, only holiday will be recorded. On regular working day, Grade B or above will be given 7 hours working hours. The others are recorded by their working hours.


Result Table: 3 Days 11/01/2008 – 13/01/2008 Working Hours (3 Columns)
StaffID Grade Workinghours
1 A 22
2 A 19
3 B 21
4 C 19
5 D 18
6 E 19

My issue here is I am successfully group 1 date data to cal the salary for each day. But when it comes to more than 1 day, let say 3 days, I have got problem. For the Grade B or above, there can be a logic like count the holidays for grade B or above, then the total days minus the holidays. The resulting days will be multiplied by 7. The others just get the sum of their working hours with criteria of the start and end days.
But I can’t put this in SQL.

[ck9663]

The problem is if I want to group my database on the time basis, how can reconcile the grouping of two different criteria structure?

This is an example.
Two table I want to use these to get a table with each staff and calculate their total working hours.

Master Table (2 Columns)
StaffID Grade
1 A
2 A
3 B
4 C
5 D
6 E

Work Hour Table (5 Columns)
StaffID Grade HourType Date WorkingHours
1 A Holiday 11/01/2008 8
2 A Holiday 11/01/2008 8
2 A Holiday 13/01/2008 4
4 C Holiday 11/01/2008 8
4 C Regular 12/01/2008 7
4 C Regular 13/01/2008 4
6 E Holiday 11/01/2008 8
6 E Regular 12/01/2008 5
6 E Regular 13/01/2008 6
5 D Regular 11/01/2008 6
5 D Regular 12/01/2008 7
5 D Regular 13/01/2008 5


The logic is:
If Grade B or above, they will not record regular work hours, only holiday will be recorded. On regular working day, Grade B or above will be given 7 hours working hours. The others are recorded by their working hours.


Result Table: 3 Days 11/01/2008 – 13/01/2008 Working Hours (3 Columns)
StaffID Grade Workinghours
1 A 22
2 A 19
3 B 21
4 C 19
5 D 18
6 E 19

My issue here is I am successfully group 1 date data to cal the salary for each day. But when it comes to more than 1 day, let say 3 days, I have got problem. For the Grade B or above, there can be a logic like count the holidays for grade B or above, then the total days minus the holidays. The resulting days will be multiplied by 7. The others just get the sum of their working hours with criteria of the start and end days.
But I can’t put this in SQL.

where did you get this:

2 A 19
3 B 21

-- CK

[sqlbraindead]

where did you get this:

2 A 19
3 B 21

-- CK

2 is the id and A is the given grade hence 19 = 8 + 4 + 7. 8 is the holiday hour on 11/01/2008. 4 is the holiday hour on 13/01/2008 and the remaining day 12/01/2008. It doesn't get recorded because he is grade A, so 7 hours are given.

for id3, 21 = 7 * 3 since it is not recorded at all in the Work Hour Table and he is grade B, so 7 hours is given for each day. For 3 days, it is 21.

[ck9663]

2 is the id and A is the given grade hence 19 = 8 + 4 + 7. 8 is the holiday hour on 11/01/2008. 4 is the holiday hour on 13/01/2008 and the remaining day 12/01/2008. It doesn't get recorded because he is grade A, so 7 hours are given.

for id3, 21 = 7 * 3 since it is not recorded at all in the Work Hour Table and he is grade B, so 7 hours is given for each day. For 3 days, it is 21.

got it... i think you might need more than just a simple query here...the reason is the work/shift table is not complete. you have to check if something is missing and assume (7 hours) if they are not recorded...

i think cursor is your best path...

-- CK

[sqlbraindead]

got it... i think you might need more than just a simple query here...the reason is the work/shift table is not complete. you have to check if something is missing and assume (7 hours) if they are not recorded...

i think cursor is your best path...

-- CK

Any idea of how to put the codings?

(CASE WHEN PRSRG < 'B' THEN (CASE WHEN APINC = 'HOLIDAY' THEN APHRS ELSE 7 END)
ELSE APHRS END) END) AS Workhrs

This is part of the logic that I use in view to get the daily working hours. However, can I use a looping to get round the problem of the incomplete database for multiple day grouping. Or another attempt that I failed is to create a new tempoary table to make the grouping easier to handle. Any idea is appreciated!!

Thanks.

[ck9663]

on your first posting. when you said, not recorded, do you mean it's not on the table (no record on the table?) or you just did not include it on the sample data?

-- CK

[sqlbraindead]

on your first posting. when you said, not recorded, do you mean it's not on the table (no record on the table?) or you just did not include it on the sample data?

-- CK

They won't be on the Work Hour Table because they will automatically recorded as 7 hrs if it is not holiday.