SQL查询问题,版本2008r2

左侧为数据库表结构,右侧是按照2个不同的查询条件,输出想要的结果,怎样写出SQL语句.
根据所给的时间范围,判断最小日期的isNew是"是"还是"否",如果为"是",则最大日期所对应的isNew为"是",否则为"否".

 select str_name, min(createdate), (select isnew from table where createdate=min(create) and str_name=str_name) as isnew from table where createdate between 日期开始 and 日期结束 group by str_name