Solved

Cost per hour calculation

Posted on 2011-03-17
5
418 Views
Last Modified: 2012-08-14
I have the following SQL:

SELECT     a_$, a_hrs, wc
FROM         jobtran
WHERE     (trans_date > '1/1/10') AND (trans_type = 'D') AND (a_hrs > 0)

What I need is to find cost per hour by work center (wc)

'a_$' is the actual cost for time worked 'a_hrs' . So a_$ / a_hrs gives me the actual cost per hour. But I need cost per hour by wc
0
Comment
Question by:thenrich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 35160758
Try:

SELECT     wc, SUM(a_$) / SUM(a_hrs) as CostPerHour
FROM         jobtran
WHERE     (trans_date > '1/1/10') AND (trans_type = 'D') AND (a_hrs > 0)
GROUP BY wc

Open in new window

0
 
LVL 5

Expert Comment

by:boowhup
ID: 35160798
SELECT     wc, sum(a_$),sum( a_hrs)
FROM         jobtran
WHERE     (trans_date > '1/1/10') AND (trans_type = 'D') AND (a_hrs > 0)
GROUP BY wc

Open in new window


You'll still need to do the final a_$ / a_hrs(as this is what you are doing currently)  but I can add it into the SQL if you prefer.

If this isn't what you want then throw up some sample data , and show the output you are after.

0
 
LVL 5

Expert Comment

by:boowhup
ID: 35160802
Doh. beat me to it, and did it better....nice
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35161219

SELECT wc, sum(a_$) [Total Cost], sum(a_hrs) [Total Hours], SUM(a_$) / SUM(a_hrs) * 1.0 [Cost / Hour]
FROM  jobtran
WHERE trans_date > '1/1/10'
AND trans_type = 'D'
AND a_hrs > 0
GROUP BY wc
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35161432
As a passing comment, I highly discourage the use of special characters (e.g. $) and  spaces (other than the under-score character) in database object names.  All it does is cause you headaches as time goes on.

Similarly, one should not use reserved words (e.g. Table, Date, Time, Currency) as object names.

Yes, I know that you can just wrap all of those things in square brackets but why would you want to have to keep doing that? ;-)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question