Geek Answers Handbook

Update field by reordering date from all records in the table

I currently have entries

InvoiceList Table

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

InvoiceID automatically increases. I want to update IssuedDate by increasing the date of the previous row. I want to update it as follows

 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

I currently have this select statement and it works well. But how can I use this to update IssuedDate ?

 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

UPDATE 1

I am terribly sorry for the first message, because the instruction given to me was not correct. Dates should not increase because we are not allowed to change the entries in the table, except that we can only change the dates in ascending order. It should be.

 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
+4
source share
6 answers

The answer is based on an updated OP question: http://sqlfiddle.com/#!3/dba13/22

 with SeqInvoice as ( select *, row_number() over(order by InvoiceId) rn from invoice ) ,SeqDate as ( select *, row_number() over(order by IssuedDate) rn from invoice ) update SeqInvoice set IssuedDate = sd.IssuedDate from SeqDate sd where sd.rn = SeqInvoice.rn; select * from Invoice;

Output:

 | INVOICEID | STORECUSTOMERID | ISSUEDDATE | AMOUNT | ISPENALTY | EMPLOYEEID | ---------------------------------------------------------------------------------------------------- | 1 | 13 | September, 19 2006 02:00:00-0700 | 244 | 0 | 41 | | 2 | 31 | October, 24 2006 02:00:00-0700 | 81 | 0 | 34 | | 3 | 23 | December, 11 2006 01:00:00-0800 | 184 | 0 | 46 | | 4 | 28 | January, 09 2007 01:00:00-0800 | 231 | 0 | 17 | | 5 | 36 | January, 12 2007 01:00:00-0800 | 121 | 0 | 22 | | 6 | 28 | January, 15 2007 01:00:00-0800 | 240 | 0 | 17 | | 7 | 15 | April, 05 2007 02:00:00-0700 | 193 | 0 | 47 | | 8 | 21 | November, 21 2007 01:00:00-0800 | 172 | 0 | 4 |

UPDATE

Here is a method without direct updating CTE directly updates the base table: http://sqlfiddle.com/#!3/dba13/24

 with SeqInvoice as ( select *, row_number() over(order by InvoiceId) rn from invoice ) ,SeqDate as ( select *, row_number() over(order by IssuedDate) rn from invoice ) update I set IssuedDate = sd.IssuedDate from Invoice i join SeqInvoice si on si.InvoiceId = i.InvoiceId join SeqDate sd on sd.rn = si.rn; select * from Invoice;
+1
source

If you know the first date in the sequence, you can simply add RowNumber to it:

 ; WITH SequenceDate AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY IssuedDate) RowNumber, MIN(IssuedDate) over () FirstDate FROM Invoice ) UPDATE SequenceDate SET IssuedDate = DATEADD(d, RowNumber - 1, FirstDate)

Here's the Sql Fiddle with an example .

UPDATE

to exactly match the first question:

 ; WITH SequenceDate AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY InvoiceID) RowNumber FROM Invoice ) UPDATE SequenceDate SET IssuedDate = DATEADD(d, RowNumber - 1, (select IssuedDate from Invoice where InvoiceID = 1))

And to change dates to follow InvoiceID :

 ; WITH SequenceDate AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY InvoiceID) RowNumber, ROW_NUMBER() OVER (ORDER BY IssuedDate) DateNumber FROM Invoice ) UPDATE SequenceDate SET IssuedDate = d.IssuedDate from SequenceDate d where SequenceDate.RowNumber = d.DateNumber
+7
source

First you want to use the first inserted date at least, then you should try something like this:

  WITH SequenceDate AS ( SELECT InvoiceID, ROW_NUMBER() OVER (ORDER BY IssuedDate) AS RowNumber FROM Invoice ) UPDATE InvoiceList SET InvoiceList.IssuedDate = DATEADD(d, SequenceDate.RowNumber - 1, b.IssuedDate) FROM SequenceDate INNER JOIN InvoiceList ON SequenceDate.InvoiceID = InvoiceList.InvoiceID CROSS JOIN (SELECT IssuedDate FROM InvoiceList WHERE InvoiceID = 1) b

Added SQL Fiddle .

+3
source

You can directly join the SequenceDate Invoice table.

 WITH SequenceDate AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY IssuedDate) RowNumber FROM Invoice ) UPDATE Invoice SET [IssuedDate] = DATEADD(d, RowNumber - 1, b.IssuedDate) FROM Invoice a INNER JOIN [SequenceDate] b ON a.[InvoiceID] = b.[RowNumber]

If this is the case, try this, then

 WITH SequenceDate AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY IssuedDate) RowNumber FROM Invoice ) UPDATE Invoice SET [IssuedDate] = b.IssuedDate FROM Invoice a INNER JOIN [SequenceDate] b ON a.[InvoiceID] = b.[RowNumber]

SQLFiddle Demo

+2
source

UPDATE : the following is a solid answer, the answer was made based on the original OP question:

For the correct conclusion, this is the shortest: http://www.sqlfiddle.com/#!3/6aa22/1

 SELECT * FROM INVOICE; WITH FirstDate AS ( SELECT row_number() over(order by InvoiceID) rn, IssuedDate FROM Invoice ) ,UpdatedDate as ( select i.InvoiceID, i.IssuedDate, dateadd(d, row_number() over(order by i.InvoiceID) - 1, fd.IssuedDate) as NewDate from invoice i join FirstDate fd on fd.rn = 1 ) update UpdatedDate set IssuedDate = NewDate; select * from Invoice;

Output:

 | INVOICEID | STORECUSTOMERID | ISSUEDDATE | AMOUNT | ISPENALTY | EMPLOYEEID | -------------------------------------------------------------------------------------------------- | 1 | 13 | January, 12 2007 08:00:00-0800 | 244 | 0 | 41 | | 2 | 31 | January, 13 2007 08:00:00-0800 | 81 | 0 | 34 | | 3 | 23 | January, 14 2007 08:00:00-0800 | 184 | 0 | 46 | | 4 | 28 | January, 15 2007 08:00:00-0800 | 231 | 0 | 17 | | 5 | 36 | January, 16 2007 08:00:00-0800 | 121 | 0 | 22 | | 6 | 28 | January, 17 2007 08:00:00-0800 | 240 | 0 | 17 | | 7 | 15 | January, 18 2007 08:00:00-0800 | 193 | 0 | 47 | | 8 | 21 | January, 19 2007 08:00:00-0800 | 172 | 0 | 4 |
+1
source

I would use a SQL cursor, something like this ...

 DECLARE @InvoiceId AS INT DECLARE @PreviousInvoiceId AS INT DECLARE @NextIssuedDate AS DATE SET @PreviousInvoiceId = 0 --Date you want to start from SET @NextIssuedDate = '2007-01-12' DECLARE csrUpdateDate CURSOR FOR SELECT InvoiceID FROM Invoice ORDER BY InvoiceID OPEN csrUpdateDate FETCH NEXT FROM csrUpdateDate INTO @InvoiceId WHILE @@FETCH_STATUS = 0 BEGIN BEGIN IF(@InvoiceId <> @PreviousInvoiceId) UPDATE Invoice SET IssuedDate = @NextIssuedDate WHERE InvoiceId = @InvoiceId END SET @PreviousInvoiceId = @InvoiceId SET @NextIssuedDate = DATEADD(DAY,1,@NextIssuedDate) FETCH NEXT FROM csrUpdateDate INTO @InvoiceId END CLOSE csrUpdateDate DEALLOCATE csrUpdateDate
0
source

More articles:


All Articles