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?
 
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
0
 
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
0
 
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
0
 
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
0
 
HuyBDCommented:
any way for your solution :))
good luck!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.