March 19, 2013 at 3:44 pm
I'm still pretty new to SQL and any help would be appreciated. I'm using the following statement:
SELECT
EMPL_ID,
PAY_TYPE_ID,
SUM(LABOR_HRS) AS 'HOURS',
SUM(LABOR_COST) AS 'Labor Cost'
FROM LABOR
Where TIMESHEET_DATE >= DATEADD(day, (DATEDIFF (day, '19800104', CURRENT_TIMESTAMP) / 7) * 7, '19800104') -6
AND TIMESHEET_DATE <= DATEADD(day, (DATEDIFF (day, '19800104', CURRENT_TIMESTAMP) / 7) * 7, '19800104')
GROUP BY PAY_TYPE_ID, EMPL_ID
Which returns the following values below:
EMPL_ID
001
002
003
PAY_TYPE_ID
A
B
99
HOURS
40
40
40
Labor Cost
400
950
52
How can I change the labor cost for any pay type using '99' to return 0, while leaving the actual value as is?
For example, I would like it to be something like this...
EMPL_ID
001
002
003
PAY_TYPE_ID
A
B
99
HOURS
40
40
40
Labor Cost
400
950
0
I've tried using CASE previously without any luck (feeling pretty ignorant at this point).
March 19, 2013 at 3:49 pm
like this maybe?
SELECT
EMPL_ID,
PAY_TYPE_ID,
SUM(LABOR_HRS) AS 'HOURS',
SUM((case when pay_type_id = 99 then 0 else LABOR_COST end)) AS 'Labor Cost'
March 19, 2013 at 3:51 pm
SELECT
EMPL_ID,
PAY_TYPE_ID,
SUM(LABOR_HRS) AS 'HOURS',
SUM(case when PAY_TYPE_ID = 99 then 0 else LABOR_COST end) AS 'Labor Cost'
???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 19, 2013 at 3:53 pm
LOL Erin beat me to it.
To the OP. This one happened to be pretty simple. Generally we like to see ddl, sample data and desired output. You might want to take a few minutes and read the first article in my signature for best practices when posting questions. The concepts covered in there will help greatly when you post more in the future.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply