complex expression Q 2

[stillwillyboy]

I am trying to achieve the following calculations and have the calc’d numbers appear in my query pane.

Units/HoursWorked = UnitsPerHour ‘appears as desired
PrevailingWage/STD = PieceRate ‘appears as desired
UnitsPerHour/STD = Percent ‘doesn’t work
UnitsPerHour * PieceRate = Pay ‘doesn’t work

How do I adjust the following code to get the calc’d Percent and Pay fields to display?

SELECT     tbl_TEST_TimesheetFile.Units, tbl_TEST_TimesheetFile.HoursWorked, 
           tbl_TEST_TimesheetFile.Units / tbl_TEST_TimesheetFile.HoursWorked AS UnitsPerHour,
 tbl_JobsStepsTable.PrevailingWage, 
                      tbl_JobsStepsTable.GuaranteedStepWage, tbl_JobsStepsTable.Standard AS STD, 
                      tbl_JobsStepsTable.PrevailingWage / tbl_JobsStepsTable.Standard AS PIECERATE
FROM         tbl_TEST_TimesheetFile INNER JOIN
                      tbl_JobsStepsTable ON tbl_TEST_TimesheetFile.CustomerNumber = tbl_JobsStepsTable.CustomerNumber

Also, do you know of any good web sites that cover complex expressions in a simple manner? The books seem to cover the Select, Insert, Update and Delete more than expressions.

TIA,

Bill

 

[bborissov]

Waht exctly doesn't works?

SELECT tbl_TEST_TimesheetFile.Units,
       tbl_TEST_TimesheetFile.HoursWorked,
       (tbl_TEST_TimesheetFile.Units /
       tbl_TEST_TimesheetFile.HoursWorked) AS UnitsPerHour,
       tbl_JobsStepsTable.PrevailingWage,
       tbl_JobsStepsTable.GuaranteedStepWage,
       tbl_JobsStepsTable.Standard AS STD,
       (tbl_JobsStepsTable.PrevailingWage / 
        tbl_JobsStepsTable.Standard) AS PIECERATE,
       (tbl_TEST_TimesheetFile.Units /
        tbl_TEST_TimesheetFile.HoursWorked)/
        tbl_JobsStepsTable.Standard AS Percent,
        (tbl_TEST_TimesheetFile.Units /
        tbl_TEST_TimesheetFile.HoursWorked)/
        (tbl_JobsStepsTable.PrevailingWage / 
         tbl_JobsStepsTable.Standard) AS Pay
FROM tbl_TEST_TimesheetFile
     INNER JOIN tbl_JobsStepsTable ON
                tbl_TEST_TimesheetFile.CustomerNumber =
                tbl_JobsStepsTable.CustomerNumber

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

instead of UnitsPerHour/STD = Percent you need to repeat the whole calculation again

(tbl_TEST_TimesheetFile.Units / tbl_TEST_TimesheetFile.HoursWorked )/(tbl_JobsStepsTable.GuaranteedStepWage, tbl_JobsStepsTable.Standard) as Percent


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

Or:

SELECT Units, HoursWorked, UnitsPerHour,
       PrevailingWage, GuaranteedStepWage,
       Std, PIECERATE,
       UnitsPerHour/Std AS Percent,
       UnitsPerHour*PieceRate AS Pay
FROM ( SELECT tbl_TEST_TimesheetFile.Units,
              tbl_TEST_TimesheetFile.HoursWorked,
              (tbl_TEST_TimesheetFile.Units /
               tbl_TEST_TimesheetFile.HoursWorked) AS UnitsPerHour,
               tbl_JobsStepsTable.PrevailingWage,
               tbl_JobsStepsTable.GuaranteedStepWage,
               tbl_JobsStepsTable.Standard AS STD,
              (tbl_JobsStepsTable.PrevailingWage /
               tbl_JobsStepsTable.Standard) AS PIECERATE
FROM tbl_TEST_TimesheetFile
     INNER JOIN tbl_JobsStepsTable ON
                tbl_TEST_TimesheetFile.CustomerNumber =
                tbl_JobsStepsTable.CustomerNumber) Tbl1

BTW I have an error in my first suggestion I use division to get PAY, now I see I must use multiplication


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[stillwillyboy]

Denis,
Where in my procedure do I put the code? I keep getting a Syntax error message near “Percent”. I get the same error when I use Boris' code. This is what I am trying:

SELECT     dbo.tbl_TEST_TimesheetFile.Units, dbo.tbl_TEST_TimesheetFile.HoursWorked, 
                      dbo.tbl_TEST_TimesheetFile.Units / dbo.tbl_TEST_TimesheetFile.HoursWorked AS UnitsPerHour, dbo.tbl_JobsStepsTable.PrevailingWage, 
                      dbo.tbl_JobsStepsTable.GuaranteedStepWage, dbo.tbl_JobsStepsTable.Standard AS STD, 
                      dbo.tbl_JobsStepsTable.PrevailingWage / dbo.tbl_JobsStepsTable.Standard AS PIECERATE, 
                      (dbo.tbl_TEST_TimesheetFile.Units / dbo.tbl_TEST_TimesheetFile.HoursWorked) / dbo.tbl_JobsStepsTable.STD AS PERCENT
FROM         dbo.tbl_TEST_TimesheetFile INNER JOIN
                      dbo.tbl_JobsStepsTable ON dbo.tbl_TEST_TimesheetFile.CustomerNumber = dbo.tbl_JobsStepsTable.CustomerNumber

Units = 600
HoursWorked = 5
UnitsPerHour = 120 (Units/HoursWorked)
Std = 500
Percent = .24 (UnitsPerHour/Std)
PrevailingWage = 7.25
PieceRate = .0145 (PrevailingWage/Std)
Pay = 8.70 (PieceRate * Units) i.e. .0145 * 600

 

[stillwillyboy]

Denis/Boris,

"Percent" and "Std" are keywords. DOH!

Thanks.

 

[bborissov]

If you want to have such a columns you must add a square brackets around them:

SELECT bla-bla AS [Percent]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP