Consolidate records

I consider security A to be equivalent to C so I want to consolidate these
positions. Three scenarios:
If an account holds both A and C I want to add the C quantity to the A
position and delete the C position.
If the account holds A but not C then no action.
If the account holds C but not A I want to update the C position to A.
My requirements are such that I need UPDATE and DELETE statement not a
SELECT statement so that my desired results would be produced by SELECT *
FROM #Positions
Any help would be appreciated.
CREATE TABLE [dbo].[#Positions] (
[AccountID] [int] NOT NULL ,
[SecurityID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Quantity] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'A',20)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'B',25)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'C',25)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (2,'A',15)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (2,'B',5)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (3,'B',5)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (3,'C',10)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (4,'C',15)
Expected results
1,A,45
1,B,25
2,A,15
2,B,5
3,A,10
3,B,5
4,A,15Please explain NULL quantity to me; I understand >=0, but not a NULL.
Create a synonym table,then create a VIEW with the base name and totals
and use it.|||Terri
The following UPDATE/DELETE statemenst should help you out:
-- Update SecurityID A with the values for SecurityID C where the AccountID
is the same
UPDATE a
SET a.quantity = a.quantity + b.quantity
FROM #Positions a JOIN #Positions b
ON a.accountid = b.accountid
AND a.securityid = 'A'
AND b.securityid = 'C'
-- Delete any of the SecurityID C rows that were used in the previous update
DELETE b
FROM #Positions a JOIN #Positions b
ON a.accountid = b.accountid
AND a.securityid = 'A'
AND b.securityid = 'C'
-- Update SecurityID C to SecurityID A
UPDATE #Positions
SET securityid = 'A'
WHERE securityid = 'C'
- Peter Ward
WARDY IT Solutions
"Terri" wrote:

> I consider security A to be equivalent to C so I want to consolidate these
> positions. Three scenarios:
> If an account holds both A and C I want to add the C quantity to the A
> position and delete the C position.
> If the account holds A but not C then no action.
> If the account holds C but not A I want to update the C position to A.
> My requirements are such that I need UPDATE and DELETE statement not a
> SELECT statement so that my desired results would be produced by SELECT *
> FROM #Positions
> Any help would be appreciated.
> CREATE TABLE [dbo].[#Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'A',20)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'B',25)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'C',25)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (2,'A',15)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (2,'B',5)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (3,'B',5)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (3,'C',10)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (4,'C',15)
> Expected results
> 1,A,45
> 1,B,25
> 2,A,15
> 2,B,5
> 3,A,10
> 3,B,5
> 4,A,15
>
>sqlsql