sql calculations 2

[WaveOut]

I need to sum several fields then take product and use it in another calculation. For example:

select sum(day_hrs)+sum(night_hrs)+sum(day_ot_hrs)+sum(night_ot_hrs) as total_hrs from Craft where weeklyuid = @weekid and craft_category = 'Boilermaker'


select sum(day_hrs)+sum(night_hrs)+sum(day_ot_hrs)+sum(night_ot_hrs) as apprentice_hrs from Craft where weeklyuid = @weekid and craft_category = 'Boilermaker' and craft_group = '3'

Once total_hrs and apprentice_hrs has been calculated I need to do the following:

ratio = apprentice_hrs / total_hrs

Whats the best way to do this?

Dave

 

[gmmastros]

The simplest way would be to create variables and assign those variables to each of the calculations and then return the ratio. Like this...

Declare @TotalHours Int
Declare @ApprenticeHours Int

select @TotalHours = sum(day_hrs)+sum(night_hrs)+sum(day_ot_hrs)+sum(night_ot_hrs) as total_hrs 
from   Craft 
where  weeklyuid = @weekid 
       and craft_category = 'Boilermaker'


select @ApprenticeHours = sum(day_hrs)+sum(night_hrs)+sum(day_ot_hrs)+sum(night_ot_hrs) as apprentice_hrs 
from   Craft 
where  weeklyuid = @weekid 
       and craft_category = 'Boilermaker' 
       and craft_group = '3'

Select Case When IsNull(@TotalHours, 0) = 0 Then 0
            Else @ApprenticeHours / @TotalHours
            End As Ratio

You could do the whole thing in 1 query, but it would be 'kinda ugly' and therefore harder to support.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

SELECT Total_Hrs, Apprentice_Hrs, Apprentice_Hrs/Total_Hrs AS Ratio
     FROM (SELECT SUM(day_hrs+night_hrs+day_ot_hrs+night_ot_hrs) AS Total_Hrs,
       SUM(CASE WHEN craft_group = '3' THEN day_hrs+night_hrs+day_ot_hrs+night_ot_hrs ELSE 0 END) AS Apprentice_Hrs
                 FROM Craft
                 WHERE weeklyuid = @weekid and
                       craft_category = 'Boilermaker') Tbl1

not tested

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

 

[WaveOut]

Thanks to everyone that responded. I was able to use the code that gmmastros provide. However I need to incorporate the ratio calculation into the following sql:

SELECT     Project_1.ProjectName, Project_1.OpCo, Project_1.Plant, Project_1.StartDate, Project_1.EndDate, Project_1.ProjectNumber, dbo.Plants.plant_name, 
                      dbo.OpCo.opco AS Expr4, dbo.WeeklyData.start_date, dbo.WeeklyData.weeklyUID, dbo.WeeklyData.end_date, dbo.WorkSchedule.WorkSchedule, 
                      dbo.WeeklyData.workscheduleID, dbo.WeeklyData.osha_recordable, dbo.WeeklyData.lost_time, dbo.WeeklyData.cost_plus, 
                      dbo.WeeklyData.unit_outage, dbo.WeeklyData.firm_price, dbo.WeeklyData.construction, dbo.WeeklyData.comment, dbo.WeeklyData.general_maint, 
                      dbo.Craft2.craft_name, dbo.Craft2.craft_group, dbo.Craft2.craft_category, dbo.Craft2.day_count, dbo.Craft2.night_count, dbo.Craft2.day_hrs, 
                      dbo.Craft2.day_ot_hrs, dbo.Craft2.night_hours, dbo.Craft2.night_ot_hrs, dbo.Craft2.ex_absent, dbo.Craft2.unex_absent, dbo.Craft2.wage, 
                      dbo.Contractors.contractor_name, dbo.OpCo.id
FROM         dbo.Contractors INNER JOIN
                      dbo.Project Project_1 ON dbo.Contractors.myid = Project_1.myContractor INNER JOIN
                      dbo.Plants ON dbo.Plants.id = Project_1.Plant INNER JOIN
                      dbo.OpCo ON dbo.OpCo.id = Project_1.OpCo INNER JOIN
                      dbo.WeeklyData ON Project_1.projectUID = dbo.WeeklyData.projectUID INNER JOIN
                      dbo.WorkSchedule ON dbo.WorkSchedule.id = dbo.WeeklyData.workscheduleID INNER JOIN
                      dbo.Craft2 ON dbo.WeeklyData.weeklyUID = dbo.Craft2.weeklyUid
WHERE     (dbo.WeeklyData.weeklyUID = @weekid) AND (dbo.Craft2.day_hrs > 0) OR
                      (dbo.WeeklyData.weeklyUID = @weekid) AND (dbo.Craft2.night_hours > 0) OR
                      (dbo.WeeklyData.weeklyUID = @weekid) AND (dbo.Craft2.ex_absent > 0) OR
                      (dbo.WeeklyData.weeklyUID = @weekid) AND (dbo.Craft2.unex_absent > 0)
