Hi,

I have a task to create a detail table having only header table.

Can anyone help me ?

DECLARE @size int
declare @tab table
(
	Name varchar(10),
	SizeTotal int,
        DeteailRowsCnt int 
)

INSERT INTO @tab VALUES ('A',8,3),('B',10,9)

declare @tabDetail table
(
	Name varchar(10),
	TotalSize int,
	DetailSize int,
)

INSERT INTO @tabDetail VALUES ('A',8,null),('A',8,null),('A',8,null),
		('B',10,null),('B',10,null),('B',10,null),('B',10,null),('B',10,null),('B',10,null),('B',10,null),('B',10,null),('B',10,null)

as result

id_step	Name	TotalSize	DetailSize
1	A	8	3
2	A	8	3
3	A	8	2
1	B	10	1
2	B	10	1
3	B	10	1
4	B	10	1
5	B	10	1
6	B	10	1
7	B	10	1
8	B	10	1
9	B	10	2

DetailSize is int value.

Hi Vitaliy

one posible solution i can think of

declare @tab table
(
	Name varchar(10),
	SizeTotal int,
    DeteailRowsCnt int 
)
INSERT INTO @tab VALUES ('A',8,3),('B',10,9)
;With Num AS
(select 1 no union all select 1)
,Num1 AS(select row_number()over( order by (select null))rn from num a,num b,num c,num d,num a1,num b1,
num c1,num d1,num a2) -- Number table to replicate items
,Calculation as(select *,Round(SizeTotal/cast(DeteailRowsCnt as float),0) gt From @tab a CROSS JOIN Num1 b where a.DeteailRowsCnt>=b.rn)
select *,case when rn = DeteailRowsCnt then (sizetotal - sum(gt) over(partition by name)) + gt else gt end DetailSize  From Calculation
order by name, rn

Thanks

Saravana Kumar C

Hello Vitaliy,

You can try following SELECT statement with multiple SQL CTE expressions

I used a SQL numbers table function for solution

DECLARE @size int
declare @tab table
(
	Name varchar(10),
	SizeTotal int,
    DeteailRowsCnt int 
)
INSERT INTO @tab VALUES ('A',8,3),('B',10,9)

;With CTE as (
SELECT 
	*,
	(SizeTotal - (t.DeteailRowsCnt * i)) size,
	CASE 
		WHEN (SizeTotal - (t.DeteailRowsCnt * i)) > 0 THEN 'Add' 
		WHEN (SizeTotal - (t.DeteailRowsCnt * i)) = 0 THEN 'Fit' 
		ELSE 'Missing'
	END Action
FROM @tab t
Cross Apply dbo.NumbersTable(1,t.sizeTotal * t.DeteailRowsCnt, 1) n
Where ABS((SizeTotal - (t.DeteailRowsCnt * i))) < t.DeteailRowsCnt
--LEFT JOIN @tabDetail d ON t.Name = d.Name
), CTE2 as (
Select
rn = ROW_NUMBER() OVER (Partition By Name Order By ABS(size)),
*
from CTE
), CTE3 as (
Select
	Name, SizeTotal, DeteailRowsCnt,
	Group1Count = 
	CASE 
		WHEN Action = 'Missing' THEN (DeteailRowsCnt + size)
		WHEN Action = 'Add' THEN size
	END,
	Group1Amount = 
	CASE 
		WHEN Action = 'Missing' THEN i
		WHEN Action = 'Add' THEN (i + 1)
	END,
	Group2Count = 
	CASE 
		WHEN Action = 'Missing' THEN ABS(size) 
		WHEN Action = 'Add' THEN (DeteailRowsCnt - size)
	END,
	Group2Amount = 
	CASE 
		WHEN Action = 'Missing' THEN (i - 1)
		WHEN Action = 'Add' THEN i
	END
from CTE2 
where rn = 1
), CTE4 as (
	select
		Name, SizeTotal, DeteailRowsCnt, Group1Amount Amount
	from CTE3
	Cross Apply dbo.NumbersTable(1,Group1Count, 1) g1
	union all
	select
		Name, SizeTotal, DeteailRowsCnt, Group2Amount Amount
	from CTE3
	Cross Apply dbo.NumbersTable(1,Group2Count, 1) g2
)
select 
	id_step = ROW_NUMBER() OVER (Partition By Name Order By Name),
	Name,
	SizeTotal,
	Amount
FROM CTE4

>> I have a task to create a detail table having only header table. <<

Tables have to have keys, so you failed to poisst a table at all. Unlike punch cards, we would compute the correct current count of details in a VIEW. Back in those days, we had to materialize values and put holes in cards like you want to do.


If you read ISO-11179 or any book on data modeling, you would not have a gneric name in the schema. And you will ewant to look the term tibble: as a design flaw (putting tbl-, tab, etc. in a table name). 


CREATE TABLE Orders
(client_name VARCHAR(10) NOT NULL PRIMARY KEY, 
 order_size INTEGER NOT NULL
       CHECK (order_size > 0));

INSERT INTO Orders VALUES ('A', 8), ('B', 10);

The details table has to have DRI back to the headers. That is what the 'R' in RDBMS means. But your attempt is not even a table and cannot ever be a table. You have duplicate rows! Like a deck of 1950's punch cards.

CREATE TABLE Order_Details
(client_name VARCHAR(10) NOT NULL 
  REFERENCES Orders(client_name),
 item_nbr INTEGER NOT NULL,
 PRIMARY KEY (client_name, item_nbr),
 detail_size INTEGER NOT NULL);

INSERT INTO Order_Details
VALUES ('A',1),('A',2),('A',3),
       ('B',1),('B',2),('B',3),
       ('B',4),('B',5),('B',6),
       ('B',7),('B',8),('B',9);

My guess is that this is what you are attemtping; a count of order items agaoinsr the total desired order. 

CREATE VIEW Order_Fulfilment
AS
SELECT O.client_name, O.order_size, 
       COUNT (D.item_nbr) AS fulfilment_cnt
  FROM Orders AS O, Order_Details AS D
 WHERE O.client_name = D.client_name 
 GROUP BY O.client_name, O.order_size ;