Sum hours per cost - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum hours per cost

Author

Topic

mrspotter
Starting Member

1 Post

Posted - 2015-04-21 : 10:41:10

I am querying a database that has hours worked listed by pay rate.
I want to return sum of hours per pay rates I have been given but hours are stored in multiple columns. So If I run the below query for one worker it returns.

Hours Bill_rate_1 Hours_2 Bill_rate_2
34.26 10.26 0.00 0.00
5.74 10.26 5.68 9.67

I want to report to the business:
40hrs 10.26
5.68 9.67

The query I have written is below, sorry im totally new to SQL so sorry if im not going in the correct direction

Thanks

Select Sum(Hours_1) As Hours,Bill_rate_1,Sum(Hours_2) as Hours_2,Bill_rate_2--,Hours_3,Bill_rate_3
From Valid_Timesheets
Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198') and
((Bill_Rate_1 = £10.26 or
Bill_Rate_1 = £9.67 or
Bill_Rate_1 = £8.27 or
Bill_Rate_1 = £7.90 or
Bill_Rate_1 = £8.50 or
Bill_Rate_1 = £8.09 or
Bill_Rate_1 = £9.05 or
Bill_Rate_1 = £8.58 or
Bill_Rate_1 = £11.64 or
Bill_Rate_1 = £10.90 OR
Bill_Rate_2 = £10.26 or
Bill_Rate_2 = £9.67 or
Bill_Rate_2 = £8.27 or
Bill_Rate_2 = £7.90 or
Bill_Rate_2 = £8.50 or
Bill_Rate_2 = £8.09 or
Bill_Rate_2 = £9.05 or
Bill_Rate_2 = £8.58 or
Bill_Rate_2 = £11.64 or
Bill_Rate_2 = £10.90 or
Bill_Rate_3 = £10.26 or
Bill_Rate_3 = £9.67 or
Bill_Rate_3 = £8.27 or
Bill_Rate_3 = £7.90 or
Bill_Rate_3 = £8.50 or
Bill_Rate_3 = £8.09 or
Bill_Rate_3 = £9.05 or
Bill_Rate_3 = £8.58 or
Bill_Rate_3 = £11.64 or
Bill_Rate_3 = £10.90 ))
Group by Bill_rate_1,Bill_rate_2

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-21 : 11:01:10

Does it work?

lcblank
Starting Member

10 Posts

Posted - 2015-04-24 : 15:03:26

I am new to sql as well! I am not sure if i understand your question, but if you want a sum by category you can use


group by bills_rate

or whatever your table structure calls for

Kristen
Test

22859 Posts

Posted - 2015-04-25 : 06:57:12

This perhaps?

Select Sum(Hours) As Hours, Bill_rate
FROM
(
Select Hours_1 As Hours, Bill_rate_1 AS Bill_rate,
    Department, tax_period, tax_year, PERSONNEL_REF, ...
From Valid_Timesheets
WHERE (Hours_1 <> 0.0 AND Hours_1 IS NOT NULL)  -- Don't include any unnecessary rows - more import for Hours_999 I expect!!
UNION ALL
Select Hours_2 As Hours, Bill_rate_2 AS Bill_rate,
    Department, tax_period, tax_year, PERSONNEL_REF, ...
From Valid_Timesheets
WHERE (Hours_2 <> 0.0 AND Hours_2 IS NOT NULL)
UNION ALL
...
) AS X
Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198')
      ...
GROUP BY Bill_rate

You could duplicate the

Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198')

in every UNION select (and exclude the additional columns

Department, tax_period, tax_year, PERSONNEL_REF, ...

from the SELECT list for each UNION). Depends what is easier for you to work with. I'm presuming you want to "fiddle" with Department etc. so having only one WHERE clause, at the bottom, would be easier.

Performance would probably be better if you have the WHERE clause in every UNION ... but unless your table has >100,000 rows I doubt it makes much difference, and I would go for simplicity.

However, if you will actually use @ParameterVariables (rather than hard-wired values as per your example) it might be worthwhile including the WHERE clause in every UNION).

Redesigning the table NOT to have multiple columns for Hours and Rates would be a good solution too

Kristen
Test

22859 Posts

Posted - 2015-04-25 : 07:05:24

Actually, if that does work for you?, rather than going to the trouble of redesigning the table (and changing all the APPs ...) you could just create a VIEW:


CREATE VIEW Valid_Timesheets_VIEW
AS
SELECT SomeUniquePKeyID AS V_SomeUniquePKeyID,
       Hours_1 As V_Hours,
       Bill_rate_1 AS V_Bill_rate
WHERE (Hours_1 <> 0.0 AND Hours_1 IS NOT NULL)
UNION ALL
SELECT SomeUniquePKeyID,
       Hours_2,
       Bill_rate_2
WHERE (Hours_2 <> 0.0 AND Hours_2 IS NOT NULL)
UNION ALL
...

and then you can do


Select Sum(V_Hours) As Hours, V_Bill_rate AS Bill_rate
FROM Valid_Timesheets
     JOIN Valid_Timesheets_View
         ON V_SomeUniquePKeyID = SomeUniquePKeyID
Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198') and 
...
Group by V_Bill_rate

That would make writing queries easier, but I'm not sure how well it will perform. Again, if <100,000 rows in the table I doubt any "poor" performance will be noticeable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources