May 21, 2009 at 11:47 am
When using this code, I cannot get the percentage by dividing success_pieces by TotalPieces. It's not working. Any help would be greatly appreciated.
Thank you.
select -- convert(varchar, delivery_date, 101) as DeliveryDate,
-- truck_number as TruckNumber,
-- box as Box,
-- truck_store as TruckStore,
-- 'Total' as Total,
emp.fname + ' ' + emp.lname as "Full Name",
driver as emp_cd,
SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +
SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail) as failure_pieces,
SUM(total_pieces) -
(SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +
SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail)) as success_pieces,
SUM(total_pieces) as TotalPieces,
SUM(total_pieces) -
(SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +
SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail)) / SUM(total_pieces) as success_percent,
--ROW_NUMBER() OVER(ORDER BY
-- SUM(crm_fail_3_day) +
-- SUM(service_fail_3_day) +
-- SUM(not_at_home_fail) +
-- SUM(failure_pieces) +
-- SUM(driver_damaged_fail) +
-- SUM(damaged_fail) +
-- SUM(missing_harware_fail) +
-- SUM(missing_parts_fail) +
-- SUM(pref_fail) +
-- SUM(other_fail) +
-- SUM(does_not_fit_fail) +
-- SUM(wrong_pull_fail) +
-- SUM(missing_item_fail) +
-- SUM(truck_late_fail) desc)/SUM(total_stops) as "Bonus Rank",
'' as "Relative Bonus Amount",
'' as "Individual Bonus Amount",
'' as "Total Bonus Amount",
'driver' as DriverType
FROM DELIVERY_EMP_COMMISSION_RATE
INNER JOIN EMP ON EMP.emp_cd = DELIVERY_EMP_COMMISSION_RATE.gers_id
INNER JOIN WAREHOUSE_TRUCK_COMPLETION_STOPS ON DELIVERY_EMP_COMMISSION_RATE.gers_id = WAREHOUSE_TRUCK_COMPLETION_STOPS.driver
WHERE DELIVERY_EMP_COMMISSION_RATE.CHM = 'C' AND
delivery_date between '01-MAY-09' and '01-MAY-09'
and emp_cd = 'ATK'
GROUP BY
driver,
emp.lname,
emp.fname
May 21, 2009 at 12:16 pm
Could you please explain more detailed. Your statement seems to be syntactical correct.
Please provide the table DDL, some sample data and the required result for the sample data.
Have a look to the link in my signature which should help to create the sample data.
Greets
Flo
May 21, 2009 at 12:19 pm
Are the numbers integers? If so, cast/convert to float as part of the calculation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2009 at 1:03 pm
Gsquared,
Could you give me an example please?
May 21, 2009 at 1:06 pm
I think this is the issue you are having...
Select 3/2
Select 3/2.0
Int/Int = Int. You just need to convert one of the columns to a decimal.
May 21, 2009 at 1:25 pm
It works now. I had to convert, as GSquared pointed out. Here is what I did:
select -- convert(varchar, delivery_date, 101) as DeliveryDate,
-- truck_number as TruckNumber,
-- box as Box,
-- truck_store as TruckStore,
-- 'Total' as Total,
emp.fname + ' ' + emp.lname as "Full Name",
driver as emp_cd,
SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +
SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail) as failure_pieces,
SUM(total_pieces) -
(SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +
SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail)) as success_pieces,
SUM(total_pieces) as TotalPieces,
convert(decimal,(SUM(total_pieces) -
(SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +
SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail)))) / SUM(total_pieces) * 100 as success_percent,
--ROW_NUMBER() OVER(ORDER BY
-- SUM(crm_fail_3_day) +
-- SUM(service_fail_3_day) +
-- SUM(not_at_home_fail) +
-- SUM(failure_pieces) +
-- SUM(driver_damaged_fail) +
-- SUM(damaged_fail) +
-- SUM(missing_harware_fail) +
-- SUM(missing_parts_fail) +
-- SUM(pref_fail) +
-- SUM(other_fail) +
-- SUM(does_not_fit_fail) +
-- SUM(wrong_pull_fail) +
-- SUM(missing_item_fail) +
-- SUM(truck_late_fail) desc)/SUM(total_stops) as "Bonus Rank",
'' as "Relative Bonus Amount",
'' as "Individual Bonus Amount",
'' as "Total Bonus Amount",
'driver' as DriverType
FROM DELIVERY_EMP_COMMISSION_RATE
INNER JOIN EMP ON EMP.emp_cd = DELIVERY_EMP_COMMISSION_RATE.gers_id
INNER JOIN WAREHOUSE_TRUCK_COMPLETION_STOPS ON DELIVERY_EMP_COMMISSION_RATE.gers_id = WAREHOUSE_TRUCK_COMPLETION_STOPS.driver
WHERE DELIVERY_EMP_COMMISSION_RATE.CHM = 'C' AND
delivery_date between '01-MAY-09' and '01-MAY-09'
and emp_cd = 'ATK'
GROUP BY
driver,
emp.lname,
emp.fname
May 22, 2009 at 9:32 am
Sorry I didn't give an example, glad you worked it out. I've seen that kind of error lots of times, and it's almost always caused by dividing integers by other integers.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply