TransWikia.com

SQL to allocate rows from one table to another

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

3 Answers

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP