locked
Increment By Day RRS feed

  • Question

  • Hi Devs 

    is it possible to have records increment by 1 for the day and restart increment by 1 again the next day, like shown below 

    Increment  Name Date 
    1  Ben  2020/02/06
    2  Carl 2020/02/06
    3  Thomas 2020/02/06
    4  Bevan 2020/02/06
    5  Nicole 2020/02/06
    1  Sam  2020/02/07
    2  Ester 2020/02/07
    3  Benjamin 2020/02/07
    4  Ronald 2020/02/07
    5  Donavan 2020/02/07

    Let me know of your thoughts 

    Friday, February 7, 2020 6:04 AM

Answers

  • Hi Ronald,

    --an example
    create table #t(dt datetime)
    insert #t values('2020/02/06 01:00:00'),
                    ('2020/02/06 02:00:00'),
    				('2020/02/06 03:00:00'),
    				('2020/02/06 04:00:00'),
    				('2020/02/06 05:00:00'),
    				('2020/02/06 06:00:00'),
    				('2020/02/07 01:00:00'),
    				('2020/02/07 02:00:00'),
    				('2020/02/07 03:00:00')
    
    ;with cte1 as(
    select *,cast(dt as date) da,cast(dt as time) ti from #t)
    select row_number()over(partition by da order by ti) as Increment,
           dt
    from cte1
    
    /*
    Increment	dt
    1	2020-02-06 01:00:00.000
    2	2020-02-06 02:00:00.000
    3	2020-02-06 03:00:00.000
    4	2020-02-06 04:00:00.000
    5	2020-02-06 05:00:00.000
    6	2020-02-06 06:00:00.000
    1	2020-02-07 01:00:00.000
    2	2020-02-07 02:00:00.000
    3	2020-02-07 03:00:00.000 */

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, February 7, 2020 8:40 AM

All replies

  • Hi Ronald

    Use the ROW_NUMBER() function. You can partition over the Date column.

    Friday, February 7, 2020 6:17 AM
  • Hi,

    Is this what you are looking for?

    select * , row_number() over( partition by [Date] order by name ) Increment from T1

    Try it here http://sqlfiddle.com/#!18/0cbc8


    Satheesh





    Friday, February 7, 2020 6:21 AM
  • great thanks, 

    so the reason for me asking is because,im writing a code whereby for that day how many records were loaded, so since records gets loaded every hour, track how many records were loaded, so i understand where you coming from, now i want to dig deeper 

    hope this makes sense

    Friday, February 7, 2020 6:24 AM
  • then why not:

    select [Date], count(*) as [Records] from [table] group by [Date]

    this will count the entries by [Date].

    Friday, February 7, 2020 7:15 AM
  • Hi

    If you want to track the records hour wise ,then instead of date data type alter to datetime and in while condition can retrieve the records hour wise

    Friday, February 7, 2020 7:18 AM
  • Do you perhaps have a sample code for me to test it out if possible, im not familiar with while condition
    Friday, February 7, 2020 7:21 AM
  • Hi Ronald,

    --an example
    create table #t(dt datetime)
    insert #t values('2020/02/06 01:00:00'),
                    ('2020/02/06 02:00:00'),
    				('2020/02/06 03:00:00'),
    				('2020/02/06 04:00:00'),
    				('2020/02/06 05:00:00'),
    				('2020/02/06 06:00:00'),
    				('2020/02/07 01:00:00'),
    				('2020/02/07 02:00:00'),
    				('2020/02/07 03:00:00')
    
    ;with cte1 as(
    select *,cast(dt as date) da,cast(dt as time) ti from #t)
    select row_number()over(partition by da order by ti) as Increment,
           dt
    from cte1
    
    /*
    Increment	dt
    1	2020-02-06 01:00:00.000
    2	2020-02-06 02:00:00.000
    3	2020-02-06 03:00:00.000
    4	2020-02-06 04:00:00.000
    5	2020-02-06 05:00:00.000
    6	2020-02-06 06:00:00.000
    1	2020-02-07 01:00:00.000
    2	2020-02-07 02:00:00.000
    3	2020-02-07 03:00:00.000 */

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, February 7, 2020 8:40 AM