asked on

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

SOLUTION

HuyBD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

HuyBD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alkabello

ASKER

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

HuyBD

any way for your solution :))
good luck!