May 20, 2014 at 8:38 pm
Hello,
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
Any ideas as to how can I update Table 1 to see desired results?
Regards,
moroformat
May 20, 2014 at 9:25 pm
Nothing doing:
-- set up table
use tempdb;
GO
CREATE TABLE t1 (
PO_Num INT PRIMARY KEY,
DaysToTravel INT,
DaysInWhse INT);
GO
-- add some sample data for folks to play with
INSERT INTO t1(PO_Num, DaysToTravel, DaysInWhse) VALUES
(1,10,20),
(2,5,30),
(3,7,40);
-- now we can write the query...
SELECT t1.PO_Num
, t1.DaysToTravel
, t1.DaysInWhse
, diw.DaysInWhse AS DaysInWhse_Batch
FROM t1
CROSS JOIN
(SELECT DISTINCT DaysInWhse
FROM t1) diw
ORDER BY PO_Num
, DaysToTravel
, t1.DaysInWhse;
For a new guy, nice job setting up the data and expected results. Makes the job of people trying to help a lot easier!
Oh, you wanted to save this stuff to the table? Not really necessary. If you run the SQL statement, it will create the result you described. Is there a reason you need to store the results? If you don't absolutely have to, I wouldn't.
May 20, 2014 at 9:39 pm
Potentially can just do a cross join like the below.
create table #test (PO int, DaysToTravel int, DaysInWarehouse int)
insert into #test VALUES
(1, 10, 20),
(2, 5, 30),
(3, 7, 40)
select b.PO, b.DaysToTravel, b.DaysInWarehouse, a.DaysInWarehouse
from #test a cross join #test b
order by b.PO
May 21, 2014 at 7:40 am
It works !!
Thank you pietlinden. Very elegant solution.
moroformat
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply