By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,369 Members | 966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,369 IT Pros & Developers. It's quick & easy.

Daily Totals ( Like a Time Sheet) Sumerized by Date.

P: 9
Greetings,

I am working on a project and need help with a querie.

Project: A Calculated Time Sheet for Job costing Combining same values and sub totals

Ie.
Name | Date | Hours (hund) | Job | Phase | Elivation |
Brad 1/1/07 3.5 1 1 1
Brad 1/1/07 2.0 3 1 2
Brad 1/2/07 8.0 1 2 1

I would like to returne this

Name | Date | Hours (hund) |
Brad 1/1/07 5.5
Brad 1/2/07 8.0

Thanks for your help in advance.

_ Brad
May 7 '07 #1
Share this Question
Share on Google+
8 Replies


JConsulting
Expert 100+
P: 603
Greetings,

I am working on a project and need help with a querie.

Project: A Calculated Time Sheet for Job costing Combining same values and sub totals

Ie.
Name | Date | Hours (hund) | Job | Phase | Elivation |
Brad 1/1/07 3.5 1 1 1
Brad 1/1/07 2.0 3 1 2
Brad 1/2/07 8.0 1 2 1

I would like to returne this

Name | Date | Hours (hund) |
Brad 1/1/07 5.5
Brad 1/2/07 8.0

Thanks for your help in advance.

_ Brad
well, let' see.
You want to select Brad and the day and total his hours

Select [Name], [Date], sum([Hours] as TotalHours
From Yourtable
Group By [Name], [Date]
May 7 '07 #2

P: 9
Awesome Job. So simple but then again that why your guys are the experts.

Now Part 2 if you can help is a little more math centric.

I have a total

Name | Date | Total |
Brad 1/1/07 11

I want to do a calculation so here it is in english however I would like the SQL

if total is less than or equil to 8 Multiply by Rate (Rate is in a Table called Employees and has a relation) and show in a field called Dollars. ELSE Total subtract 8 and multiply remainder by (Rate * 1.5) + (8 * Rate) and place into field called Dollars.

Ie. (Rate = 10.0)
Name | Date | Total | Dollars |
Brad 1/1/07 11 $125

Math
Figure OverTimeHours 11-8=3 Figure Dollars 3*10 =30
Figure Dollars with OverTime Calc 30*1.5=45 $45.00 in Overtime
Figure RegularDollars 8*10=80 $80.00 in Regular
Figure Total 45+80=125 $125 in Total Dollars

Thanks again for your expertice.

Brad
May 8 '07 #3

JConsulting
Expert 100+
P: 603
Awesome Job. So simple but then again that why your guys are the experts.

Now Part 2 if you can help is a little more math centric.

I have a total

Name | Date | Total |
Brad 1/1/07 11

I want to do a calculation so here it is in english however I would like the SQL

if total is less than or equil to 8 Multiply by Rate (Rate is in a Table called Employees and has a relation) and show in a field called Dollars. ELSE Total subtract 8 and multiply remainder by (Rate * 1.5) + (8 * Rate) and place into field called Dollars.

Ie. (Rate = 10.0)
Name | Date | Total | Dollars |
Brad 1/1/07 11 $125

Math
Figure OverTimeHours 11-8=3 Figure Dollars 3*10 =30
Figure Dollars with OverTime Calc 30*1.5=45 $45.00 in Overtime
Figure RegularDollars 8*10=80 $80.00 in Regular
Figure Total 45+80=125 $125 in Total Dollars

Thanks again for your expertice.

Brad

Word problems...hated them in school...never thought I'd be interpreting them for a living. I might have a partnthesis or two out of order...but this is to help you learn right?
J

Expand|Select|Wrap|Line Numbers
  1. Select [Name], [Date], sum([Hours] as TotalHours, iif(sum([Hours])<=8,sum([Hours])*[Employees].[Rate],sum([Hours])-8)*([employees].[rate]*1.5 + ([employees].[rate] * 8)) as Dollars
  2. From Yourtable  INNER JOIN YourTable ON yourtable.ID = employees.ID
  3. Group By [Name], [Date];
  4.  
May 8 '07 #4

P: 9
Ok we are almost there however there is somethign wrong with the Math.

Here is My Control

11 hours rate is 10.00 Total should be 125.


With the code I am getting 240. Can you please help me troubleshoot this code.

Thanks Again!

Here is my Code

Expand|Select|Wrap|Line Numbers
  1. SELECT [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Sum([Time Sheets].[Hours (Hund)]) AS TotalHours,Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, TotalHours -8) * (Employees.Rate * 8 ) as Dollars
  2. FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee
  3. GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;
  4.  
May 8 '07 #5

JConsulting
Expert 100+
P: 603
Ok we are almost there however there is somethign wrong with the Math.

Here is My Control

11 hours rate is 10.00 Total should be 125.


With the code I am getting 240. Can you please help me troubleshoot this code.

Thanks Again!

Here is my Code

Expand|Select|Wrap|Line Numbers
  1. SELECT [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Sum([Time Sheets].[Hours (Hund)]) AS TotalHours,Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, TotalHours -8) * (Employees.Rate * 8 ) as Dollars
  2. FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee
  3. GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;
  4.  

you were missing the overtime calc

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. [Time Sheets].Employee, 
  3. [Time Sheets].[Date Of Hours], 
  4. Sum([Time Sheets].[Hours (Hund)]) AS TotalHours,
  5. Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, ((TotalHours - 8) * 1.5) + (Employees.Rate * 8 ) as Dollars
  6. FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee
  7. GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;
  8.  
May 8 '07 #6

P: 9
Awesome Job I added the Rate in one spot and it works perfectly. Great Job. Can I give you Kudos points on this form?


Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. [Time Sheets].Employee, 
  3. [Time Sheets].[Date Of Hours], 
  4. Sum([Time Sheets].[Hours (Hund)]) AS TotalHours,
  5. Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, ((TotalHours - 8) * 1.5) * (Employees.Rate)+ (Employees.Rate * 8 ) )as Dollars
  6. FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee
  7. GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;
May 8 '07 #7

JConsulting
Expert 100+
P: 603
Awesome Job I added the Rate in one spot and it works perfectly. Great Job. Can I give you Kudos points on this form?


Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. [Time Sheets].Employee, 
  3. [Time Sheets].[Date Of Hours], 
  4. Sum([Time Sheets].[Hours (Hund)]) AS TotalHours,
  5. Employees.Rate,iif(TotalHours <=8, TotalHours *  Employees.Rate, ((TotalHours - 8) * 1.5) * (Employees.Rate)+ (Employees.Rate * 8 ) )as Dollars
  6. FROM Employees INNER JOIN [Time Sheets] ON Employees.ID = [Time Sheets].Employee
  7. GROUP BY [Time Sheets].Employee, [Time Sheets].[Date Of Hours], Employees.Rate;
Happy to help...Thanks for the kudos :)
J
May 8 '07 #8

NeoPa
Expert Mod 15k+
P: 31,475
Happy to help...Thanks for the kudos :)
J
We notice these things :)
May 11 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.