For the 9/80 Work Week, Need to Add Hours from PM of First Friday To Next Friday...

[hneal98]

Hi Everyone,

I have something like the following...

Date ........ Hours ........ 980X
03/08/13 ........ 4 ........ 98
03/08/13 ........ 2 ........ AM
03/08/13 ........ 2 ........ NULL
03/08/13 ........ 3 ........ PM
03/15/13 ........ 2 ........ 98
03/15/13 ........ 3 ........ AM
03/15/13 ........ 2 ........ NULL

I will have a set for each date in the week; however, for simplicity, I just included Friday as that is the one I am having trouble with.

The problem I am having is that I need to sum all the totals for the week and calculate overtime, if it exists. So, on the first week, you can see the various values. If the value is 98, Am, or null, it is summed for the current week. However, if it is PM, it is added to the following week instead.

Any help with this would be great.

Thanks and let me know if you need any other information.

Neal

 

[carp]

Well, here's one way:

SELECT CASE WHEN (NVL(x980,'AM') IN ('AM','98')) THEN theDate
            ELSE theDate + 7 END AS weekDate, SUM(hours) AS theTime
FROM mytable
GROUP BY  CASE WHEN (NVL(x980,'AM') IN ('AM','98')) THEN theDate
               ELSE theDate + 7 END;

 

[hneal98]

I will give it a try.

Thanks.