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

Considering only first arrival

I am have a report that compiles response times for first responders which
works fine. But what I need is to be able to only consider the responding
units that get to the scene first. Example. Currently if 2 units are
dispatched and one gets on scene in 4 mins and the other in 6 mins, our
response time is 5 mins, which is not true since we were there in 4 mins. In
other words, I need to be able to ignore the 2nd, 3rd etc arriving units.
The reportNumber is the key.
ReportNumber, Unit_ID, Arrival_Time
Thanks.Cant imaging what you mean without DDL or /and sample data.
Jens.
http://www.sqlserver2005.de
--
"Dave S." <davidstedman@.colliergov.net> schrieb im Newsbeitrag
news:OlS65SQQFHA.2580@.TK2MSFTNGP10.phx.gbl...
>I am have a report that compiles response times for first responders which
> works fine. But what I need is to be able to only consider the responding
> units that get to the scene first. Example. Currently if 2 units are
> dispatched and one gets on scene in 4 mins and the other in 6 mins, our
> response time is 5 mins, which is not true since we were there in 4 mins.
> In
> other words, I need to be able to ignore the 2nd, 3rd etc arriving units.
> The reportNumber is the key.
> ReportNumber, Unit_ID, Arrival_Time
> Thanks.
>|||Try,
select ReportNumber, Unit_ID, Arrival_Time
from table1
where Arrival_Time = (select min(a.Arrival_Time) from table1 as a where
a.ReportNumber = table1.ReportNumber)
AMB
"Dave S." wrote:

> I am have a report that compiles response times for first responders which
> works fine. But what I need is to be able to only consider the responding
> units that get to the scene first. Example. Currently if 2 units are
> dispatched and one gets on scene in 4 mins and the other in 6 mins, our
> response time is 5 mins, which is not true since we were there in 4 mins.
In
> other words, I need to be able to ignore the 2nd, 3rd etc arriving units.
> The reportNumber is the key.
> ReportNumber, Unit_ID, Arrival_Time
> Thanks.
>
>|||This sorta works except that it appears to not return records where only one
unit responded, which is most of the time..:(
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5BCF21B6-B932-4807-B96E-3FA61A36420D@.microsoft.com...
> Try,
> select ReportNumber, Unit_ID, Arrival_Time
> from table1
> where Arrival_Time = (select min(a.Arrival_Time) from table1 as a where
> a.ReportNumber = table1.ReportNumber)
>
> AMB
> "Dave S." wrote:
>
which
responding
mins. In
units.|||On Fri, 15 Apr 2005 09:22:40 -0400, Dave S. wrote:

>This sorta works except that it appears to not return records where only on
e
>unit responded, which is most of the time..:(
Hi Dave,
Strange. I used the following script to test Alejandro's suggestion and
it worked for me. Could you post a script to show me under what
circumstances you don't get the rows you expect?
-- Make the table
CREATE TABLE table1
(ReportNumber int NOT NULL,
Unit_ID int NOT NULL,
Arrival_Time datetime NOT NULL,
PRIMARY KEY (ReportNumber, Unit_ID) -- just guessing
)
go
-- Put in some data.
-- Report 1 has two rows; reports 2 and 3 have one row each.
INSERT INTO table1 (ReportNumber, Unit_ID, Arrival_Time)
SELECT 1, 1, dateadd(minute, -3, getdate())
UNION ALL
SELECT 1, 2, dateadd(minute, -2, getdate())
UNION ALL
SELECT 2, 1, dateadd(minute, -1, getdate())
UNION ALL
SELECT 3, 2, dateadd(minute, -4, getdate())
go
-- Alejandro's code - returns one row for each report
select ReportNumber, Unit_ID, Arrival_Time
from table1
where Arrival_Time = (select min(a.Arrival_Time) from table1 as a where
a.ReportNumber = table1.ReportNumber)
go
-- Done - clean up my test DB.
DROP TABLE table1
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||This only returns a single record. Is it supposed to work that way?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5BCF21B6-B932-4807-B96E-3FA61A36420D@.microsoft.com...
> Try,
> select ReportNumber, Unit_ID, Arrival_Time
> from table1
> where Arrival_Time = (select min(a.Arrival_Time) from table1 as a where
> a.ReportNumber = table1.ReportNumber)
>
> AMB
> "Dave S." wrote:
>
which
responding
mins. In
units.sqlsql