SQL Recursive query to generate output

Question

Hello Expert , I am trying to generate one output as:
Two different Tables:

Table1:

Cat1, Vol,Rank

Cat1, 1, 1

Cat1, 4, 2

Cat1, 6, 3

Table2:

Rank, Vol_Threshold, Partition

1, 21, 1

2, 27, 2

3, 34, 3

Would like to generate output:
if running first table vol added to Second table Vol_Threshold and it is less then or equal to next Vol_Threshold and greater then previous threshhold then assign same partition from second table else check with next and assign next partition as:
Cat1, 1, 21+1 = 22 < 27, 1

Cat1, 4, 21+1+4 = 26 < 27, 1

Cat1, 6, 21+1+4+6 = 32 > 27 and < 34 , 2

Answer 1

Hi @Avanish Tomar

Try this query:

;WITH CTE1 AS
(
 SELECT *,SUM(vol)OVER(PARTITION BY cat1 ORDER BY [rank] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM_Total 
 FROM #table1
),CTE2 AS
(
 SELECT *,Vol_threshold-21  AS GAP1,LEAD(Vol_threshold)OVER(ORDER BY Rank)-21 AS GAP2 
 FROM #table2
)
SELECT C1.cat1,C1.vol,C2.Partition
FROM CTE1 C1 JOIN CTE2 C2 ON C1.SUM_Total >= GAP1 AND C1.SUM_Total < GAP2

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

I am not sure that I understand the logic exactly, but maybe this can help you in the right direction:

CREATE TABLE #table1 (cat1   varchar(20),
                      vol    int,
                      rank   int)
INSERT #table1(cat1, vol, rank)
   VALUES('Cat1', 1, 1),
         ('Cat1', 4, 2),
         ('Cat1', 6, 3),
         ('Cat1', 16, 4)
CREATE TABLE #table2 (Rank   int,
                      Vol_threshold int,
                      Partition     int)
INSERT #table2(Rank, Vol_threshold, Partition)
   VALUES(1, 21, 1),
         (2, 27, 2),
         (3, 34, 3)
; WITH runsum AS (
   SELECT t1.cat1, (SELECT t2.Vol_threshold FROM #table2 t2 WHERE t2.Rank = 1) +
                   SUM(t1.vol) OVER(PARTITION BY t1.cat1 
                                    ORDER BY t1.rank 
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runsum
   FROM   #table1 t1
)
SELECT r.cat1, r.runsum, t2.Partition
FROM   runsum r
OUTER  APPLY (SELECT TOP(1) t2.Partition
              FROM   #table2 t2
              WHERE  t2.Vol_threshold <= r.runsum
              ORDER BY t2.Partition DESC) t2
go
DROP TABLE #table1, #table2