Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Warsha
Starting Member
4 Posts |
 Posted - 2015-03-18 : 15:23:59
|
Hi all,
I have a query that gives me info from a sales table.It is basically a range of serials sold per invoice.
eg.
name date description beginserial endserial qty
apple store 18-Mar-15 retail bags 1234 1237 4
The firts row is the column names.
Now i want instead of just one row of data 4 rows of data, like
apple store 18-Mar-15 retail bags 1234
apple store 18-Mar-15 retail bags 1235
apple store 18-Mar-15 retail bags 1236
apple store 18-Mar-15 retail bags 1237
The number of rows is 4 because endserial minus beginserial plus 1 is 4. That is the way to calculate the number of rows of data.
Is this possible?
I will really appreciate if anyone can help me.
Regards,
Randjana |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-03-19 : 02:11:39
|
[code]
;WITH numList
AS ( SELECT 0 as num
UNION ALL
SELECT num + 1
FROM numList
WHERE num < 50)
,SampleCTE
AS(SELECT 'apple store' AS name,'18-Mar-15'AS [date]
,'retail bags' AS description, 1234 AS beginserial,1237 AS endserial,4 AS qty)
SELECT
Name , Date , description
,beginserial + num as Serial
FROM SampleCTE
CROSS JOIN numList
WHERE
num<qty
[/code]
and the result set :
[code]
Name Date description Serial
apple store 18-Mar-15 retail bags 1234
apple store 18-Mar-15 retail bags 1235
apple store 18-Mar-15 retail bags 1236
apple store 18-Mar-15 retail bags 1237
[/code]
sabinWeb MCP |
 |
|
|
Warsha
Starting Member
4 Posts |
Posted - 2015-03-20 : 10:09:38
|
Hi stepson,
the code works as i want, but it doesn't work when the qty is greater then 100. Why is it like that?
even when I do "WHERE num < 300".
I get the following error:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
please help.
Regards,
Warsha |
|
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-03-20 : 11:03:22
|
add this piece of code OPTION ( MaxRecursion 0) after where
sabinWeb MCP |
|
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-03-20 : 11:03:42
|
[code]
;WITH numList
AS ( SELECT 0 as num
UNION ALL
SELECT num + 1
FROM numList
WHERE num < 400)
,SampleCTE
AS(SELECT 'apple store' AS name,'18-Mar-15'AS [date]
,'retail bags' AS description, 1234 AS beginserial,1237 AS endserial,4 AS qty)
SELECT
Name , Date , description
,beginserial + num as Serial
FROM SampleCTE
CROSS JOIN numList
WHERE
num<qty
OPTION ( MaxRecursion 0)
[/code]
sabinWeb MCP |
|
|
|
|
|
|
populate rows of data with input from a column