I have a table as below fields:

1. PO_no (varchar)
2. First_serial (int)
3. Last_serial (int)

Sample Data as below:

PO_no   First_serial   Last_serial

4001     100             105
4002     110             112

PO_no     Serial

4001       100
4001       101
4001       102
4001       103
4001       104
4001       105

4002       110
4002       111
4002       112

quote:

---

Originally posted by khtan

MCQ :

1. you want to create a new table automatically ?

2. you want to automatic create records in the 2nd table when records are inserted into 1st table ?

3. none of the above ?

---

KH
[spoiler]Time is always against us[/spoiler]

---

; with cte as
(
	select	po_no, serial = first_serial, last_serial
	from	tbl1

	union all

	select	po_no, serial = serial + 1, last_serial
	from	cte
	where	serial + 1 <= last_serial
)
select	po_no, serial
from	cte
order by po_no, serial

; with cte as
(
	select	po_no, serial = first_serial, last_serial
	from	inserted

	union all

	select	po_no, serial = serial + 1, last_serial
	from	cte
	where	serial + 1 <= last_serial
)
insert into tbl2 (po_no, serial)
select	po_no, serial
from	cte

quote:

---

Originally posted by khtan

the following query will create the records into 2nd table

; with cte as
(
	select	po_no, serial = first_serial, last_serial
	from	tbl1

	union all

	select	po_no, serial = serial + 1, last_serial
	from	cte
	where	serial + 1 <= last_serial
)
select	po_no, serial
from	cte
order by po_no, serial

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by khtan

what is the max no of serial you might have ?

do you have bad data in there ?

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by khtan

add this to the end of query

option (MAXRECURSION 10000)

---

KH
[spoiler]Time is always against us[/spoiler]

---

; with tblASmax as
(
	select	serialnumber, runno,process,side, result
	from	(
			select	serialnumber,runno,process,result,side, row_no = row_number() over (partition by process,side,serialnumber order by runno desc)
			from	tblAS

			union all

			select	serialnumber,runlocal,process,result,NULL, row_no = row_number() over (partition by process,serialnumber order by runlocal desc)
			from	tblFA
		) m  	where	m.row_no	= 1 and process = '12'
)
select k.productiono,k.qty,serialnumber,result 
from(
select po.productiono,qty,partcode,serialnumber,result

from	tblPO po
	left join tblASmax rs	on	po.firstsr	<= rs.serialnumber	and	po.lastsr	>= rs.serialnumber 
group by po.productiono,qty,partcode,serialnumber,result)k     where k.productiono='5009073'

; with cte as
(
	select	productiono, serial = firstsr, lastsr
	from	tblpo where productiono = '5009073'

	union all

	select	productiono, serial = serial + 1, lastsr
	from	cte
	where	serial + 1 <= lastsr
)
select	productiono, serial
from	cte 
order by productiono, serial
option (maxrecursion 1000);

quote:

---

Originally posted by khtan

add this to the end of query

option (MAXRECURSION 10000)

---

DECLARE @table table (
	PO_no varchar(50),
	First_serial int,
	Last_serial int)
	
INSERT INTO @table
SELECT '4001', 100, 105 UNION ALL
SELECT '4002', 110, 112

SELECT 
	a.PO_no,
	Serial = b.number
FROM @table a
	INNER JOIN master..spt_values b
		ON b.number BETWEEN a.First_serial AND a.Last_serial
WHERE b.type = 'p'

quote:

---

Originally posted by Lumbago

quote:

---

Originally posted by khtan

add this to the end of query

option (MAXRECURSION 10000)

---

khtan: this is a horrible advice! Especially when recursion is completely unnecessary.

->

DECLARE @table table (
	PO_no varchar(50),
	First_serial int,
	Last_serial int)
	
INSERT INTO @table
SELECT '4001', 100, 105 UNION ALL
SELECT '4002', 110, 112

SELECT 
	a.PO_no,
	Serial = b.number
FROM @table a
	INNER JOIN master..spt_values b
		ON b.number BETWEEN a.First_serial AND a.Last_serial
WHERE b.type = 'p'

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

---