-
Setting a row cell to zero?
I'm trying to isolate duplicate sale numbers and change the revenue dollar amount for just one of each duplicate sale number found to zero.
Anyone know how to do this? (Code and data set below)
Code:
SELECT dis.name AS
district_id,
pat.account_number,
Substr(pat.Sale_number,1,4) as Sale_Num,
pat.sale_date,
revenue_dollar_amount,
(select COUNT(*)
from district_tbl dis,
payroll_accrual_tbl pat
Where pat.district_id = dis.id
AND pat.accrual_status_flag = 'P'
AND '926103' = pat.district_id
AND pat.sale_number = pat.sale_number
HAVING COUNT(*) >= 1)
a
FROM district_tbl dis,
payroll_accrual_tbl pat
Where pat.district_id = dis.id
AND pat.accrual_status_flag = 'P'
AND '926103' = pat.district_id
AND pat.sale_number = pat.sale_number
25291 1478 15-FEB-07 3952.8 4
25291 1478 15-FEB-07 3952.8 4
22485 1479 15-FEB-07 2436.6 4
22485 1479 15-FEB-07 2436.6 4
So one 3952.8 would show 0 and one 2436.6 would show 0
-
nvm...discovered the anwser to my question. If anyone is interested let me know and I can post for you.
thx again.
-
Can you post the answer?
Thanks
-
setting a row cell to zero?
OCP here ya go:
The highlighted portions are the main drivers for this to take place.
In my case I had “Sale_Number” that could occur 2 or more times, so Max(sale_number) would give me one of the two records (this separates the two even though they are the same).
NOTE: using Min(sale_number) would also work of course.
Then, in the “inner select” statement just add in your criteria (FROM, WHERE, etc…)
The case statement at top is where it’s decided to set the value of the duplicate to 0 and the other to the original amount (or whatever you want them to be) based on the COUNT(*) from the inner query. You can add and extend what’s done with other duplicates within the case easily.
Code:
SELECT dis.name AS
dist_id,
pat.account_number,
Substr(pat.Sale_number,1,4) as Sale_Num,
pat.sale_date,
case when inner.sale_count is null or inner.sale_count = 1
then revenue_dollar_amount
else 0
end as revenue_outputFROM acc_tbl pat
LEFT JOIN
(SELECT Substr(pat.Sale_number,1,4) as Sale_Num,
count(*) as sale_count,
max(sale_number) as second_sale_number
FROM pay_acc_tbl pat
Where pat.stat_flag = 'P'
GROUP BY substr(pat.sale_number,1,4)) inner
ON inner.second_sale_number = pat.sale_number
JOIN dist_tbl dis
ON dis.id = pat.dist_id
WHERE pat.stat_flag = 'A'
ORDER by sale_num
It's a nice little piece of code, hope it helps!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|