March 12, 2014 at 8:23 pm
Here I have attached excel sheet. I want to do row wise sum, product and then sub total. Please find my attachment.
Thank you in advance.
March 12, 2014 at 9:08 pm
Is there a reason you can't just post the SQL in the body of your message? Sending people to your attachment is a good way to get no answers.
March 12, 2014 at 9:34 pm
Please have a look.
SWeightingFactor SPartRetailPricePerService LTimePerServiceLaborCostPerHour
0.19 171.00 0.50 92
0.09 120.00 0.80 92
0.27 85.00 0.40 92
Hi ,
Here just I want to write query to find the “TotalWeightedCost “ from the given data.
Eg:
In which,
Total LabourCost = LaborCostPerHour * LTimePerService [ie, 92*.50 =46]
TotalCost = SPartRetailPricePerService + Total LabourCost [171 + 46 = 217]
TotalWeightedCost = sum(SWeightingFactor * (TotalCost )) [(.19 * (171 +46)) + (.09 * (120 + 73.60) + (.27 * (85 + 36.80) Ans: 91.53]
March 12, 2014 at 9:37 pm
SWeightingFactor ||| SPartRetailPricePerService ||| LTimePerService ||| LaborCostPerHour
0.19 ||| 171.00 ||| 0.50 ||| 92
0.09 ||| 120.00 ||| 0.80 ||| 92
0.27 ||| 85.00 ||| 0.40 ||| 92
Hi ,
Here just I want to write query to find the “TotalWeightedCost “ from the given data.
Eg:
In which,
Total LabourCost = LaborCostPerHour * LTimePerService [ie, 92*.50 =46]
TotalCost = SPartRetailPricePerService + Total LabourCost [171 + 46 = 217]
TotalWeightedCost = sum(SWeightingFactor * (SPartRetailPricePerService + LabourCost)) [(.19 * (171 +46)) + (.09 * (120 + 73.60) + (.27 * (85 + 36.80) Ans: 91.53]
March 13, 2014 at 8:27 am
Unless I am missing something it seems that you already wrote the query.
TotalWeightedCost = sum(SWeightingFactor * (SPartRetailPricePerService + LabourCost))
Just put a SELECT in front and a FROM
Select TotalWeightedCost = sum(SWeightingFactor * (SPartRetailPricePerService + LabourCost))
From YourTable
_______________________________________________________________
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply