Monday, March 20, 2017

T-SQL row Count by hourly range on datetime field

If you want to row count hourly based on date range like this:



You can try with this SQL:


  ;with mycte as (Select case when datepart(hour, tprocesstime) between 0 and 8 then 0   
           when datepart(hour, tprocesstime) >= 21 then 21  
           else datepart(hour, tprocesstime) end as OnHour  
                          , * From profile)   
 Select Case OnHour when 0 then '0AM-9AM'  
              when 9 then '9AM-10AM'  
              when 10 then '10AM-11AM'  
              when 11 then '11AM-12PM'  
              when 12 then '12PM-1PM'  
              when 13 then '1PM-2PM'  
              when 14 then '2PM-3PM'  
              when 15 then '3PM-4PM'  
              when 16 then '4PM-5PM'  
              when 17 then '5PM-6PM'  
              when 18 then '6PM-7PM'  
              when 19 then '7PM-8PM'  
              when 20 then '8PM-9PM'  
              when 21 then 'Above 9PM' end OnHourRange,  
  SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Sun' THEN 1 else 0 end ) as [Sun]  
 ,SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Mon' THEN 1 else 0 end ) as [Mon]  
 ,SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Tue' THEN 1 else 0 end ) as [Tue]  
 ,SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Wed' THEN 1 else 0 end ) as [Wed]  
 ,SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Thu' THEN 1 else 0 end ) as [Thu]  
 ,SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Fri' THEN 1 else 0 end ) as [Fri]  
 ,SUM(CASE WHEN left(datename(dw, tprocesstime), 3)='Sat' THEN 1 else 0 end ) as [Sat]  
  FROM mycte   
  Group by OnHour   


Ensure that this project has Microsoft.Bcl.Build installed and packages.config is located next to the project file

In my Xamarin form application their was dependency Restclient on Microsoft.Bcl.Build Microsoft.Bcl. i just remove all using nuget and ...