SQL - ID to date, multiple dates

Question

This is my table:

Date        SSSN    MyID    Hours
01012013    1234            8
01012013    2345            7
01012013    3456            8
02012013    1234            5
02012013    2345            12
02012013    3456            7

I want to set MyID so that the table looks like this:

Date        SSSN    MyID    Hours
01012013    1234    1       8
01012013    2345    1       7
01012013    3456    1       8
02012013    1234    2       5
02012013    2345    2       12
02012013    3456    2       7

I was looking at this: Syntax of for-loop in SQL Server and this SQL : Update ID; According to Date but didn´t get there.

Seems to be a easy job but somehow not working as desired.

I cannot use alter table IDENTITY(1,1) just to be clear.

This is when using SQL Server.

Solved by using, dense_rank() instead of row_number() as suggested by BlueFeet.

Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it. — Taryn, Mar 18 '13 at 12:16

Taryn · Accepted Answer · 2013-03-18T13:15:59.300

2

Depending on you database product that you are using, you might be able to apply a row_number() to generate the MyID value:

;with cte as
(
  select date, sssn, myid, hours,
    row_number() over(partition by sssn order by date) rn
  from yourtable
) 
update cte
set myid = rn;

See SQL Fiddle with Demo.

The row_number() function is available in most modern RDBMS.

Edit #1, Since you can have multiple rows of sssn on each date, then you will want to use dense_rank:

;with cte as
(
  select date, sssn, myid, hours,
    dense_rank() over(order by date) rn
  from yourtable
) 
update cte
set myid = rn;

See SQL Fiddle with Demo

edited Mar 18 '13 at 13:15

answered Mar 18 '13 at 12:21

Taryn

242,637
56
362
405

Already tried this solution but was having issues due to, row number being added to same date when multiple transactions. That is when Date SSN MyId 01012013 1234 1 01012013 1234 2 – AceAlfred Mar 18 '13 at 12:44
@AceAlfred So you will have multiple transactions on the same date? – Taryn Mar 18 '13 at 12:51
Yes sometimes, when writing hours to different jobs. – AceAlfred Mar 18 '13 at 12:52
@AceAlfred If the date is the same, does the `MyID` value have to be the same on each date? – Taryn Mar 18 '13 at 12:54
Yes, the MyID has to be the same on each date – AceAlfred Mar 18 '13 at 12:55
@AceAlfred If that is the case, then you should be able to use `dense_rank` instead of `row_number` -- see this demo -- http://www.sqlfiddle.com/#!3/01e74/7 – Taryn Mar 18 '13 at 12:58

score 0 · Answer 2 · answered Mar 18 '13 at 12:13

0

If your DBMS can calculate difference beetween two dates you can use the following

get minimum date

select min(date) from yourtable
update your id

update yourtable set myid = DBMS_SPECIFIC_FUNCTION_DATE_DIFF(min_date, date) + 1

answered Mar 18 '13 at 12:13

AnatolyS

4,249
18
28

score 0 · Answer 3 · answered Mar 18 '13 at 12:19

0

You don't state what RDBMS you are using. However, if it is MySQL, you could do this:

update yourtable set myid=extract(day from date);

This, of course, assuming that the MyID column should be equal to the day part of your date.

answered Mar 18 '13 at 12:19

NickJ

9,380
9
51
74

SQL - ID to date, multiple dates

3 Answers3