Skip to main content

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


Comments

Post a Comment

Popular posts from this blog

WPF datagrid cell textbox change event

Entity/Class: public class FeesDetails : INotifyPropertyChanged { public int Id { get; set; } public string FeesName { get; set;} public string FeesDetailsName { get; set; } public int? PaidAmount { get; set; } public event PropertyChangedEventHandler PropertyChanged; private void NotifyPropertyChanged(System.String info) { if (PropertyChanged != null) { PropertyChanged(this, new PropertyChangedEventArgs(info)); } } public int feesAmount { get; set; } public int FeesAmount { get { return this.feesAmount; } set { if (value != this.feesAmount) { this.feesAmount = value; NotifyPropertyChanged("FeesAmount"); } } } } XAML: <DataGrid AutoGenerateColumns="False" Height="21...
Post a Comment
Read more

mvc razor textboxfor change event change another textboxfor value

Based on value of Weight, Rate , CNF & AWB it will change the value of Freight , TTLCNF anfd TTLFright . Freight= Weight*Rate; TTLCNF  = Weight*CNF; TTLFright=  Freight+ TTLCNF  + AWB; @Html.TextBoxFor(model => model.Weight, new { onChange="return GetWight(this);"}) @Html.TextBoxFor(model => model.Rate, new { onChange="return GetWight(this);"})/Kg @Html.TextBoxFor(model => model.Freight, new {disabled = "disabled" , @readonly = "readonly" ,onChange="return GetTTLFright(this);"}) @Html.TextBoxFor(model => model.CNFPK, new { onChange="return GetCNFPK(this);"}) @Html.TextBoxFor(model => model.TTLCNF, new {disabled = "disabled" , @readonly = "readonly",onChange="return GetTTLFright(this);" }) @Html.TextBoxFor(model => model.AWB, new { onChange="return GetTTLFright(this);"}) and script <script> function GetW...
Post a Comment
Read more

mvvm double click event in listview

If you want to get the double click event on a listview item you can try with this code; <ListView Grid.Row="0" Grid.RowSpan="3" Grid.Column="0" Width="250" Height="200" HorizontalAlignment="Stretch" VerticalAlignment="Top" AlternationCount="2" BorderBrush="#FFA8CC7B" ItemContainerStyle="{StaticResource alternatingStyle}" ItemsSource="{Binding FromPayerNameList}" SelectedItem="{Binding SelectedFromPayer, Mode=TwoWay}"> <ListView.ItemTemplate> <DataTemplate> <TextBlock Width="{Binding Path=ActualWidth, RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type ListView}}}" Text=...
Post a Comment
Read more