CODEWITHSUNDEEP
sqlsql-server-2014
nobody
nobody

Reputation: 11080

Count consecutive days when column type is int - SQL Server

I am trying to count consecutive days and I can't get the start date right.I suspect the issue is because the column datatype is int.How can I get the right start date?

I've gone through this post to count the consecutive days and here is my sql. Below is the table datapost with post_date and bytes with int datatype.I need to count the total consecutive days where bytes is < than 1024

Table

post_date   bytes  
20161220    22320
20161221    16905
20161222    26851
20161223    0
20161224    27676
20161225    26042
20161226    41461
20161227    23710
20161228    24179
20161229    27697
20161230    28703
20161231    26196
20170101    27887
20170102    27499
20170103    31053
20170104    31272
20170105    26330
20170106    20637
20170107    21375
20170108    24274
20170109    19679
20170110    20425
20170111    20105
20170112    23061
20170113    5310
20170114    0
20170115    0
20170116    0
20170117    0
20170118    0

SQL

;with cte as
(
        select post_date,
               bytes,
               startdate =  post_date - row_number()  over (order by post_date) 
        from  datapost
        where bytes < 1024
)
select 
    startdate = min(startdate),
    total = count(1)
from cte
group by startdate
order by startdate

My Output

startdate   total
20161222    1
20170112    5

Expected

startdate   total
20161223    1
20170114    5

EDIT If I do select * from cte I get

enter image description here

Finally got it to work using Gordon's script and correcting the select from cte.

Solution

;with cte as
(
        select post_date,
               bytes,
               startdate = dateadd(day,
                             - row_number() over (order by post_date),
                             cast(cast(post_date as varchar(255)) as date)
                            ) 
        from  @datapost
        where bytes < 1024
    )
    select min(post_date) startdate,count(post_date) total 
    from cte
    group by startdate
    order by startdate

Upvotes: 0

Views: 86

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You have the right logic, you just need to cast to a date. In your case, this is two casts -- first to a string then to a date:

with cte as (
      select post_date, bytes,
             startdate = dateadd(day,
                                 - row_number() over (order by post_date),
                                 cast(cast(post_date as varchar(255)) as date)
                                )
        from datapost
        where bytes < 1024
      )
select startdate = min(startdate), total = count(1)
from cte
group by startdate
order by startdate ;

I also changed the date arithmetic to use dateadd() rather than -. The latter works on datetime, but I don't think it works on dates.

Upvotes: 3

Related Questions