Carry over partial total from week 1 to week 2...

[hneal98]

Hi Everyone,

I also posted this under Oracle, but I know more about SQL Server, os it should be easy to translate any differences...

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

 

[gmmastros]

Perhaps you could use a Case statement in the group by clause. Something like this (in pseudocode).

Select   Sum...
From     Table...
Group By Case When DayOfWeek = Friday and PM = True 
              Then Date + 3 
              Else Date 
              End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hneal98]

I will give that a try. However, I don't understand why you have date + 3. Should that not be Date + 7?

Thanks for your reply.

 

[gmmastros]

3.... 7... I dunno. I was thinking that if it's a Friday, then it should be counted as the next Monday, but if it's better to add 7, then do that.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hneal98]

No, that would be wrong because then you have overtime for monday. It would need to be counted with the next Friday's total.

Thanks