CODEWITHSUNDEEP
sqlsql-servercrystal-reports
MikeOscarEcho
MikeOscarEcho

Reputation: 548

Crystal query to sql?

I'm not sure how to ask this question. I need to convert this 

MACHINE HOURS [@HrsMachine]:
if left({Employee.First_Name},1)="8" then {Job_Operation_Time.Act_Setup_Hrs}+{Job_Operation_Time.Act_Run_Hrs} else 0

Crystal Report query to a sql query. I understand the table names.column part but I don't understand what the numbers that come after are, ie.

left({Employee.First_Name},1)="8"

Is this right? If Employee first name has an 8 in it, then Job Operation Time Actual Setup Hours plus Job Operation time Actual Run Hours, otherwise return nothing.

Can someone help me understand with perhaps some pseudo-code?

EDIT:

Okay I tried

SELECT TOP 50 Employee.First_Name, Job_Operation_Time.Act_Setup_Hrs, Job_Operation_Time.Act_Run_Hrs,
        CASE
          WHEN LEFT(Employee.First_Name, 1)='8' THEN 
            Job_Operation_Time.Act_Setup_Hrs + Job_Operation_Time.Act_Run_Hrs
          ELSE 0
        END FOO
FROM   Employee, Job_Operation_Time

but I'm getting this error: Warning: mssql_next_result() expects parameter 1 to be resource, object given in /home/toolplas/public_html/main/manage/adminer.php on line 673

It's working to some extent as I'm getting the first 50 employees but not the ones that start with the number 8 (the number 8s are machines). I'm not sure if the addition is working either.

Upvotes: 0

Views: 45

Answers (2)

craig
craig

Reputation: 26262

SELECT  ...,
        CASE
          WHEN LEFT(Employee.First_Name, 1)='8' THEN 
            Job_Operation_Time.Act_Setup_Hrs + Job_Operation_Time.Act_Run_Hrs
          ELSE 0
        END TOTAL_HOURS
FROM    ...
-- remove non-machines
WHERE   LEFT(Employee.First_Name, )='8'

** edit **

If you are eliminating everything that isn't a machine, then the formula could be: 

Job_Operation_Time.Act_Setup_Hrs + Job_Operation_Time.Act_Run_Hrs TOTAL_HOURS

Upvotes: 1

SOfanatic
SOfanatic

Reputation: 5573

int machineHours = 0;
if(Employee.First_Name.CharAt(0) == 8) // if the first character i.e., left most character
                                      //in an employee's first name is equal to 8
machineHours = Act_Setup_Hurs + Act_Run_hrs;

So basically the formula is only adding the hours for employees that have an '8' at the beginning of their name, everyone else gets 0.

Upvotes: 2

Related Questions