StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Based on the answer of Damien_The_Unbeliever, here is a solution for result 1 of the third set of data (a solution for result 2 would be better):</p> <pre><code>DECLARE @InvoiceLine TABLE ( [id] [int] IDENTITY(1,1) NOT NULL, [invoiceId] [int] NOT NULL, [position] [int] NOT NULL, [text] [nvarchar](255) NULL, [price] [decimal](18,2) NULL ) DECLARE @Invoice TABLE ( [id] [int] IDENTITY(1,1) NOT NULL, [customerId] [int] NULL ) INSERT INTO @Invoice([customerId]) VALUES (1000), (2000), (3000); 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), (2000,1,'B',4.15), (2000,2,'C',1.35), (2000,3,'D',1.20), (2000,4,'F',12.10); DECLARE @ids TABLE(n int); INSERT INTO @ids (n) VALUES (1000),(2000); ;WITH Lines AS ( SELECT CAST(text AS nvarchar) AS id, position AS pos, CASE WHEN rank_position = _rankMain THEN position ELSE NULL END AS position, CASE WHEN rank_text = _rankMain THEN text ELSE NULL END AS text, CASE WHEN rank_price = _rankMain THEN price ELSE NULL END AS price, 0 AS isRecordDeleted FROM ( SELECT T4.position, RANK() OVER (PARTITION BY T4.text, T4.position ORDER BY id) AS rank_position, T4.text, RANK() OVER (PARTITION BY T4.text ORDER BY id) AS rank_text, T4.price, RANK() OVER (PARTITION BY T4.text, T4.price ORDER BY id) AS rank_price, RANK() OVER (PARTITION BY T4.text ORDER BY id) AS _rankMain, _cnt FROM ( -- Filter lines SELECT text, ( SELECT COUNT(id) FROM @InvoiceLine WHERE invoiceId IN (SELECT * FROM @ids) AND text = T2.text ) AS _cnt FROM ( -- add rank on text field (an invoice line is considered equal to another one if both text fields are equal) SELECT RANK() OVER (PARTITION BY text ORDER BY invoiceId) AS rnk, text FROM ( -- distinct lines SELECT DISTINCT invoiceId, text FROM @InvoiceLine WHERE invoiceId IN (SELECT n FROM @ids) ) T1 ) T2 WHERE rnk = (SELECT COUNT(n) FROM @ids) ) T3 INNER JOIN @InvoiceLine T4 ON T4.text = T3.text ) T5 WHERE _cnt = _rankMain ), LinesNoDuplicates AS ( -- All lines of invoice from the selected invoices that aren't duplicated in any of those invoices SELECT * FROM @InvoiceLine WHERE text NOT IN( -- Exclude texts that appear twice in an invoice SELECT text FROM ( -- texts that appear twice in an invoice SELECT text, RANK() OVER (PARTITION BY text, invoiceId ORDER BY position) as rnk FROM @InvoiceLine WHERE invoiceId IN (SELECT n FROM @ids) ) T1 WHERE rnk = 2 ) AND invoiceId IN (SELECT n FROM @ids) ), Switchables AS ( -- Which lines can be switched SELECT UpperLine.text UpperText, LowerLine.text LowerText FROM LinesNoDuplicates UpperLine INNER JOIN LinesNoDuplicates LowerLine ON UpperLine.invoiceId = LowerLine.invoiceId AND UpperLine.position = LowerLine.position - 1 GROUP BY UpperLine.text, LowerLine.text HAVING COUNT(*) = (SELECT COUNT(*) FROM @ids) ) SELECT id, position, text, price, isRecordDeleted, CASE WHEN SwitchUp.LowerText IS NOT NULL THEN 'yes' ELSE 'no' END AS moveUp, CASE WHEN SwitchDown.UpperText IS NOT NULL THEN 'yes' ELSE 'no' END AS moveDown FROM Lines LEFT JOIN Switchables SwitchUp ON text = SwitchUp.LowerText LEFT JOIN Switchables SwitchDown ON text = SwitchDown.UpperText ORDER BY pos; </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