Get multiple rows equivalent to division on two fields

user_2249971 0
Viewed 65

I have a table that has the following columns:

Date       Quantity RollCount PlanID
20-12-2021 200      2         100
21-12-2021 150      4         101

I want to get the following data:

Date        Quantity  RollNo  PlanID
20-12-2021  100       1       100
20-12-2021  100       2       100
21-12-2021  37.5      1       101
21-12-2021  37.5      2       101
21-12-2021  37.5      3       101
21-12-2021  37.5      4       101

I have tried, but got no solution to achieve that result. Can anyone help me get that?

tsql sql-server
2 Answers

A recursive statement or a statement using numbers table are probably the first two options:

Table:

SELECT *
INTO Data
FROM (VALUES
   (CONVERT(date, '20-12-2021', 105), 200, 2, 100),
   (CONVERT(date, '21-12-2021', 105), 150, 4, 101)
) v (Date, Quantity, RollCount, PlanID)

Statement with recursion:

; WITH rCTE AS (
   SELECT d.Date, d.Quantity, 1 AS RollNo, d.RollCount, d.PlanID
   FROM Data d
   UNION ALL
   SELECT r.Date, r.Quantity, r.RollNo + 1, r.RollCount, r.PlanID
   FROM rCTE r
   WHERE r.RollNo + 1 <= r.RollCount
)   
SELECT Date, (Quantity * 1.0 / RollCount) AS Quantity, RollNo, PlanID
FROM rCTE
ORDER BY PlanID, RollNo
OPTION (MAXRECURSION 0)

Statement using numbers table (with 1000 rows):

; WITH nCTE AS (
   SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Rn
   FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) a (n)
   CROSS APPLY (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) b (n)
   CROSS APPLY (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) c (n)
)
SELECT d.Date, (d.Quantity * 1.0 / d.RollCount) AS Quantity, n.Rn AS RollNo, d.PlanID
FROM Data d
JOIN nCTE n ON d.RollCount >= n.Rn
ORDER BY d.PlanID, n.Rn

Result:

Date       Quantity         RollNo PlanID
2021-12-20 100.000000000000 1     100
2021-12-20 100.000000000000 2     100
2021-12-21 37.500000000000  1     101
2021-12-21 37.500000000000  2     101
2021-12-21 37.500000000000  3     101
2021-12-21 37.500000000000  4     101
user_65780800

Suppose table named tblPlan with columns: PlanDate, Quantity, RollCount, PlanID In Oracle, you can write a query like below:

select distinct *
from
(
  select PlateDate, Quantiy/RollCount as Quantity, level as RollNo, PlanID
  from tblPlan t
  connect by level <= t.RollCount
)
order by PlanID, RollNo

Edit: Using CTE:

with cte (PlanDate, Quantity, RollCount, PlanID , RollNo) as (
  select t.PlanDate, t.Quantity, t.RollCount, t.PlanID , 1 as RollNo
  from tblPlan t
  
  union all
  
  select u.PlanDate, u.Quantity, u.RollCount, u.PlanID , t.RollNo + 1 as RollNo
  from cte t
    inner join tblPlan u on t.PlanID  = u.PlanID  and t.RollNo < u.RollCount
)
select distinct *
from cte
order by PlanID, RollNo
user_31639310
Related