locked
daynumber auto increment daily RRS feed

  • Question

  • User1513962562 posted

    i want to add a column "daynumber" which auto increment daily by checking the date ..like below

    Daynumber                 Date                   Details

    1                              11/04/2016             test
    1                              11/04/2016             test
    2                              12/04/2016             testing
    2                              12/04/2016             testing
    3                              13/04/2016             testing test
    3                              13/04/2016             testing test

    Monday, April 11, 2016 6:55 AM

Answers

  • User1401801381 posted
    Hi,
    You can do that with an auto-computed column. but you need to define a reference date (the day "zero")
    Here is a sample:
    create table #temp
    (
    daynumber as datediff(DAY, '20160410', [Date]), -- day zero
    [date] datetime,
    details nvarchar(max)
    )

    insert into #temp ([date], details) values('20160411','test')
    insert into #temp ([date], details) values('20160411','test')
    insert into #temp ([date], details) values('20160412','testing')
    insert into #temp ([date], details) values('20160412','testing')
    insert into #temp ([date], details) values('20160413','testing test')
    insert into #temp ([date], details) values('20160413','testing test')

    select * from #temp

    drop table #temp
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 11, 2016 7:35 AM

All replies

  • User1401801381 posted
    Hi,
    You can do that with an auto-computed column. but you need to define a reference date (the day "zero")
    Here is a sample:
    create table #temp
    (
    daynumber as datediff(DAY, '20160410', [Date]), -- day zero
    [date] datetime,
    details nvarchar(max)
    )

    insert into #temp ([date], details) values('20160411','test')
    insert into #temp ([date], details) values('20160411','test')
    insert into #temp ([date], details) values('20160412','testing')
    insert into #temp ([date], details) values('20160412','testing')
    insert into #temp ([date], details) values('20160413','testing test')
    insert into #temp ([date], details) values('20160413','testing test')

    select * from #temp

    drop table #temp
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 11, 2016 7:35 AM
  • User77042963 posted
    select  dense_rank()Over (order by [date]) as Daynumber,  Date ,Details 
    from  yourtable

    Monday, April 11, 2016 2:01 PM