Select Case Help Needed HERE!!!

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

SQL Server 2005 Forums

Transact-SQL (2005)

Select Case Help Needed HERE!!!

Author

Topic

nfedei
Starting Member

4 Posts

Posted - 2007-11-04 : 14:31:02

Hello All...I'm working on a project for Reporting Services. What I need to do is take the information from the HOURS_WORKED column and separate them into three different columns depending on a MULTIPLIER_1. If the MULTIPLIER_1 = 1.000 then I need it to go to the hours column, if the MULTIPLIER_1 = 1.500 then I need it to go to the ot_hours column, and if the MULTIPLIER_1 = 2.000 then I need it to go to the double_hours column.

I comprised this CASE Statment and it seems to work just fine. It just does not populate the information to the column I need. I can't find a way to do it.

SELECT

CASE LABOR_TICKET.MULTIPLIER_1
WHEN '1.000' THEN HOURS_WORKED
ELSE NULL

END

FROM LABOR_TICKET

Any suggestions???

Thanks
Nick

Kristen
Test

22859 Posts

Posted - 2007-11-04 : 14:39:03

[code]
SELECT CASE LABOR_TICKET.MULTIPLIER_1
WHEN '1.000' THEN HOURS_WORKED
ELSE NULL
END AS [Hours],
CASE LABOR_TICKET.MULTIPLIER_1
WHEN '1.500' THEN HOURS_WORKED
ELSE NULL
END AS [ot_hours],
CASE LABOR_TICKET.MULTIPLIER_1
WHEN '2.000' THEN HOURS_WORKED
ELSE NULL
END AS [double_hours]
FROM LABOR_TICKET
[/code]
Kristen

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-04 : 14:40:47

Sounds like you want to multiple the hours worked by the multiplier column

Select
Case When Multiplier_1 = 1.0 then Hours_Worked * 1.0 end as Regular_Hours,
Case When Multiplier_1 = 1.5 then OT_Hours * 1.5 end as OT_Hours,
Case When Multiplier_1 = 2.0 then Double_hours * 2.0 end as DoubleTimeHours
From Labor_Ticket

Kristen
Test

22859 Posts

Posted - 2007-11-04 : 14:41:21

That was using your example, actually I would prefer:


SELECT CASE WHEN LABOR_TICKET.MULTIPLIER_1 = '1.000'
       THEN HOURS_WORKED
       ELSE NULL
       END AS [Hours],
       CASE WHEN LABOR_TICKET.MULTIPLIER_1 = '1.500'
       THEN HOURS_WORKED
       ELSE NULL
       END AS [ot_hours],
       CASE WHEN LABOR_TICKET.MULTIPLIER_1 = '2.000'
       THEN HOURS_WORKED
       ELSE NULL
       END AS [double_hours]
FROM LABOR_TICKET

Do NOT use single quotes if the multiplier_1 column is of numeric datatype, i.e.

MULTIPLIER_1 = '1.500'
would be
MULTIPLIER_1 = 1.500

Kristen

nfedei
Starting Member

4 Posts

Posted - 2007-11-04 : 15:08:18

Kristen,
Thanks so much for the rapid response.
I think you have the idea as to what I'm trying to accomplish...but the last code did not populate the columns as I needed.

It queried the database correctly...just did not put the results returned into the columns.

Here was the code I ran:
SELECT
CASE WHEN LABOR_TICKET.MULTIPLIER_1 = 1.000
THEN HOURS_WORKED
ELSE NULL
END AS [hours],
CASE WHEN LABOR_TICKET.MULTIPLIER_1 = 1.500
THEN HOURS_WORKED
ELSE NULL
END AS [ot_hours],
CASE WHEN LABOR_TICKET.MULTIPLIER_1 = 2.000
THEN HOURS_WORKED
ELSE NULL
END AS [double_hours]

FROM LABOR_TICKET
Thanks
Nick

Kristen
Test

22859 Posts

Posted - 2007-11-04 : 15:20:44

What did you get as output?

And what did you expect to get?

Probably worth selecting some additional columns for debugging - e.g. to make sure that [MULTIPLIER_1] is exactly one of those three values, and some sort of ID/Name so you know which row is which.

Kristen

nfedei
Starting Member

4 Posts

Posted - 2007-11-04 : 15:28:36

The results were exactly what I needed...I just need those results to populate the columns in the table. This will allow me to show those three tables and the figures in the. For example, when I create the report for this..I need a column for reg hours, ot hours and double hours...but the hours worked only contains the actual hours. it's the multiplier that determins what type of hours it actually are. So when the 1.000 multiplier is there, hours are regular...1.500 multiplier is ot and 2.000 is double. But now I need to seperate that HOURS_WORKED COLUMN into three separate columns.

Make any sense??

The results in the query are what I need...I just need them to save to the table..

Kristen
Test

22859 Posts

Posted - 2007-11-04 : 17:05:15

"I just need them to save to the table"

That bit I don't understand. Do you want to update the table (instead of just displaying them)?

Kristen

nfedei
Starting Member

4 Posts

Posted - 2007-11-10 : 11:39:35

quote:
Originally posted by Kristen

"I just need them to save to the table"

That bit I don't understand. Do you want to update the table (instead of just displaying them)?

Kristen

Yes...It hit me last week...UPDATE!!! NOT SELECT!!! Sorry about that!
Thanks
Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources