Assign value to column depending on ROW_NUMBER()

Question

Post reply

Assign value to column depending on ROW_NUMBER()

ddicecca

Mr or Mrs. 500

Points: 512
More actions
May 20, 2009 at 2:30 pm

#384626

Hey there,
I want to assign a value to a column I made in SQL: '' as "Relative Bonus Amount"
So, if the ROW_NUMBER() returned in 1 and 2, then I want to assign that column in that row (1 and 2) $125.
Does anyone know how I can do this please?
Thank you.
Here is my cide that I have right now:
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(total_stops)/
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 "Completion Rate",
ROW_NUMBER() OVER(ORDER BY SUM(total_stops)/
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) as "Bonus Rank",
'' as "Relative Bonus Amount",
'' as "Individual Bonus Amount",
'' as "Total Bonus Amount",
SUM(total_pieces) as TotalPieces,
'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 '15-MAY-09'
GROUP BY
driver,
emp.lname,
emp.fname

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You could use a CASE statement:

SELECT

CASE

WHEN ROW_NUMBER() OVER (ORDER BY P.DAYSTOMANUFACTURE) 2 AND

ROW_NUMBER() OVER (ORDER BY P.DAYSTOMANUFACTURE) < 5 THEN 100

ELSE 75

END AS ROW,

P.PRODUCTID

FROM

PRODUCTION.PRODUCT P

I'd probably use a CTE to get the row_Numbers then use the case later.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ddicecca Mr or Mrs. 500 Points: 512 More actions · Answer 2

Thank you very much. I will try that after I figure out my percentage problem. :w00t:

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Jack Corbett (5/20/2009)
You could use a CASE statement:
SELECT
CASE
WHEN ROW_NUMBER() OVER (ORDER BY P.DAYSTOMANUFACTURE) 2 AND
ROW_NUMBER() OVER (ORDER BY P.DAYSTOMANUFACTURE) < 5 THEN 100
ELSE 75
END AS ROW,
P.PRODUCTID
FROM
PRODUCTION.PRODUCT P
Man, I just hate double posts. I made the same bloody recommendation on an identical thread by this op.
I'd probably use a CTE to get the row_Numbers then use the case later.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)