traceCodesStat

 select isnull(code, '') id,
        isnull(code, '') code,
        count(*) totalCount,
        isnull((select count(*) from as_trace t3 where t3.code=t1.code and cast(created as date) > cast(dateadd(day, -7, getdate()) as date) ), 0) [week],
        isnull((select count(*) from as_trace t3 where t3.code=t1.code and cast(created as date) = cast(dateadd(day, -1, getdate()) as date) ), 0) yesterday,
        isnull((select count(*) from as_trace t2 where t2.code=t1.code and cast(created as date) = cast(getdate() as date) ), 0) today,
        isnull((select count(*) from as_trace t2 where t2.code=t1.code and created >  dateadd(hour, -1, getdate()) ), 0) lastHour

    from as_trace t1
    group by code
    order by code