Challenging ORDER BY "rip order" or "tear order"

Question

Post reply

Challenging ORDER BY "rip order" or "tear order"

Jason Akin

SSChasing Mays

Points: 640
More actions
August 9, 2007 at 2:23 pm

#197242

I 'm working on a payment coupon book. I know the payment amounts and payment due dates. I want to sort the payments in order by page, then by the payment number so that the end user can print 4 per page, but then just rip the stacks three times and lay them one atop another. For example, with 12 payments:
Page 1
1
4
7
10
Page 2
2
5
8
11
Page 3
3
6
9
12
This is pretty straightforward when I calculate the number of pages and the number per page exactly fills the last page. I can
ORDER BY (PaymentNo - 1) % (@Num2Print / @NumPerPage), PaymentNo.
However, I have two problems with this. Due to the frequency or term of the loan, the payments will not be a multiple of 4. For example,
Page 1
1
4
7
10
Page 2
2
5
8
(blank)
Page 3
3
6
9
(blank)
The problem here is that I don't have blank entries to accommodate the spaces I want. PaymentNo 3 ends up printing on Page 2 and messing up the subsequent payments.
I am using a temp table to create my raw data for the payment dates, so I am open to inserting "fake" rows there. The second challenge is to change the starting date of the payments and only print for future dates.
Here is my temp table code. Payment Amount is in another table to be joined later.
--Create temp table for payment dates
CREATE TABLE #tmp(fkAccountID int NOT NULL, DueDate datetime NOT NULL, PaymentNo int)
DECLARE @iCounter int; SET @iCounter = 0
WHILE @iCounter < @PymtCnt
BEGIN
--Loop through payment Dates
INSERT INTO #tmp(fkAccountID, DueDate, PaymentNo)
VALUES (@AccountID, DATEADD(mm, @iCounter, @DateFirstPay), @iCounter)
SET @iCounter = @iCounter + 1
END
DECLARE @DateFrom datetime
IF @ShowPast =1
SET @DateFrom = @DateFirstPay
ELSE
SET @DateFrom = dbo.fnDateOnly(GETDATE())
DECLARE @FirstPrint int, @Num2Print int
SELECT @FirstPrint = MIN(PaymentNo), @Num2Print = COUNT(PaymentNo)
FROM #tmp WHERE DueDate >= @DateFrom
I would really appreciate any help with this. Thanks in Advance!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

AnzioBake SSCarpal Tunnel Points: 4134 More actions · Answer 1

If I understand Correctly you want a max of 4 payments per page

Then The Page to print to is

PageNo = PaymentNo % ( Ceiling( Count(PaymentNo )/4.00 ) )

AnzioBake SSCarpal Tunnel Points: 4134 More actions · Answer 2

MAybe I should explain

Get the number of payments. Count( paymentNo )

Calculate number of pages with 4 Payments per page: Count( paymentNo ) / 4.00

The modulo of the PaymentNo and number of pages is the page to print to

PS: Modulo gives the remainder of int one devided by int two

Jason Akin SSChasing Mays Points: 640 More actions · Answer 3

Thanks for replying. I just finished an inelegant but workable solution. The problem was that I needed some blank placeholders added to my temp table. I added dummy rows at the end to "pad" paymentNo to an even multiple of four. My sort works then and the users have a lot less to collate.