June 24, 2008 at 7:54 am
Have a Rebate table which holds 2 types of records.
Code type 1 which is a customer_no and item_no combo
Code Type 3 which is a Customer_type and item_no combo
When an order is entered have a trigger that reads the rebate table and if a match is found write the info that is in the Rebate table to an OrderRebate Table. It is possible there will be code type 1 and 3 that will match a certain customer number. But I want it to check for a code type 1 first, then if code type 1 does not exist then check for code type 3 and use it. So right now if both exist both get written.
ALTER TRIGGER [updateOrderRebatebycusttype] ON [dbo].[oeordlin_sql]
AFTER INSERT
AS
--This insert statement looks for matching records in the Rebate Table
--It looks for customer type and item_no combinations or price Code type 3.
--If a match is found it writes a record to the OrderRebate table.
begin
insert into OrderRebate(ord_type,
ord_no,
line_seq_no,
item_no,
qty_ordered,
qty_to_ship,
cus_no,
a4ID,
AccountTypeCode,
price,
rebate_pct,
cd_tp)
select inserted.ord_type,
inserted.ord_no,
inserted.line_seq_no,
inserted.item_no,
inserted.qty_ordered,
inserted.qty_to_ship,
inserted.cus_no,
inserted.ID,
cicmpy.AccountTypeCode,
rebate.price,
rebate.rebate_pct,
rebate.cd_tp
from inserted INNER JOIN cicmpy ON inserted.cus_no = cicmpy.debcode INNER join rebate on
cicmpy.AccountTypeCode = Rebate.cd_tp_3_cus_type AND inserted.item_no = Rebate.item_no
where cicmpy.AccountTypeCode = Rebate.cd_tp_3_cus_type AND inserted.item_no = Rebate.item_no
end
--This insert statement looks for matching records in the Rebate Table
--It looks for customer number and item_no combinations or price Code type 1.
--If a match is found it writes a record to the OrderRebate table.
begin
insert into OrderRebate(ord_type,
ord_no,
line_seq_no,
item_no,
qty_ordered,
qty_to_ship,
cus_no,
a4ID,
AccountTypeCode,
price,
rebate_pct,
cd_tp)
select inserted.ord_type,
inserted.ord_no,
inserted.line_seq_no,
inserted.item_no,
inserted.qty_ordered,
inserted.qty_to_ship,
inserted.cus_no,
inserted.ID,
cicmpy.AccountTypeCode,
rebate.price,
rebate.rebate_pct,
rebate.cd_tp
from inserted INNER JOIN cicmpy ON inserted.cus_no = cicmpy.debcode INNER join rebate on
inserted.item_no = Rebate.item_no and inserted.cus_no = Rebate.cd_tp_1_cus_no
where inserted.item_no = Rebate.item_no and inserted.cus_no = Rebate.cd_tp_1_cus_no
end
--This update statement calculates the rebate amount and extended rebate amt
-- and updates the orderrebate table with these amounts for price code type 3 records
-- and writes the literal 'Rebate' to freefield3 in the orderrebate table. freefield3 will help
-- determine which records are rebate records that get inserted into the gbkmut table.
begin
update OrderRebate
set rebate_amt = (rebate.price*(.01*rebate.rebate_pct)),
ext_rebate = round((rebate.price*(.01*rebate.rebate_pct)*inserted.qty_to_ship),2),freefield3 = 'Rebate'
from inserted INNER JOIN cicmpy ON inserted.cus_no = cicmpy.debcode INNER join rebate on
cicmpy.AccountTypeCode = Rebate.cd_tp_3_cus_type AND inserted.item_no = Rebate.item_no
where inserted.ord_type = OrderRebate.ord_type and inserted.ord_no = OrderRebate.ord_no and
inserted.line_seq_no = OrderRebate.line_seq_no
end
--This update statement calculates the rebate amount and extended rebate amt
-- and updates the orderrebate table with these amounts for price code type 1 records
-- and writes the literal 'Rebate' to freefield3 in the orderrebate table. freefield3 will help
-- determine which records are rebate records that get inserted into the gbkmut table.
begin
update OrderRebate
set rebate_amt = (rebate.price*(.01*rebate.rebate_pct)),
ext_rebate = Round((rebate.price*(.01*rebate.rebate_pct)*inserted.qty_to_ship),2),
freefield3 = 'Rebate'
from inserted INNER JOIN rebate on
inserted.cus_no = Rebate.cd_tp_1_cus_no AND inserted.item_no = Rebate.item_no
where inserted.ord_type = OrderRebate.ord_type and inserted.ord_no = OrderRebate.ord_no and
inserted.line_seq_no = OrderRebate.line_seq_no
end
June 24, 2008 at 12:19 pm
I'm not sure what you're asking/telling us. I see the sample code and what it does, but are you expecting something from us on this?
- 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
June 24, 2008 at 1:38 pm
Things have changed a bit since that post. I think I've simplified the issue.
I have the following records:
ord_type ord_no Item_no line_seq_no cd_tp
O 55 part 1 1
O 55 part 1 3
O 56 part3 1 3
Out of those records I want to delete the 2nd after its inserted.
I've been trying to group by ord_type,ord_no, item_no, line_seq_no
and where count>1 delete the record where the cd_tp = 3 but can't seem to get it to work. Here is one sample of what I have tried.
delete OrderRebate
FROM OrderRebate
where exists
(
select ord_type, ord_no, item_no, line_seq_no
from OrderRebate
where
inserted.ord_type = ord_type and
inserted.ord_no = item_no and
inserted.ord_no = ord_no and inserted.line_seq_no = line_seq_no and inserted.cd_tp = '3'
group by ord_type, ord_no, item_no, line_seq_no
having count(*)>1
)
June 24, 2008 at 3:10 pm
Try something like this:
;with CTE (Row, Ord_Type, Ord_No, Item_No, Line_Seq_No, CD_TP) as
(select row_number() over
(partition by ord_type, ord_no, item_no, line_seq_no
order by cd_tp),
ord_type, ord_no, item_no, line_seq_no, cd_tp
from OrderRebate)
delete from CTE
where cd_tp = 3
and row > 1
- 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
June 25, 2008 at 7:22 am
didn't get a chance to try your code, but here is the code that did the trick for me.
DELETE t
FROM YourTable t
INNER JOIN
(SELECT ord_type ord_no item_no line_seq_no
FROM YourTable
WHERE cd_tp IN (1,3)
GROUP BY ord_type ord_no item_no line_seq_no
HAVING COUNT(DISTINCT cd_tp)=2)tmp
ON t.ord_type = tmp.ord_type
AND t.ord_no = tmp.ord_no
AND t.item_no = tmp.item_no
AND t.line_seq_no = tmp.line_seq_no
WHERE t.cd_tp=3
June 25, 2008 at 8:59 am
Try both, see which one works faster.
- 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 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply