Replacing a field in one table generated by multiple records of another table

Hi

I have 2 SQL tables with a one to many relationship.

The first table is:  Customer
  CustId     ->Customer Id
  DiscSum  ->Discount Summary

The second table is:  Discounts
  CustId     ->Customer Id
  DiscType ->Discount Type (A single letter)
  DiscPct    ->Discount Percent

Here is some sample data from the Discounts table
 CustId     DiscType     DiscPct
   100          A              10
   100          B              15
   200          B              15
   200          C              15
   200          D              20

What I need to do is generate a summary string of the discounts.
For CustId 100 the string should be A=10, B=15
For CustId 200 the string should be B,C=15, D=20

Ideally, I could make this function work either on the whole Discounts table and replace all Customer discount summary fields,
or by passing the CustId as a parameter and replace the discount summary for only that customer.  (I would use this with a trigger)

I know I could do this with VB & SQL, but I've been impressed by the functionality available in SQL.

Thanks in Advance.
Ed

LVL 2
alkabelloAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuyBDCommented:
try this trigger

CREATE TRIGGER trigger_name
ON Discounts
FOR UPDATE, INSERT
AS
declare @str varchar(200),@repstr varchar(50),@CustId int
select @CustId = CustId from inserted
select @str = DiscType+cast(DiscPct as char(10)) from Discounts where CustId=@CustId
select @repstr = DiscType+cast(DiscPct as char(10)) from inserted
if update(DiscType) or UPDATE(DiscPct)
begin
    update Customer set DiscSum=replace(DiscSum,@str,@repstr) where CustId=@CustId
end
if not exists ( select 1 from deleted )
begin
   if exists (select 1 from Customer where CustId=@CustId)
    begin
       update Customer set DiscSum=DiscSum+@repstr where CustId=@CustId
    end
   else
    begin
       insert into Customer(CustId,DiscSum) values(@CustId,@repstr)
    end  
end
HuyBDCommented:
add delete action

CREATE TRIGGER trigger_name
ON Discounts
FOR INSERT,UPDATE,DELETE
AS
declare @str varchar(200),@repstr varchar(50),@CustId int
if exists( select 1 from inserted ) and exists ( select * from deleted )
      begin
            select @CustId = CustId from inserted
            select @str = DiscType+cast(DiscPct as char(10)) from Discounts where CustId=@CustId
            select @repstr = DiscType+cast(DiscPct as char(10)) from inserted
                update Customer set DiscSum=replace(DiscSum,@str,@repstr) where CustId=@CustId
      end
else if exists ( select 1 from inserted)
      begin
            select @CustId = CustId from inserted
            select @str = DiscType+cast(DiscPct as char(10)) from Discounts where CustId=@CustId
            select @repstr = DiscType+cast(DiscPct as char(10)) from inserted
            if exists (select 1 from Customer where CustId=@CustId)
                begin
                   update Customer set DiscSum=DiscSum+@repstr where CustId=@CustId
                end
            else
                begin
                   insert into Customer(CustId,DiscSum) values(@CustId,@repstr)
                end  
      end
else if exists ( select 1 from deleted)
begin
            select @CustId = CustId from deleted
            select @str = DiscType+cast(DiscPct as char(10)) from deleted
            update Customer set DiscSum=replace(DiscSum,@str,'') where CustId=@CustId
end
LowfatspreadCommented:
i think this is more like what you require...

huybd's case only caters for single row 'updates' of the discount table...

CREATE TRIGGER trigger_name
ON Discounts
FOR INSERT,UPDATE,DELETE
AS

declare @str varchar(200)
declare @repstr varchar(50)
declare @CustId int
declare @oldcustid int
declare @discpct decimal(6,3)
declare @rowid int
declare @maxrows int
set @rowid=1

/*
 determine which customers have significantly changed
*/
Select distinct i.custid as custid
  into #temp
  From Inserted as I
 Where Not Exists (select Custid from Deleted as D
                    Where I.custid=d.custid
                      and I.disctype=d.disctype
                      and I.discpct=d.disctype )

Insert into #temp
Select distinct d.custid as custid
  From Deleted as d
 Where Not Exists (select Custid from Inserted as I
                    Where I.custid=d.custid
                      and I.disctype=d.disctype
                      and I.discpct=d.disctype )

 
   /*
    create table of customers and distinct discounts where there has been a change
   */
     select distinct d.custid,d.discpct,identity(int,1,1) as rowid
       into #temppct
       from (select distinct custid,discpct from discounts) as d
      Inner Join #temp as t
         on d.custid=t.custid
      order by d.custid,d.discpct
     
set @maxrows = @@rowcount
set @str=''

/*
loop through customers and discount pcts
*/

While @rowid<=@maxrows
begin
     Select @custid=custid,@discpct=discpct
          ,@oldcustid = case @rowid
                             when 1 then custid
                             else @oldcustid
                             end
          ,@repstr=''
          ,@rowid=@rowid+1 from #temppct
       where rowid=@rowid

/* update the previous customer */

     If @custid<>@oldcustid
     begin
     Update Customer
        set DiscSum=left(@str,len(@str)-1)
      where CustId=@oldCustId
        and discsum+',' <> @str
     set @str=''
     set @oldcustid=@custid
     end
         
     Select @repstr=@repstr+disctype+','
       from discounts
      where custid=@custid
        and discpct=@discpct
      order by disctype
     Set @str=@str+left(@repstr,Len(@repstr)-1)+' = '+convert(varchar(6),@discpct)+','
     
End

/* update the last customer */

If @maxrows > 0
begin
 Update Customer
        set DiscSum=left(@str,len(@str)-1)
      where CustId=@CustId
        and discsum+',' <> @str
end

Return

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alkabelloAuthor Commented:
Wow that was fast from both of you.  I will test this out this morning.
I'm curious about the performance of this trigger.

Any suggestions of how I would replace all the discount summaries?
I may do this at End-of-Day or End-of-Week since this 'for display purposes only'.
All price calculations still use the Discount table.

p.s.  How do you guys feel about splitting the points?

Ed
HuyBDCommented:
any way for your solution :))
good luck!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.