October 27, 2010 at 3:17 pm
Ok we have a printer that will print two 8.5 X 11 forms side by side, continuous.
That said I need to assign a sequence number that will keep thing in order. Here is how the file must be ordered.
Now for this example code or structure is not really needed; I don't think. This is more a business rules, approach kind of thing.
Lets say I have 100 records.
Now within this 5 records belong to person A and the rest are all individual customers.
So because we are using side-by-side printing on continuous forms, another machine cuts these apart, lets say left side and right side.
The way this needs to print is that all the forms belonging to a given customer (in the case of duplicates need to be grouped together, and printed on ONE SIDE of the (left / right) printing.
So as a simple example if there were NO duplicate forms for a customer I would need records 1 - 50 printed on the left and 51 - 100 printed on the right.
In the above example the physical records would need to be ordered as follows:
RecordID
1
51
2
53
3
54
4
55
SO.... When printed record one would be on the left, first item printed, on the right (right next to record one) would be record 51
so the records would read down the left 1,2,3,4, etc...
And on the right starting from the top 51,52,53,54,55
Now because of possible multiple forms for a given customer, like our example of 5 we want to make sure this guys 5 pages are printed together on one side; down the left, consequentiality for 5 pages or forms..
Am I making this harder than this is?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
October 28, 2010 at 1:07 am
DECLARE@Sample TABLE
(
PageID INT
)
INSERT@Sample
VALUES(1), (2), (3), (4), (51), (53), (54), (55)
;WITH cteSource(PageID, RecID)
AS (
SELECTPageID,
ROW_NUMBER() OVER (PARTITION BY PageID / 50 ORDER BY PageID) AS RecID
FROM@Sample
)
SELECTPageID
FROMcteSource
ORDER BYRecID,
PageID
N 56°04'39.16"
E 12°55'05.25"
October 28, 2010 at 5:24 am
Hi Jeffrey,
Actually, this is quite complex (in my mind anyway), because of the multiple records per customer needing to be on alternate lines. I'm assuming that you have data that looks like something this:
CustomerIdPrint_Data
1Print data
3Print data
3Print data
3Print data
5Print data
7Print data
7Print data
7Print data
7Print data
8Print data
9Print data
10Print data
The tricky bit is that you need to alternate records where there are multiple lines for a single customer, like this:
CustomerIdPrint_DataPrint_On
1Print dataLeft
3Print dataRight
5Print dataLeft
3Print dataRight
8Print dataLeft
3Print dataRight
etc...
Here's a solution that relies on a temporary interim table and a tally table to do the job. It is likely that it will be possible to refine this, but it's a starter for 10.
[Replace references to tally table with the details for your own tally table. If you don't have one, search this site for Jeff Moden's tally table article - you'll be glad you did :-)]
Create some test data:
create table #printdata (
CustomerId int
, Print_Data varchar(10)
)
insert #printdata values (1,'Print data')
insert #printdata values (3,'Print data')
insert #printdata values (3,'Print data')
insert #printdata values (3,'Print data')
insert #printdata values (5,'Print data')
insert #printdata values (7,'Print data')
insert #printdata values (7,'Print data')
insert #printdata values (7,'Print data')
insert #printdata values (7,'Print data')
insert #printdata values (8,'Print data')
insert #printdata values (9,'Print data')
insert #printdata values (10,'Print data')
insert #printdata values (11,'Print data')
insert #printdata values (12,'Print data')
insert #printdata values (13,'Print data')
insert #printdata values (14,'Print data')
insert #printdata values (15,'Print data')
insert #printdata values (16,'Print data')
insert #printdata values (17,'Print data')
insert #printdata values (18,'Print data')
insert #printdata values (19,'Print data')
insert #printdata values (20,'Print data')
insert #printdata values (21,'Print data')
Essentially, we need to split the data into two - the customers with multiple records and the customers with just one. The multi customers need to have a separator record interspersed between each data record. This section does that:
-- find the number of records that are part of multiples
-- so that we know how many blank records to create
declare @multi_record_count int
select @multi_record_count = count(*)
from #printdata
where CustomerId in (
select CustomerId from #printdata
group by CustomerId
having count(*) > 1
)
-- process the multiple line customers
-- first section creates null rows
-- so that we can interspace these between the rows with data later
; with cte as (
select null as CustomerId
, null as Print_Data
from Dev_Utils.dbo.Tally_Table
where Tally_Number <= @multi_record_count
)
select *
, row_number() over (order by CustomerId) as Ordering
into #multi_print_data
from cte
union all
-- second section retrieves all records that are part of multiples
select *
, row_number() over (order by CustomerId) as Ordering
from #printdata pd
where CustomerId in (
select CustomerId from #printdata
group by CustomerId
having count(*) > 1
)
Now, we pull out the customers with just one record, and use coalesce to fill in the gaps in the first table. Note that the use of a right join assumes that you have more single customer records than multiple, if this is not the case then the join needs to change. You should probably build in some kind of check for this to prevent any unexpected consequences.
-- join back to original data to merge the two sets
; with cte as (
select CustomerId
, Print_Data
from #printdata
where CustomerId in (
select CustomerId from #printdata
group by CustomerId
having count(*) = 1 -- we only want the single items
)
)
, cte2 as (
select *
, row_number() over (order by CustomerId) as Ordering
from cte
)
select coalesce(mpd.CustomerId,cte2.CustomerId) as CustomerId
, coalesce(mpd.Print_Data,cte2.Print_Data) as Print_Data
from #multi_print_data mpd
right join cte2
on mpd.Ordering = cte2.Ordering
order by cte2.Ordering, mpd.CustomerId
Hope this helps.
Edit: I'm assuming that order of printing is not relevant, so printing 1 left 2 right, 3 left, 4 right is just as good as 1 left, 51 right, 2 left, 52 right. Let me know if this isn't the case...
Regards, Iain
October 28, 2010 at 6:07 am
I will look at these solutions the moment I get too the office thank you so much guys. Just an fyi, 10 records was just an easy number to discuss. These tables will have 100,000 + records in them.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
October 28, 2010 at 7:05 am
Post some more sample data to test with.
Also, post for several customers (ie add customer column) to the sample data.
Meanwhile, test this...
-- Prepare sample data
DECLARE @Sample TABLE
(
CustomerNumber INT NOT NULL,
PageNumber INT NOT NULL
)
INSERT @Sample
VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 51), (1, 53), (1, 54), (1, 55),
(2, 1),
(3, 19), (3, 22), (3, 45)
-- Display the wanted output
;WITH cteSource(CustomerNumber, PageNumber, Items, Yak)
AS (
SELECT CustomerNumber,
PageNumber,
(1 + COUNT(*) OVER (PARTITION BY CustomerNumber)) / 2 AS Items,
ROW_NUMBER() OVER (PARTITION BY CustomerNumber ORDER BY PageNumber) - 1 AS Yak
FROM @Sample
)
SELECT CustomerNumber,
PageNumber
FROM cteSource
ORDER BYCustomerNumber,
Yak % Items,
PageNumber
N 56°04'39.16"
E 12°55'05.25"
October 28, 2010 at 8:29 am
Thank you everyone for your help. These solutions did not do the trick however I did manage to solve the problem. It MAY not be the most efficient method; took about a minute to do 23,000 records but it DOES work like a charm
I built what I am calling an index table and assigning an identity column called IndexID
The of course will auto number sequentially and is used to keep the records in order after they are sorted; so to speak.
Then I run the following stored procedure to populate the index table
CREATE PROCEDURE PartitionSeqNumbers
AS
DECLARE @Count as bigint
,@PartitionRecordID as bigint
,@LeftIndex as bigint
,@RightIndex as bigint
SELECT @PartitionRecordID = (count(*) / 2)
FROM SourceParsed1_227
SET @LeftIndex = '1'
SET @RightIndex = @PartitionRecordID
SELECT @Count = count(*)
FROM SourceParsed1_227
PRINT ' File Partition Point determined: ' + cast(@PartitionRecordID as varchar)
PRINT ''
PRINT ''
PRINT ''
PRINT ' Building Results Cube'
PRINT ' LEFT Page Index marker SET as: ' + cast(@LeftIndex as varchar)
PRINT ' RIGHT Page Index marker SET as: ' + cast(@RightIndex as varchar)
PRINT ' Total Records to Process: ' + cast(@Count as varchar)
PRINT ' DETERMINE PARTIONED VIEW State'
WHILE @Count > 0
BEGIN
INSERT INTO SeqNumberIndex
(SeqNumber, SourceRecordID)
SELECT TOP 1 SeqNumber, RecordID
FROM SourceParsed1_227
WHERE SourceParsed1_227.SeqNumber = @LeftIndex
ORDER BY SeqNumber
INSERT INTO SeqNumberIndex
(SeqNumber, SourceRecordID)
SELECT TOP 1 SeqNumber, RecordID
FROM SourceParsed1_227
WHERE SourceParsed1_227.SeqNumber = @RightIndex
ORDER BY SeqNumber
SET @LeftIndex = (@LeftIndex + 1)
SET @RightIndex = (@RightIndex + 1)
SET @Count = (@Count - 2)
CONTINUE
END
To query my data table I use the index table in a joint so that I can force the order for the export of this data.
This query is simply:
SELECT *
FROM SourceParsed1_227 as sp1
JOIN SeqNumberIndex as sni on sni.SeqNumber = sp1.SeqNumber
ORDER BY sni.IndexID
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
October 28, 2010 at 8:59 am
Email me the full sample data to peso(at)developerwork(dot)net.
N 56°04'39.16"
E 12°55'05.25"
October 29, 2010 at 3:43 am
Hi Jeffrey,
I can see one potential issue with your solution that you might want to consider, specifically, what happens when a group of prints for a customer sit either side of the halfway point in your data?
Example:
10 records to print, note that CustomerId 4 straddles the break point:
CustomerIdPrintData
1Print This
2Print This
3Print This
4Print This
4Print This
4Print This
5Print This
6Print This
7Print This
8Print This
Keys are assigned like this:
CustomerIdPrintDataKey
1Print This 1
2Print This 3
3Print This 5
4Print This 7
4Print This 9
4Print This 2
5Print This 4
6Print This 6
7Print This 8
8Print This 10
Leading to a final order of:
CustomerIdPrintDataKey
1Print This 1
4Print This 2
2Print This 3
5Print This 4
3Print This 5
6Print This 6
4Print This 7
7Print This 8
4Print This 9
8Print This 10
With the prints for CustomerId 4 split into two groups.
Why are you splitting from halfway? Is it important, or just so you can separate the grouped records? Why did my solution fail? Did you get errors, or was there another issue? It should work.
DDL and sample data would be useful.
October 30, 2010 at 2:13 pm
This brings back memories, here's an updated version of what I recall doing some 10 years ago.
SELECT
[CustomerID] = '01'
,[PrintData] = 'Print This'
INTO #CustData
UNION
SELECT '01', 'Print This 2'
UNION
SELECT '02', 'Print This'
UNION
SELECT '03', 'Print This'
UNION
SELECT '04', 'Print This'
UNION
SELECT '04', 'Print This 2'
UNION
SELECT '04', 'Print This 3'
UNION
SELECT '05', 'Print This'
UNION
SELECT '06', 'Print This'
UNION
SELECT '07', 'Print This'
UNION
SELECT '08', 'Print This'
DECLARE @Split int
SET @Split = (Select count(*) from #CustData)
IF (@Split % 2) = 1
BEGIN
INSERT INTO #CustData VALUES ('99', 'BLANK')
SET @Split += 1
END
SELECT
[CustomerID] = CustomerID
,[FormCount] = count(*)
INTO #CustQty
FROM #CustData
GROUP BY [CustomerID]
SELECT
-- [PrintSeq] = (dense_rank() over (order by q.FormCount desc, d.CustomerID, d.PrintData)) * 2
[PrintSeq] = (dense_rank() over (order by d.CustomerID, d.PrintData)) * 2
,[CustomerID] = d.CustomerID
,[PrintData] = d.PrintData
INTO #PrintOrder
FROM #CustData d
JOIN #CustQty q on q.CustomerID = d.CustomerID
UPDATE #PrintOrder
SET [PrintSeq] = [PrintSeq] - 1
WHERE [PrintSeq] <= @Split
UPDATE #PrintOrder
SET [PrintSeq] = ([PrintSeq] - @Split)
WHERE [PrintSeq] > @Split
Select
[PrintSeq]
,[CustomerID]
,[PrintData]
,[Side] = CASE WHEN ([PrintSeq] % 2) = 1
THEN 'LEFT'
ELSE 'RIGHT'
END
FROM #PrintOrder
ORDER BY [PrintSeq]
DROP TABLE #CustData
DROP TABLE #CustQty
DROP TABLE #PrintOrder
You sequence your list with gaps then adjust sequence for first half to odd numbers (Left) and last half to sequence - split point. This interleaves the two halves so that when printed and slit you stack the left half on the right half resulting in a continuous set of forms.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply