?
Solved

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

Posted on 2006-04-23
5
Medium Priority
?
275 Views
Last Modified: 2008-02-26
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

0
Comment
Question by:alkabello
  • 3
5 Comments
 
LVL 17

Assisted Solution

by:HuyBD
HuyBD earned 300 total points
ID: 16522207
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
 
LVL 17

Assisted Solution

by:HuyBD
HuyBD earned 300 total points
ID: 16522235
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 450 total points
ID: 16523419
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
 
LVL 2

Author Comment

by:alkabello
ID: 16524622
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
 
LVL 17

Expert Comment

by:HuyBD
ID: 16530913
any way for your solution :))
good luck!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question