Databases
StackOverflow2013
Postsdbo
POUpdate field by reordering date from all records within the table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUpdate field by reordering date from all records within the table
    primarykey
    data
    text
    <p>Currently, I have this records</p> <p><strong>InvoiceList Table</strong></p> <pre><code>InvoiceID StoreCustomerID IssuedDate Amount IsPenalty EmployeeID ----------- --------------- ---------- ----------- ----------- ----------- 1 13 2007-01-12 244 0 41 2 31 2007-04-05 81 0 34 3 23 2007-01-09 184 0 46 4 28 2007-11-21 231 0 17 5 36 2006-09-19 121 0 22 6 28 2006-10-24 240 0 17 7 15 2006-12-11 193 0 47 8 21 2007-01-15 172 0 4 </code></pre> <p><code>InvoiceID</code> is auto-incremented. What I want to do is to update <code>IssuedDate</code> by increment the date of the previous row. I want to update it like this</p> <pre><code>InvoiceID StoreCustomerID IssuedDate Amount IsPenalty EmployeeID ----------- --------------- ---------- ----------- ----------- ----------- 1 13 2007-01-12 244 0 41 2 31 2007-01-13 81 0 34 3 23 2007-01-14 184 0 46 4 28 2007-01-15 231 0 17 5 36 2007-01-16 121 0 22 6 28 2007-01-17 240 0 17 7 15 2007-01-18 193 0 47 8 21 2007-01-19 172 0 4 </code></pre> <p>Currently I have this select statement and is working well. But how can i used this to update the <code>IssuedDate</code>?</p> <pre><code>WITH SequenceDate AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY IssuedDate) RowNumber FROM Invoice ) SELECT RowNumber, DATEADD(d, RowNumber - 1, b.IssuedDate) FROM SequenceDate ORDER BY RowNumber </code></pre> <p><strong>UPDATE 1</strong></p> <p>I'm terribly sorry for the first post as the instruction given to me was not correct. The dates shouldn't be incremented since we are not allowed to alter the records in the table except that we can only rearrange the dates in ascending order. So it should be.</p> <pre><code>InvoiceID StoreCustomerID IssuedDate Amount IsPenalty EmployeeID ----------- --------------- ---------- ----------- ----------- ----------- 1 13 2006-09-19 244 0 41 2 31 2006-10-24 81 0 34 3 23 2006-12-11 184 0 46 4 28 2007-01-09 231 0 17 5 36 2007-01-12 121 0 22 6 28 2007-01-15 240 0 17 7 15 2007-04-05 193 0 47 8 21 2007-11-21 172 0 4 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSkyDrive
    1. USSkyDrive
    plurals
    1. PL
      singulars
      1. LTLinked
    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. POUpdate field by reordering date from all records within the table
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUpdate field by reordering date from all records within the table
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POUpdate field by reordering date from all records within the table
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO1. Select the date of last invoice id `SELECT IssuedDate from InvoiceList where InvoiceID = (SELECT MAX(InvoiceID) from InvoiceList)` 2. I am not sure whether date + 1 would give u the next day or not but there would be some way or the other to get that thing. `INSERT INTO InvoiceList values (blah, blah, STEP 1 LOGIC HERE);`
      singulars
      1. POUpdate field by reordering date from all records within the table
      1. USDhruvenkumar Shah
 

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