This page is a Draft. Its content is not complete and might contain errors.

with MyData as (
    -- Your SQL goes here
    )
/*===============================================================================================*/
,a as (
    select
        *
        ,LinesPerPage       = @LinesPerPage
        ,RowNumber          = row_number() over (partition by OrderNumber order by LineNumber)
        ,LinesThisRecord    = 1 + case when Qty = 0 then 0 else 1 end
    from
        MyData
)
/*===============================================================================================*/
,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      = this.LinesThisRecord + case 
                                -- If LinesThisRecord would put us over the limit, ...
                                when prev.LinesThisPage + this.LinesThisRecord > this.LinesPerPage
                                    then 0 -- ... then reset the running total for this page; ...
                                else prev.LinesThisPage  -- ...otherwise, add to the running total.
                                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.OrderNumber = this.OrderNumber
    )
/*===============================================================================================*/
select
     *
    ,LinesLeftThisPage      = LinesPerPage - LinesThisPage
    ,TotalPages             = max(PageNumber) over (partition by OrderNum)
from
    b
OPTION (MAXRECURSION 1000)