StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Okay, I've lost sleep on this one. I think this works:</p> <pre><code>;with CommonLines as ( select [text], MIN(position) as minPos,MAX(position) as maxPos, MIN(price) as minPrice,MAX(price) as maxPrice from @InvoiceLine where invoiceId in (select n from @ids) group by [text] having COUNT(*) = (select COUNT(*) from @ids) ), InvoiceOrders as ( select invoiceId,[text],ROW_NUMBER() OVER (PARTITION BY InvoiceId order by Position) as rn from @InvoiceLine where [text] in (select [text] from CommonLines) and invoiceId in (select n from @ids) ), AlwaysAdjacent as ( --Ignoring lines that aren't going to appear at all select a1.[text] as FirstText,a2.[text] as SecondText,ROW_NUMBER() OVER (ORDER BY a1.[text]) as Ord from InvoiceOrders a1 inner join InvoiceOrders a2 on a1.invoiceId = a2.invoiceId and a1.rn = a2.rn - 1 group by a1.text,a2.text having COUNT(*) = (select COUNT(*) from @ids) ) select cl.[text], CASE WHEN aa1.Ord IS NOT NULL THEN 1 ELSE 0 END as MoveDown, CASE WHEN aa2.Ord IS NOT NULL THEN 1 ELSE 0 END as MoveUp, CASE WHEN minPrice=maxPrice THEN minPrice END as price, CASE WHEN minPos=maxPos THEN minPos END as Position from CommonLines cl left join AlwaysAdjacent aa1 on cl.[text] = aa1.FirstText left join AlwaysAdjacent aa2 on cl.[text] = aa2.SecondText order by COALESCE(aa2.Ord,aa1.Ord), CASE WHEN aa1.Ord IS NOT NULL THEN 0 WHEN aa2.Ord IS NOT NULL THEN 1 ELSE 2 END </code></pre> <p>I put the invoice data into a table variable also, and ignored invoice since it didn't seem relevant - setup data below. </p> <p>Hopefully, it's reasonable easy to read, but some explanation anyway. <code>CommonLines</code> does a relational division against <code>InvoiceLine</code>, to find the <code>text</code>s that we're going to work with. <code>InvoiceOrders</code> then, for each invoice, calculates the order in which these <code>text</code>s appear - this ordering ignores any other rows, and is just based on <code>position</code>.</p> <p><code>AlwaysAdjacent</code> then performs another relational division, having used <code>InvoiceOrders</code> twice to determine those <code>text</code> values that appear in the same (relative) positions within all invoices - that is, the difference between their ordering is a constant, even if the absolute values are different.</p> <p>Finally, we output the texts, and use <code>AlwaysAdjacent</code> to determine whether moving down or up should be allowed. The final <code>ORDER BY</code> is a bit tricky, but I think is correct - it's trying to accommodate longer runs than 2, where the middle rows can move both up and down (i.e. swap <code>F</code> and <code>E</code>'s positions in <code>1001</code> and F should appear between C and G, and the entire set can be reordered)</p> <p>Result:</p> <pre><code>text MoveDown MoveUp price Position ---- ----------- ----------- --------------------------------------- ----------- F 0 0 NULL NULL B 1 0 NULL NULL C 0 1 NULL NULL G 1 0 4.20 6 H 0 1 NULL 7 </code></pre> <hr> <p>Setup data:</p> <pre><code>declare @InvoiceLine table (invoiceId int not null,position int not null,[text] char(1) not null,price decimal(12,2) not null) INSERT INTO @InvoiceLine([invoiceId],[position],[text],[price]) VALUES (1000,1,'F',10.5), (1000,2,'A',3.0), (1000,3,'B',4.0), (1000,4,'C',1.0), (1000,5,'E',1.0), (1000,6,'G',4.2), (1000,7,'H',9.0), (1000,8,'K',9.0), (2000,1,'B',4.15), (2000,2,'C',1.35), (2000,3,'D',1.20), (2000,4,'F',12.10), (2000,6,'G',4.2), (2000,7,'H',2.7), (2000,8,'I',1.3), (3000,1,'B',41.15), (3000,2,'C',15.35), (3000,3,'D',12.20), (3000,4,'F',11.10), (3000,5,'I',4.0), (3000,6,'G',4.2), (3000,7,'H',6.7), (3000,8,'E',7.3); DECLARE @ids TABLE(n int); INSERT INTO @ids (n) VALUES (1000),(2000),(3000); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload