Databases
StackOverflow2013
Postsdbo
POFind which pairs of records' positions can safely be switched in a combined set of records
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFind which pairs of records' positions can safely be switched in a combined set of records
    primarykey
    data
    text
    <p>Here is the situation: I have a page where I can edit multiple records at once (Let's say invoices). They are not shown below each other, but they are edited like one record. At page load, values are displayed in a given field only when they are the same throughout the whole set of records.</p> <p>Now, on top of that, I also want to edit child records (1-n relationship; lines of invoice). I managed to show the invoice lines records that are identical throughought all of the edited invoices, like so:</p> <pre><code>Invoice 1 Invoice 2 Lines edited A B D D C G F D =&gt; G E G </code></pre> <p>Assuming A, B, ... are the lines of invoice</p> <p>Ordering matters through the lines of the invoice, therefore each line of invoice has a position field. Here is what I want to do: allow reordering the lines of invoice. When editing one invoice, this is an easy task. But when editing multiple invoices at once, some issue(s) appears. Consider the following:</p> <pre><code>Invoice 1 Invoice 2 Lines edited F(1) B(1) F(1) A(2) C(2) B(2) B(3) D(3) =&gt; C(3) C(4) F(4) E(5) </code></pre> <p>When moving line F after line B, in Invoice 1, F is not only moved after B but also after A and the user doesn't know about it; in Invoice 2, F is already after B, but the user didn't know about it. So should B be placed right before F (at position 3) or stay where it is ? This is unclear.</p> <p>What I want to do is prevent reordering when the behavior is unclear (or unexpected) and allow it in other situations. Here is my solution: For each line of invoice, find if it can be moved up one step (position--) and if it can be moved down one step (position++). How ? For each neighbour pair of lines in the edition page (in the example: F-B; B-C), if the corresponding pairs in the source invoices lines are neighbours and in the same order, the pair can be switched. So, in the example, that means B and C can be switched but not F and B. Therefore the result would be:</p> <pre><code>Lines edited move down move up F no no B yes no C no yes </code></pre> <p>Here is more or less my current situation:</p> <pre><code>CREATE TABLE [InvoiceLine]( [id] [int] IDENTITY(1,1) NOT NULL, [invoiceId] [int] NOT NULL, [position] [int] NOT NULL, [text] [nvarchar](255) NULL, [price] [decimal](18,2) NULL, CONSTRAINT [PK_InvoiceLine] PRIMARY KEY CLUSTERED ([id] ASC) ON [PRIMARY] ) CREATE TABLE [Invoice]( [id] [int] IDENTITY(1,1) NOT NULL, [customerId] [int] NULL, CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED ([id] ASC) ON [PRIMARY] ) INSERT INTO [Invoice]([customerId]) VALUES (1000), (2000); 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); DECLARE @n int; SET @n = (SELECT COUNT(*) FROM @ids); SELECT CAST(text AS nvarchar) AS id, 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 ORDER BY position </code></pre> <p>My question is:</p> <p><strong>how should I transform that query (actually stored procedure) to get the "move up" and "move down" fields ?</strong></p> <p>The first idea I had was to take all neighbour pairs from the result and find their distance in each source invoice lines (the distance being the absolute value of the position difference) and take the maximum of the distances. If the maximum is equal to 1 and the differences all have the same sign, then the pair's position can be switched. But then I don't know how to translate that to SQL...</p> <p>[<strong>EDIT</strong>] One more thing: top edited record should always have move up = no while lowest edited record should always have move down = no.</p> <p>[<strong>EDIT 2012-02-23</strong>] Added ORDER BY at the end of the query</p> <p>[<strong>EDIT 2012-02-23</strong>] Here is a second set of data an its expected output:</p> <pre><code>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), (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> <p>Should Yield:</p> <pre><code>id position text price isRecordDeleted moveUp moveDown B NULL B NULL 0 no yes C NULL C NULL 0 yes no F NULL F NULL 0 no no G 6 G 4.20 0 no yes H 7 H NULL 0 yes no </code></pre> <p>[<strong>EDIT 2012-02-24</strong>] And duplicated lines should appear only once and have moveUp and moveDown only if they are direct neighbours</p> <p>Here is a third set of data an its expected output:</p> <pre><code>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), (1000,6,'J',3.2), (1000,7,'G',4.2), (1000,8,'H',9.0), (1000,9,'K',9.0), (1000,10,'F',3.0), (2000,1,'B',4.15), (2000,2,'C',1.35), (2000,3,'D',1.20), (2000,4,'C',1.35), (2000,5,'F',12.10), (2000,6,'J',6.2), (2000,7,'G',4.2), (2000,8,'H',2.7), (2000,9,'H',3.1), (2000,10,'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,'J',2.3), (3000,7,'G',4.2), (3000,8,'H',6.7), (3000,9,'E',7.3); DECLARE @ids TABLE(n int); INSERT INTO @ids (n) VALUES (1000),(2000),(3000); </code></pre> <p>Should Yield:</p> <pre><code>id position text price isRecordDeleted moveUp moveDown B NULL B NULL 0 no no C NULL C NULL 0 no no F NULL F NULL 0 no no J 6 J NULL 0 no yes G 7 G 4.20 0 yes no H NULL H NULL 0 no no </code></pre> <p>Or better:</p> <pre><code>id position text price isRecordDeleted moveUp moveDown B NULL B NULL 0 no no C NULL C NULL 0 no no F NULL F NULL 0 no no J 6 J NULL 0 no yes G 7 G 4.20 0 yes yes H NULL H NULL 0 yes no </code></pre> <p>[<strong>EDIT 2012-03-02</strong>]</p> <p>The second result is better because although H appears twice in invoice 2000, both lines are neighbours, therefore it is safe to switch position of H and G: both H lines will be switched.</p> <p>But this last result will probably result in an overcomplicated query.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDaniel
    1. USDaniel
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFind which pairs of records' positions can safely be switched in a combined set of records
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFind which pairs of records' positions can safely be switched in a combined set of records
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POFind which pairs of records' positions can safely be switched in a combined set of records
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 for giving us your table structure and data as a script. You'd be amazed at how much time that saves when trying to answer a question.
      singulars
      1. POFind which pairs of records' positions can safely be switched in a combined set of records
      1. USJohn N
    2. COHard to help because I can't visualize what the user is doing with your interface. What kind of app is this, what's the goal? Why are users editing multiple invoices at once? Do they see all these lines under the invoices as well as "lines edited"? When values are displayed in a given "field"... what "fields" are you referring to? Do "lines edited" mean lines commonly found in all invoices? Does this list change as they use the interface? Why does a user need to know lines edited? When a user "moves a line", are they drag-n-dropping horizontally within an invoice?
      singulars
      1. POFind which pairs of records' positions can safely be switched in a combined set of records
      1. USsisdog
    3. CO@sisdog, answer 1: Actually, I'm creating something like an app generator (more like scaffolding and only partial for now). So this should apply to the generic case of 1-n relationships. Which is the case with invoices where 1 invoice can contain n lines of invoice.
      singulars
      1. POFind which pairs of records' positions can safely be switched in a combined set of records
      1. USDaniel
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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