Stack Overflow Asked by user3093820 on January 17, 2021
I would like to allocate rows from one table to another using a sort on TopLvlOrd field. The inputs are the [Orders] table and the [Defects] table. I would like to create an SQL that produces [Output]. Even after a bunch of online research I’m not sure how to do this. I’d prefer not to do a cursor, but will go there if necessary. Any ideas? Using SQL Server 2012.
Rules:
(1) Allocate by TopLvlOrd asc,
(2) Allocate one TopLvlOrd row per PegQty
[Orders]
TopLvlOrd IntOrd PegQty
========= ====== ======
67 25 3
120 25 1
111 25 1
16 25 1
127 25 1
127 65 1
127 85 1
[Defects]
DefectID IntOrd TotQty
======== ====== ======
1 25 10
2 25 10
3 25 10
4 25 10
5 25 10
6 25 10
7 25 10
8 25 10
9 25 10
10 25 10
11 65 1
12 85 2
13 85 2
[Output]
DefectID IntOrd TotQty TopLvlOrd
======== ====== ====== =========
1 25 10 16
2 25 10 67
3 25 10 67
4 25 10 67
5 25 10 111
6 25 10 120
7 25 10 127
8 25 10 NULL
9 25 10 NULL
10 25 10 NULL
11 65 1 127
12 85 2 127
13 85 2 NULL
Please another time make another question. Please check this query:
SELECT DefectID, IntOrd,
TotQty, TopLvlOrd, PegQty
FROM
(
SELECT B. DefectID, COALESCE (A.IntOrd, B. IntOrd) IntOrd,
B.TotQty, A. TopLvlOrd, A.PegQty FROM
(
SELECT TopLvlOrd ,IntOrd, ROW_NUMBER () OVER (PARTITION By IntOrd ORDER by
TopLvlOrd) Num, PegQty FROM Orders
) A
FULL JOIN
(
SELECT DefectID , IntOrd ,TotQty, ROW_NUMBER () OVER (PARTITION By IntOrd ORDER by
TotQty) Num FROM Orders
) B
ON A. IntOrd=B.IntOrd AND A.Num=B.Num
)C
JOIN
master.dbo.spt_values Tab
ON Tab.type='P' AND Tab.number<C.PegQty
Correct answer by Olga Romantsova on January 17, 2021
SELECT B. DefectID, COALESCE (A.IntOrd, B. IntOrd) IntOrd,
B.TotQty, A. TopLvlOrd FROM
(
SELECT TopLvlOrd ,IntOrd , ROW_NUMBER () OVER (PARTITION By IntOrd ORDER by TopLvlOrd) Num FROM Orders
) A
FULL JOIN
(
SELECT DefectID , IntOrd ,TotQty, ROW_NUMBER () OVER (PARTITION By IntOrd ORDER by TotQty) Num FROM Orders
) B
ON A. IntOrd=B.IntOrd AND A. Num=B.Num
Answered by Olga Romantsova on January 17, 2021
This answers the original version of the question.
I think you want to join on an implicit sequence number, which you can add using row_number()
:
select d.*, o.*
from (select d.*,
row_number() over (partition by intord order by defectid) as seqnum
from defects d
) d left join
(select o.*,
row_number() over (partition by IntOrd order by TopLvlOrd) as seqnum
from orders o
) o
on d.intord = o.intord and d.seqnum = o.seqnum
Answered by Gordon Linoff on January 17, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP