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
Comments
Post a Comment