Compare Page Revisions
« Older Revision - Back to Page History - Newer Revision »
if object_id(N'dbo.Paginate', N'TF') is not null drop function dbo.Paginate go create function dbo.Paginate ( @inputDoc xml ) returns @t table ( DocumentId varchar(50) ,RowNumber int ,SubLineNum int ,LinesPerPage int ,LinesThisRecord int ,LinesThisPage int ,PageNumber int ,LinesLeftThisPage int ,TotalPages int ) as begin /*===============================================================================================*/ /* declare @inputDoc xml select @inputDoc = '<row> <DocumentId>411223924</DocumentId> <RowNumber>1</RowNumber> <SubLineNum>0</SubLineNum> <LinesPerPage>18</LinesPerPage> <LinesThisRecord>3</LinesThisRecord> </row> <row> <DocumentId>411223924</DocumentId> <RowNumber>2</RowNumber> <SubLineNum>1</SubLineNum> <LinesPerPage>18</LinesPerPage> <LinesThisRecord>3</LinesThisRecord> </row>' */ /*===============================================================================================*/ ; with a as ( select DocumentId = T.c.value('DocumentId[1]', 'varchar(50)') ,RowNumber = T.c.value('RowNumber[1]', 'int') ,SubLineNum = T.c.value('SubLineNum[1]', 'int') ,LinesPerPage = T.c.value('LinesPerPage[1]', 'int') ,LinesThisRecord = T.c.value('LinesThisRecord[1]', 'int') from @inputDoc.nodes('row') T(c) ) /*===============================================================================================*/ ,b as ( /*--- Base Case ---*/ select a.* ,LinesThisPage = LinesThisRecord ,PageNumber = 1 from a where 1=1 and RowNumber = 1 /*--- Recursive Case ---*/ union all select this.* ,LinesThisPage = case -- If LinesThisRecord would put us over the limit, ... when prev.LinesThisPage + this.LinesThisRecord > this.LinesPerPage -- ... then reset the running total for this page; ... then this.LinesThisRecord when this.SubLineNum > 0 then prev.LinesThisPage -- ...otherwise, add to the running total. else this.LinesThisRecord + prev.LinesThisPage end ,PageNumber = prev.PageNumber + case -- If LinesThisRecord would put us over the limit, ... when prev.LinesThisPage + this.LinesThisRecord > this.LinesPerPage then 1 -- ... then start a new page; ... else 0 -- ... otherwise, continue with the current page. end from a this inner join b prev on prev.RowNumber = this.RowNumber - 1 and prev.DocumentId = this.DocumentId ) /*===============================================================================================*/ insert into @t ( DocumentId, RowNumber, SubLineNum, LinesPerPage, LinesThisRecord, LinesThisPage, PageNumber, LinesLeftThisPage, TotalPages ) select * ,LinesLeftThisPage = LinesPerPage - LinesThisPage ,TotalPages = max(PageNumber) over (partition by DocumentId) from b OPTION (MAXRECURSION 1000) return end
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.