Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  ORDER BY problem rip order

Author  Topic  ja928 Starting Member 5 Posts Posted - 2007-08-07 : 11:05:39 I want to make a payment coupon book. I know the payment amounts and payment 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!