sql - Ordering dates for 4 weeks -

i writing sql query pulls information last 4 weeks. seeing 2 issues results (see below).

first problem when 4 weeks, range should august 10 - september 6. when order 'day of month', dates in september moved top of results when should @ end. results should start 10 (august) , end @ 6 (september).

second problem i'm missing few random dates (3, 4, 13, 27).

day of month    number of x 1                   125 2                   77 5                   5 6                   23 10                  145 11                  177 12                  116 14                  2 15                  199 16                  154 17                  134 18                  140 19                  154 21                  8 22                  166 23                  145 24                  151 25                  107 26                  79 28                  3 29                  151 30                  163 31                  147 

here general version of query:

declare @startdate datetime, @enddate datetime; set @startdate = dateadd(day, -28, getdate()); set @enddate = dateadd(day, -1, getdate());  select datepart(dd, time) 'day of month', count(*) ' number of x'  sometable st time >= @startdate , time <  @enddate  group datepart(dd, time)    order 'day of month'   

for first problem can order date correct date order. use convert time-free date entries group correctly.

declare @startdate datetime, @enddate datetime; set @startdate = dateadd(day, -28, getdate()); set @enddate = dateadd(day, -1, getdate());  select  datepart(dd, convert(date, time)) 'day of month', count(*) ' number of x'  sometable st time >= @startdate , time <  @enddate  group convert(date, time)  order convert(date, time)  

as missing days, more complicated data needs there group-by work.

one option create temporary table dates in, join in data. still leave row join not find data, , can "zero" count.

declare @startdate datetime, @enddate datetime; set @startdate = dateadd(day, -28, getdate()); set @enddate = dateadd(day, -1, getdate());  --create temporary table days between 2 dates ;with d(d)  (   select dateadd(day, n, dateadd(day, datediff(day, 0, @startdate), 0))   ( select top (datediff(day, @startdate, @enddate) + 1)      n = row_number() on (order [object_id]) - 1     sys.all_objects order [object_id] ) n ) --join in our query table temporary table select       datepart(dd, d.d) 'day of month',      count(time) ' number of x'  d left outer join sometable st on datepart(dd, d.d) = datepart(dd, time)                                , time >= @startdate                                , time <  @enddate  group d.d order d.d