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