Update field by reordering date from all records within the table

Question

Currently, I have this records

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 is auto-incremented. What I want to do is to update IssuedDate by increment the date of the previous row. I want to update it like this

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

Currently I have this select statement and is working well. But how can i used this to update the 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'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.

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

1. 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);` — Dhruvenkumar Shah, Aug 28 '12 at 13:55

Nikola Markovinović · Answer 1 · 2012-08-29T07:46:52.210

7

If you know 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 is Sql Fiddle with example.

UPDATE:

to match first question's output exactly:

; 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 rearange 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

edited Aug 29 '12 at 07:46

answered Aug 28 '12 at 14:11

Nikola Markovinović

18,963
5
46
51

SequenceDate is the CTE table, I think you need to update the IssueList instead. – András Ottó Aug 28 '12 at 14:17
1

@AndrásOttó CTE behaves as updatable view under certain conditions. You can directly update it. – Nikola Markovinović Aug 28 '12 at 14:19
@DerekFloss even you didn't update your question, this answer is still incorrect, the answer's start date is September 19,2006 instead of January 12,2007(from your original question) – Michael Buen Aug 28 '12 at 15:18
For the original requirement, this is the correct answer: http://stackoverflow.com/questions/12160599/update-field-by-reordering-date-from-all-records-within-the-table/12162198#12162198 For the new requirement, this is the correct answer: http://stackoverflow.com/questions/12160599/update-field-by-reordering-date-from-all-records-within-the-table/12162373#12162373 – Michael Buen Aug 28 '12 at 15:26
Upvoters doesn't bother to check if this answer is correct, despite the answerer's sqlfiddle link clearly shows his own answer is incorrect. Just because an answer look elegant, it gets an upvote :-) Correctness fell out of fashion :p – Michael Buen Aug 28 '12 at 16:23
+1 since you corrected your answer(on both old question and updated question). I want to +1 you again as I haven't quickly thought of running two `row_number()` in parallel. It's a bit confusing though which of the two `row_number()` will the final select base its sorting (it might be implementation dependent). Though for the OP's requirement, the sorting has no bearing on final output, just need to match the right IssuedDate – Michael Buen Aug 29 '12 at 15:40

András Ottó · Answer 2 · 2012-08-28T14:24:38.673

3

First you want to use the first inserted date not the minimum, 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

SQL Fiddle is attached.

edited Aug 28 '12 at 14:24

answered Aug 28 '12 at 14:15

András Ottó

7,605
1
28
38

+1 for pointing out OP's requirement to start with date from invoice 1. It is still strange because date order from example suggests that OP would rather have order by InvoiceID in row_number() part of query. – Nikola Markovinović Aug 28 '12 at 14:27
Sql Fiddle has `Text to DDL` feature, please give it a try. It usually gets the job done after a bit of shuffling original text around. – Nikola Markovinović Aug 28 '12 at 14:29

John Woo · Answer 3 · 2012-08-28T15:07:34.677

2

You can join Invoice table from SequenceDate directly.

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 that's 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

edited Aug 28 '12 at 15:07

answered Aug 28 '12 at 14:23

John Woo

258,903
69
498
492

It won't work properly, since even years and months are not the same. If you are using the IssueDate from the CTE, try your solution in the sql fiddle (just replacing Invoice to InvoiceList) – András Ottó Aug 28 '12 at 14:27

score 1 · Answer 4 · edited Sep 04 '12 at 01:45

UPDATE: following is stale answer, answer was made based on OP's original question:

For having correct output, 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 |

Michael Buen · Accepted Answer · 2012-08-28T22:44:36.990

Answer based on the OP's updated 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's a way without directly updating the CTE, updates the base table directly: 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;

I'm looking for this answer, can you please explain a little bit what is happening in your code? why can you directly update `CTE` ? — SkyDrive, Aug 28 '12 at 22:05
Not only you can directly update CTE, you can also directly update views in Sql Server. Some RDBMS doesn't allow updating views and CTEs. CTE is just like an inlined view. If a view or CTE is simple, Sql Server allows you to update its base table (and even delete from it) — Michael Buen, Aug 28 '12 at 22:19
Deleting from CTE http://stackoverflow.com/questions/6645746/how-to-get-one-unique-record-from-the-same-list-of-records-from-table-no-unique/6645780#6645780 — Michael Buen, Aug 28 '12 at 22:21
@MichaelBuen kabayan, you mean that `View` is updatable? even if it has multiple joins on it? — John Woo, Aug 29 '12 at 01:44
@JohnWoo Yep kabayan, since I learned that views is updateable in Sql Server 7, I quickly use that capability(on VB6's DataGrid). Other RDBMS don't have updateable views nor updateable CTE. Updateable views in action: http://www.sqlfiddle.com/#!3/f1176/2 Btw, do you have any blog or twitter? ツ — Michael Buen, Aug 29 '12 at 12:20
@JohnWoo my SqlFiddle example is contrived(now), I used that technique when SQL Server don't have any computed column capability yet — Michael Buen, Aug 29 '12 at 12:31

Jonno Lord · Answer 6 · 2012-08-28T14:47:45.913

I would use an 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

Update field by reordering date from all records within the table

6 Answers6

SQLFiddle Demo