CODEWITHSUNDEEP
oracle-databaseoracle11goracle-sqldeveloper
Henz D'Wraith
Henz D'Wraith

Reputation: 93

insert id number only in sql

I have a SQL Server table like this

+----+-----------+------------+
| id |  acoount  |    date    |
+----+-----------+------------+
|    |  John     |  2/6/2016  | 
|    |  John     |  2/6/2016  | 
|    |  John     |  4/6/2016  | 
|    |  John     |  4/6/2016  | 
|    |  Andi     |  5/6/2016  | 
|    |  Steve    |  4/6/2016  | 
+----+-----------+------------+

i want insert the id coloumn like this.

+-----------+-----------+------------+
|    id     |  acoount  |    date    |
+-----------+-----------+------------+
| 020616001 |  John     |  2/6/2016  | 
| 020616002 |  John     |  2/6/2016  | 
| 040616001 |  John     |  4/6/2016  | 
| 040616002 |  John     |  4/6/2016  | 
| 050616001 |  Andi     |  5/6/2016  | 
| 040616003 |  Steve    |  4/6/2016  | 
+-----------+-----------+------------+

I want to generate id number of the date provided like this. 02+06+16(from date)+001 = 020616001. if have same date, id + 1. I have tried but still failed . I want make it in oracle sql develop. Someone help me. Thanks.

Upvotes: 1

Views: 87

Answers (4)

KumarHarsh
KumarHarsh

Reputation: 5094

you cannot convert your dates column to datetime type in normal way because it is dd/mm/yyyy.

Try this,

declare @t table(acoount varchar(50),dates varchar(20))
insert into @t values
('John','2/6/2016') 
,('John','2/6/2016') 
,('John','4/6/2016') 
,('John','4/6/2016') 
,('Andi','5/6/2016') 
,('Steve','4/6/2016')

;With CTE as
(select * , SUBSTRING(dates,0,charindex('/',dates)) dd
,SUBSTRING(stuff(dates,1,charindex('/',dates),''),0, charindex('/',stuff(dates,1,charindex('/',dates),''))) MM
,right(dates,2) yy

 from @t 
)
,CTE1 as
(
select *
,ROW_NUMBER()over(partition by yy,mm,dd order by  yy,mm,dd)rn from cte c
)

select *, REPLICATE('0',2-len(dd))+cast(dd as varchar(2)) 
+REPLICATE('0',2-len(MM))+cast(MM as varchar(2))
+yy+REPLICATE('0',3-len(rn))+cast(rn as varchar(2))

from cte1

Upvotes: 0

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

MySql

i can give you the logic of 020616001 this part right now ....... for same id +1 i have to work on it....that i ll let u know after my work

  insert into table_name(id) 
  select concat
  (
    if(length (day(current_date))>1,day(current_date),Concat(0,day(current_date))),
    if(length (month(current_date))>1,month(current_date),Concat(0,month(current_date))),
    (right(year(current_date),2)),'001'
  )as id

Upvotes: 0

sandeep rawat
sandeep rawat

Reputation: 4957

Update your table using statement . 

update table set id= replace(CONVERT(VARCHAR(10),CONVERT(datetime ,date,103),3) ,'/', '') + Right('00'+convert(varchar(2),row_number()over(partition by account,[date] order by t.[date])) ,3)

Upvotes: 0

StackUser
StackUser

Reputation: 5398

Try the below SQL as per the given data, Its in SQL Server 2012....

select REPLACE(CONVERT(VARCHAR(10),convert(date,t.[date]), 101), '/', '')
+'00'+convert(varchar(2),row_number()over(partition by account,[date] order by t.[date])) as ID,
t.account,
t.date
from (values ('John','2/6/2016'), 
('John','2/6/2016'), 
('John','4/6/2016'), 
('John','4/6/2016'), 
('Andi','5/6/2016'), 
('Steve','4/6/2016'))T(account,[date])

Upvotes: 1

Related Questions