ORDER BY dbo.Craft2.craft_category, dbo.Craft2.craft_group

Rather than me setting the craft_category = 'Boilermaker' I need it to be assigned a value from craft2.craft_category. I need to calculate a Ratio for each row that is returned from the above SQL statement. How is this done?

Dave

 

[WaveOut]

Let me try to simplify this some.

I use the above sql code to return data from a table. Now what I would like to do is on each row that is returned I would like to run the following stored procedure:

CREATE PROCEDURE spCalcRatio
        @weekid varchar(75),@category varchar(25)
AS
BEGIN
Declare @TotalHours float
Declare @ApprenticeHours float

select @TotalHours = isnull(sum(day_hrs),0)+isnull(sum(night_hours),0)+isnull(sum(day_ot_hrs),0)+isnull(sum(night_ot_hrs),0) 
from   Craft2 
where  weeklyuid = @weekid 
       and craft_category = @category

select @ApprenticeHours = isnull(sum(day_hrs),0)+isnull(sum(night_hours),0)+isnull(sum(day_ot_hrs),0)+isnull(sum(night_ot_hrs),0) 
from   Craft2 
where  weeklyuid = @weekid 
       and craft_category = @category
       and craft_group = '3'

Select Case When IsNull(@TotalHours, 0) = 0 Then 1
            Else @ApprenticeHours / @TotalHours
            End As Ratio

end
GO

My problem is that the parameter value of @category could change with each row. Currently the stored procedure returns the correct value but only for the initial value of the @category. How can I have each row call the stored procedure and provide the category for that row?

Dave

 

[SQLSister]

How are you paaing it in? If it is a fieldname that the value is set to it should change just like the id field changes for each record.

I want to caution you about your function. Float is not an exact data type, it should never be used for a variable in which you are going to do caluations espcially division and multiplication. Use decimal instead. Since you are dividing, make the decimal value 2 places farther out than the result you want.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[WaveOut]

Thanks for the tip on the float data type, I made the changes. What I'm trying to do is calculate the ratio of Journeymen to Apprentice. The above stored procedure works nicely but for only the craft type that is passed in via the @category parameter. Here's what the data looks like:

craft                  type group      day_hrs    night_hrs
Boilermakers            BM    1           8            8
Boilermakers App #1     BM    2          16           16
Boilermakers App #2     BM    2           8            8
Carpenter               CP    1           8            0
Carpenter App #1        CP    2          16            0
Carpenter App #2        CP    2          16            0

I need to sum all the day_hrs and night_hrs for the type BM (TotalHours) then I need to sum all the day_hrs and night_hrs for the type BM and group number of '2' (AppHours). Then all that is needed to calculate the Ratio is AppHours / Total Hours. Then I do the same thing for the type CP and so on.

Dave

 

[SQLSister]

Are you storing the potential crafts in a table? then join to it and get the category from there or use group by in your select if all the categories are in your current table. Normally you don't want to design a stored proc to run on only one record at a time but on the whole set of data. If you want to do a differnt calulation for each indidividula record from within another stored proc, perhaps you would do better to make this a function.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[WaveOut]

Yes I have a table that contains all possible crafts. What I'm trying to do is create a report on the data you see above and I need to calculate the ratio of Apprentice to Journeyman.

Dave

 

[SQLSister]

If you take what you have inthe stored proc and make it a function that is called within a selct statment, it processes it for every row of the select. I think that is what you want. Look at creating and calling functions in Books on line to see what fuinctions do and how they operate.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[WaveOut]

SQLSister,
Thanks for the suggestion on creating the User Defined Function. I think this is the way I want to go. I've created the following function:

CREATE FUNCTION dbo.ratio (@weekid varchar(75), @category varchar(50))  
RETURNS decimal 
AS  
BEGIN
Declare @TotalHours decimal
Declare @ApprenticeHours decimal
Declare @Ratio decimal

select @TotalHours = isnull(sum(day_hrs),0)+isnull(sum(night_hours),0)+isnull(sum(day_ot_hrs),0)+isnull(sum(night_ot_hrs),0) 
from   Craft2 
where  weeklyuid = @weekid 
       and craft_category = @category

select @ApprenticeHours = isnull(sum(day_hrs),0)+isnull(sum(night_hours),0)+isnull(sum(day_ot_hrs),0)+isnull(sum(night_ot_hrs),0) 
from   Craft2 
where  weeklyuid = @weekid 
       and craft_category = @category
       and craft_group = '3'

select @Ratio = @ApprenticeHours / @TotalHours
Return @Ratio

END

When @Ratio is returned I'm getting the value of 1 rather than a decimal value, lets say .85 (I'm dividing a small number by a big number). How can I get the function to return a decimal value and not round it up.

I'm calling it like so:
dbo.ratio(dbo.craft2.weeklyuid,dbo.craft2.craft_category) as MyRatio



David

 

[WaveOut]

OK I was able to get the function to work! SQLSister, thanks again for the suggestion to use a function to get the results I needed.

Thanks for everyone's help.

Dave