June 19, 2013 at 7:53 am
Hi All
Thanks in advance for any advice.
I need to print address data in blocks of 8 for the labels we are using
The data sets returned could be any number of rows what I want to do is add a column that gives a values like this
Rowsreturns values in new column
1 - 8 1
9 - 162
17 – 243
The new column value would be the page number as we need to do odd even to print on the back of the labels within SSRS 2008 body I would like to do this within the tsql
I have tried ((ROW_NUMBER() OVER (ORDER BY oddpages.id_num)) / 9) + 1 AS pagenumber, but that gives strange results as the first count is correct but the 2nd give one extra
Row 17 should be the start of page 3
Row_IDpage number
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 2
10 2
11 2
12 2
13 2
14 2
15 2
16 2
17 2
18 3
19 3
20 3
21 3
22 3
23 3
24 3
25 3
26 3
27 4
Thanks
Simon
June 19, 2013 at 8:13 am
You're over complicating things. This is just a bit of simple maths 😉
-- CREATE SAMPLE DATA
IF object_id('tempdb..#Table') IS NOT NULL
BEGIN
DROP TABLE #Table;
END;
SELECT TOP 30
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Row_ID
INTO #Table
FROM (SELECT 1
FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
)a(N)
CROSS JOIN (SELECT 1
FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)
)b(N);
-- ACTUAL SOLUTION
SELECT Row_ID, ((Row_ID - 1) / 8) + 1 AS [Page Number]
FROM #Table;
June 19, 2013 at 8:19 am
Pretty sparse on the details but I think that something like this should work:
((ROW_NUMBER() OVER (ORDER BY oddpages.id_num) - 1) / 8) + 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2013 at 8:21 am
Thanks Cadavre that did the trick thank you very much 🙂
June 19, 2013 at 8:22 am
Thanks Sean your solution worked also
June 19, 2013 at 8:23 am
Simon Hardstaff (6/19/2013)
Thanks Sean your solution worked also
It was pretty much exactly the same as Cadavre's. He posted his while I was still typing. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 19, 2013 at 8:23 am
Simon Hardstaff (6/19/2013)
Thanks Cadavre that did the trick thank you very much 🙂
Bear in mind that if your Row_ID is not contiguous, you need to use the ROW_NUMBER solution propose by Sean.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply