Page History: Paginate Function

Compare Page Revisions

1 0 Current 1 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Tue, Aug 10, 2010, 9:51 AM

SQL Code¶

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

Sample Usage¶

select
     DocumentId         = OrderNum
    ,RowNumber          = row_number() over (partition by OrderNum
                                            order by RowGroupSort, GroupRowNumber, SubLineNum)

    ,SubLineNum

    ,LinesPerPage       = @LinesPerPage - case when Language = 'FRENCH' then 1 else 0 end

    ,LinesThisRecord    = case 
                            when RowGroup > @RowGroup_LineItem then 1 
                            else max(SubLineNum) over (partition by OrderNum, LineNum) + 1
                            end
from
    a4

for xml 
    path('row')