March 7, 2011 at 8:29 am
I need to remove a specific row of data when a certain condition is met.
See the list below.
When Peg is a duplicate, Runout is 1 (true), and the quantity is zero, then I need to remove that duplicate peg row. But, if the quantity for that same runout row is not zero, Then I need to remove the other duplicate peg row.
ShipToNum,PartNum,Qty,Panel,Peg,RunOut
22898,FGNJ126,2,P1,P1.2,0
22898,FGNJ128,3,P1,P1.3,0
22898,FGNJ129,0,P1,P1.4,1 <=== Keep this row when Qty is not zero
22898,FGNJ127,3,P1,P1.4,0 <=== Keep the row when the above Qty is zero and marked RunOut(true)
22898,FGNJ134,3,P2,P2.1,0
22898,FGNJ135,3,P2,P2.2,0
22898,FGNJ136,3,P2,P2.3,0
I just can't seem to get my head around the logic needed for this. That duplicate Peg can happen on more than once. There can be several different duplicates in that table too.
Does anyone have any ideas, or a good stiff nudge in the right direction?
I appreciate any help you can give! Thanks!
--Matt
March 7, 2011 at 9:52 am
So I'm slightly confused. You have marked the two rows as duplicates with separate conditions, but the second one doesn't make sense. Are there other duplicates? In this set, which row is removed?
The first thing to remember is there is no "above" or below" in SQL. The rows are all separete and you can't count on anything being before or after another row. So first you want to separate the rows with logic, not any type of ordering.
Second, think about finding the rows to delete, you want to identify them with logic within the row first, so perhaps a
WHEN Qty = 0
AND Runout = 1
And then use an EXISTS to check for another row that has different logic.
So maybe add an
AND EXISTS ( select id from Shipping s2 where QTY > 0 and s2.id <> s1.id)
Assuming that S1 is your first instance of the table.
March 7, 2011 at 9:57 am
Break it down a bit.
First, select the rows where Runout = 1 and Qty = 0, and use Exists to test if there is another row with the same Peg value.
I'm not sure if there are other criteria, but that would match the main ones you listed.
That should get you half the logic. Add in whatever other criteria you need, and then remove those rows.
Then build the test for the next set of log, and remove those.
Piece by piece, instead of all at once.
Does that help?
- 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
March 7, 2011 at 1:43 pm
It was a little hard to explain. 😉
We have figured out a possible solution. If any of the duplicate "pegs" have a Qty that is less than 0, then we can ignore all duplicates. That would solve this issue. But I haven't quite figured out how to do that. I did run a query that gives me just the duplicates:
SELECT Peg, COUNT(*)
FROM TMP_ProposedInventory
Group By Peg
Having Count(*)> 1
ORDER BY COUNT(*) DESC
Is there any way to run an update on that table if any of the quantities are < 0?
March 8, 2011 at 1:17 am
Hi
What column are you trying to update and what value do you want to set it to? Is the new value a simple calculation of whats already there or will it be based on values in another column?
--update s
set [column] = 'value'
-- select *
from shipping s
where Qty < 0
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply