November 19, 2019 at 1:07 pm
I need help with UPDATE Query. I have this data in tables and want to update weighted average in table tblData3 - (TValue * BPrice) .... / SUM(TValue)
create table tblData1
(
IDate date,
PID varchar(20),
SID varchar(20),
TValue float
)
insert into tblData1 values ('10/15/2019','4567','ABC',4567.34)
insert into tblData1 values ('10/15/2019','4567','ABC',5678.34)
insert into tblData1 values ('10/15/2019','3456','ABC',3494.56)
insert into tblData1 values ('10/15/2019','3456','TMZ',2323.98)
insert into tblData1 values ('10/15/2019','3456','TMZ',234.78)
insert into tblData1 values ('10/15/2019','3456','TMZ',345.24)
insert into tblData1 values ('10/15/2019','1358','PQR',2896.24)
insert into tblData1 values ('10/15/2019','1358','PQR',2345.23)
insert into tblData1 values ('10/15/2019','1358','XYZ',2332.23)
insert into tblData1 values ('10/15/2019','1358','ABC',3333.33)
create table tblData2
(
PID varchar(20),
SID varchar(20),
BPrice float,
)
insert into tblData2 Values ('4567','ABC',103.24)
insert into tblData2 values ('3456','ABC',202.24)
insert into tblData2 values ('3456','TMZ',56.24)
insert into tblData2 values ('1358','PQR',50.25)
insert into tblData2 values ('1358','XYZ',235.24)
insert into tblData2 values ('1358','ABC',238.24)
create table tblData3
(
PID varchar(20),
SID varchar(20),
BWAvg float
)
November 19, 2019 at 1:31 pm
What have you tried so far?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 19, 2019 at 1:34 pm
You mentioned an UPDATE query, yet table 3 contains no data (and therefore no amount of UPDATE queries will help). Are you sure that your test data is up to scratch?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 19, 2019 at 1:52 pm
It would be helpful if you could show what results are expected.
There is no data in tblData3 so I don't see how you can do an update?
November 19, 2019 at 2:44 pm
Don't know how come I missed that. The BWAvg will be NULL when the data is inserted and will be updated later.
insert into tblData3 Values ('4567','ABC',NULL)
insert into tblData3 values ('3456','ABC',NULL)
insert into tblData3 values ('3456','TMZ',NULL)
insert into tblData3 values ('1358','PQR',NULL)
insert into tblData3 values ('1358','XYZ',NULL)
insert into tblData3 values ('1358','ABC',NULL)
November 19, 2019 at 3:36 pm
The weighted average price is still the price. Are you looking for average volumes? Unique constraints on (SID, PID) to tables t2 and t3 are valid for your situation? Assuming yes to the questions here's average volumes (copy, paste, run):
drop table if exists #tblData1;
go
create table #tblData1
(
IDate date,
PID varchar(20),
SID varchar(20),
TValue float
);
go
insert into #tblData1 values ('10/15/2019','4567','ABC',4567.34)
insert into #tblData1 values ('10/15/2019','4567','ABC',5678.34)
insert into #tblData1 values ('10/15/2019','3456','ABC',3494.56)
insert into #tblData1 values ('10/15/2019','3456','TMZ',2323.98)
insert into #tblData1 values ('10/15/2019','3456','TMZ',234.78)
insert into #tblData1 values ('10/15/2019','3456','TMZ',345.24)
insert into #tblData1 values ('10/15/2019','1358','PQR',2896.24)
insert into #tblData1 values ('10/15/2019','1358','PQR',2345.23)
insert into #tblData1 values ('10/15/2019','1358','XYZ',2332.23)
insert into #tblData1 values ('10/15/2019','1358','ABC',3333.33)
drop table if exists #tblData2;
go
create table #tblData2
(
PID varchar(20),
SID varchar(20),
BPrice float,
constraint unq_tblData2_pid_sid unique(PID, SID)
);
go
insert into #tblData2 Values ('4567','ABC',103.24)
insert into #tblData2 values ('3456','ABC',202.24)
insert into #tblData2 values ('3456','TMZ',56.24)
insert into #tblData2 values ('1358','PQR',50.25)
insert into #tblData2 values ('1358','XYZ',235.24)
insert into #tblData2 values ('1358','ABC',238.24)
drop table if exists #tblData3;
go
create table #tblData3
(
PID varchar(20),
SID varchar(20),
BWAvg float,
constraint unq_tblData3_pid_sid unique(PID, SID)
);
go
insert into #tblData3 Values ('4567','ABC',NULL)
insert into #tblData3 values ('3456','ABC',NULL)
insert into #tblData3 values ('3456','TMZ',NULL)
insert into #tblData3 values ('1358','PQR',NULL)
insert into #tblData3 values ('1358','XYZ',NULL)
insert into #tblData3 values ('1358','ABC',NULL)
go
with
volumes_cte as (
select
t1.PID,
T1.SID,
T1.TValue,
isnull(t1.TValue/T2.BPrice, 0) volume,
t2.BPrice price
from
#tblData1 t1
join
#tblData2 t2 on t1.SID=t2.SID
and t1.PID=t2.PID),
avg_cte as (
select
PID,
SID,
avg(volume) avg_volume
from
volumes_cte
group by
PID,
SID)
update t3
set
BWAvg=ac.avg_volume
from
#tblData3 t3
join
avg_cte ac on t3.PID=ac.PID
and t3.SID=ac.SID;
select * from #tblData3;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 19, 2019 at 3:50 pm
Don't know how come I missed that. The BWAvg will be NULL when the data is inserted and will be updated later.
insert into tblData3 Values ('4567','ABC',NULL)
insert into tblData3 values ('3456','ABC',NULL)
insert into tblData3 values ('3456','TMZ',NULL)
insert into tblData3 values ('1358','PQR',NULL)
insert into tblData3 values ('1358','XYZ',NULL)
insert into tblData3 values ('1358','ABC',NULL)
So, for example, for
PID = 4567
SID = ABC
TValues = 4567.34, 5678.34, 3494.56
BPrice = 103.2
What value would you expect BWAvg to be for the data above?
November 19, 2019 at 3:51 pm
The two CTE's could be consolidated into one.
with
avg_cte as (
select
t1.PID,
t1.SID,
avg(isnull(t1.TValue/T2.BPrice, 0)) avg_volume
from
#tblData1 t1
join
#tblData2 t2 on t1.SID=t2.SID
and t1.PID=t2.PID
group by
t1.PID,
t1.SID)
update t3
set
BWAvg=ac.avg_volume
from
#tblData3 t3
join
avg_cte ac on t3.PID=ac.PID
and t3.SID=ac.SID;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply