hecrus

traceCodesStat

Feb 11th, 2021
955
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  select isnull(code, '') id,
  2.         isnull(code, '') code,
  3.         count(*) totalCount,
  4.         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],
  5.         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,
  6.         isnull((select count(*) from as_trace t2 where t2.code=t1.code and cast(created as date) = cast(getdate() as date) ), 0) today,
  7.         isnull((select count(*) from as_trace t2 where t2.code=t1.code and created >  dateadd(hour, -1, getdate()) ), 0) lastHour
  8.    
  9.     from as_trace t1
  10.     group by code
  11.     order by code
RAW Paste Data