0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for enthusiasts and power users of the Android operating system

declare @dateto as date = convert(DATE, ##todate:string?1 Jan 2099##);
select  @dateto = case when @dateto > CONVERT(DATE,CURRENT_TIMESTAMP)
                       then CONVERT(DATE,CURRENT_TIMESTAMP)
                       else @dateto
                  end;
declare @datefrom as date = dateadd(month, - ##months:int?6##, @dateto);

with UserAct as (
    select CONVERT(DATE,CreationDate) as CalDate,
           count(*) as UserCount
    from Users
    where CreationDate between @datefrom and @dateto
    group by CONVERT(DATE,CreationDate)
    )
select out.CalDate as CalDate,
       out.UserCount as DayCount,
       sum (sub.UserCount) as WeekCount,
       count (distinct sub.CalDate) as WeekDays
from UserAct out,
     UserAct sub
where sub.CalDate between dateadd(DAY, -6, out.CalDate) and out.CalDate
group by out.CalDate, out.UserCount
order by out.CalDate

fork query

edited jan 31 14

created jan 31 14 rolfl