select Top 9 case when datefield < dateadd(day,-day(datefield) + 16 ,datefield)
then dateadd(day,-day(datefield) + 1 ,datefield)
else dateadd(day,-day(datefield) + 16 ,datefield) end as F
,count(*)
from dt
--下面是关键
group by case when datefield < dateadd(day,-day(datefield) + 16 ,datefield)
then dateadd(day,-day(datefield) + 1 ,datefield)
else dateadd(day,-day(datefield) + 16 ,datefield) end
order by F desc
select top 9 case
when datefield < dateadd(day, -day(datefield) + 16 ,datefield)
then dateadd(day,-day(datefield) + 1 ,datefield)
else dateadd(day,-day(datefield) + 16 ,datefield)
end as a, count(*)
from dt
group by case
when datefield < dateadd(day, -day(datefield) + 16 ,datefield)
then dateadd(day,-day(datefield) + 1 ,datefield)
else dateadd(day,-day(datefield) + 16 ,datefield)
end
order by a desc
declare @t table(d datetime)
insert into @t select '2003-10-19 15:00:00'
union select '2003-10-01 15:00:00'
union select '2003-10-02 15:00:00'
union select '2003-10-20 15:00:00'
select * from @t where datediff(month,d,getdate())<=5 and day(d)<=15 union all
select * from @t where datediff(month,d,getdate())<=5 and day(d)>15
select count(*)
from dt
group by case when datefield < dateadd(day,-day(datefield) + 16 ,datefield) then dateadd(day,-day(datefield) + 1 ,datefield)
else dateadd(day,-day(datefield) + 16 ,datefield) end