Update using two different rows using join
-
I am working with two tables (please see sample code below).
TableA has following rows:
For item 1 revenue = 1000.35 and rundate is 2019-09-01 (run month = 9) and shipmentdate is 2019-10-01 (month=10).
I would like to update the revenue field based by joining to second table (season_rate) on itemnumber using following criteria:
I will use item 1 as an example:
for following condition:
tableA.item = season_rate.item
and tableA.rundate month = season_rate. rundate month
set tableA.revenue = rate (tableA.shipment_date month = season_rate. season_month) / rate(tableA.rundate month = season_rate. rundate month)
here is a worked out example for item 1:
revenue = 1000.35,
shipment_date = '2019-10-01' so month = 10
run_date = '2019-09-01' so month = 9
in season_rate table for item 1:
for month 9 rate is 4.25
for month 10 rate is 2.25
so revenue = revenue * (4.25/2.25)
how can I do this?
thanks
sample code below:
drop table tableA;
create table tableA (
itemnumber int,
revenue float,
shipmentdate date,
rundate date)
insert into tableA values (1, 1000.35, '2019-10-01', '2019-09-01')
insert into tableA values (2, 2000.45, '2019-11-01', '2019-09-01')
insert into tableA values (3, 3000.55, '2019-10-01', '2019-09-01')
insert into tableA values (4, 4000.66, '2019-11-01', '2019-09-01')
select * from tableA
drop table season_rate
create table season_rate (
itemnumber int,
rate float,
season_month int,
rundate date)
insert into season_rate values (1, 1.25, 1, '2019-09-01')
insert into season_rate values (1, 1.05, 5, '2019-09-01')
insert into season_rate values (1, 1.65, 6, '2019-09-01')
insert into season_rate values (1, 1.85, 10, '2019-09-01')
insert into season_rate values (1, 2.25, 11, '2019-09-01')
insert into season_rate values (1, 4.25, 9, '2019-09-01')
insert into season_rate values (2, 1.25, 1, '2019-09-01')
insert into season_rate values (2, 1.05, 5, '2019-09-01')
insert into season_rate values (2, 1.65, 6, '2019-09-01')
insert into season_rate values (2, 1.85, 10, '2019-09-01')
insert into season_rate values (2, 2.25, 11, '2019-09-01')
insert into season_rate values (2, 4.25, 9, '2019-09-01')
insert into season_rate values (3, 1.25, 1, '2019-09-01')
insert into season_rate values (3, 1.05, 5, '2019-09-01')
insert into season_rate values (3, 1.65, 6, '2019-09-01')
insert into season_rate values (3, 1.85, 10, '2019-09-01')
insert into season_rate values (3, 2.25, 11, '2019-09-01')
insert into season_rate values (3, 4.25, 9, '2019-09-01')
insert into season_rate values (4, 1.25, 1, '2019-09-01')
insert into season_rate values (4, 1.05, 5, '2019-09-01')
insert into season_rate values (4, 1.65, 6, '2019-09-01')
insert into season_rate values (4, 1.85, 10, '2019-09-01')
insert into season_rate values (4, 2.25, 11, '2019-09-01')
insert into season_rate values (4, 4.25, 9, '2019-09-01')
select * from season_rate -
update a
set
revenue=revenue*isnull((tsr_run.rate/tsr_ship.rate),1)
from
test_tableA a
left join
test_season_rate tsr_ship on a.itemnumber=tsr_ship.itemnumber
and datepart(m, a.shipmentdate)=tsr_ship.season_month
left join
test_season_rate tsr_run on a.itemnumber=tsr_run.itemnumber
and datepart(m, a.rundate)=tsr_run.season_month;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
>> Table_A has following rows: <<
Why do you think that's a good name for a table? Do you find it clear and precise? Just offhand it looks like shipments, not the A . Also including the metadata "table_" in a data element name is called a tibble and it's a common design flaw with newbies.
For item 1 revenue = 1000.35 and run_date is 2019-09-01 (run month = 9) and shipment_date is 2019-10-01 (month=10).
>> I would like to update the revenue field [sic: columns are not fields] based by joining to the second table (Season_Rates) on item_nbr using following criteria: <<
A month is a unit of temporal measure. But that's not the way you're using it
I think you want to use a MERGE statement, not a join.
I will use item 1 as an example:
table2
for the following condition:
Shipments.item_nbr = Season_Rates.item_nbr
and Shipments.run_datemonth = Season_Rates. run_datemonth
SET Shipments.revenue
= rate (Shipments.shipment_date month = Season_Rates.season_month) / rate(Shipments.run_datemonth = Season_Rates. run_datemonth)
here is a worked-out example for item 1:
revenue = 1000.35,
shipment_date = '2019-10-01' so month = 10
run_date = '2019-09-01' so month = 9
in Season_Rates table for item 1:
for month 9 rate is 4.25
for month 10 rate is 2.25
so revenue = revenue * (4.25/2.25)
how can I do this?
thanks
sample code below:
You might want to read a book on SQL and pay attention to the first couple of chapters. By definition, a table must have a key but what you posted can never have a key because everything can be NULL. Basically you're still working with punchcards in your head. Next, your item number should be a character string because it's on the nominal scale naming some product. Even better it should be an industry-standard identifier like a UPC code. Let's pretend it's okay to do computations on it but your revenue is going to be money and it's never never never float. You can go to jail for this read the generally accepted accounting practices, EU regulations and just about anything else in the commercial environment.
CREATE TABLE Shipments
(item_nbr CHAR(10) NOT NULL PRIMARY KEY,
something_revenue DECIMAL(8,2) NOT NULL,
shipment_date DATE NOT NULL,
run_date DATE NOT NULL);
INSERT INTO Shipments
VALUES
('001', 1000.35, '2019-10-01', '2019-09-01'),
('002', 2000.45, '2019-11-01', '2019-09-01'),
('003', 3000.55, '2019-10-01', '2019-09-01'),
('004', 4000.66, '2019-11-01', '2019-09-01') ;
A month is a temporal unit of measure. Specifically it's what the standards call interval data type in SQL. The way we can show this in SQL Server (which has not implemented intervals) is with (something_begin_date. something_end_date) pairs. We would never use your integer. Why don't you take the time to download Rick Snodgrass's book on temporal queries in SQL. It's available as a free PDF from the University of Arizona
CREATE TABLE Season_Rates
(item_nbr CHAR(10) NOT NULL,
season_rate DECIMAL (8,2) NOT NULL,
season_begin_date DATE NOT NULL,
season_end_date DATE NOT NULL,
CHECK (season_begin_date < season_end_date)
);
Since I not being paid for this :-), I'll let you readjust your rates into the proper time slots. Your example seems to have some problems with that rate because it is not a function, but you tried to use it that way rate(). I'll leave it to you to figure out how to write the queries you need.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply