title: Pulling out monthly daypart data
body: <p>I'm dealing with sales data that looks like this</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">Date</th>
  <th style="text-align: center;">Time</th>
  <th style="text-align: center;">Net Amount</th>
  <th style="text-align: center;">Store No</th>
  <th style="text-align: center;">Item Category Code</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">2021-03-21 00:00:00.000</td>
  <td style="text-align: center;">1754-01-01 08:01:14.627</td>
  <td style="text-align: center;">100</td>
  <td style="text-align: center;">001</td>
  <td style="text-align: center;">FOOD</td>
  </tr>
  <tr>
  <td style="text-align: center;">2021-01-31 00:00:00.000</td>
  <td style="text-align: center;">1754-01-01 15:42:21.670</td>
  <td style="text-align: center;">120</td>
  <td style="text-align: center;">002</td>
  <td style="text-align: center;">FOOD</td>
  </tr>
  <tr>
  <td style="text-align: center;">2021-04-24 00:00:00.000</td>
  <td style="text-align: center;">1754-01-01 17:35:30.070</td>
  <td style="text-align: center;">160</td>
  <td style="text-align: center;">002</td>
  <td style="text-align: center;">FOOD</td>
  </tr>
  <tr>
  <td style="text-align: center;">2021-03-14 00:00:00.000</td>
  <td style="text-align: center;">1754-01-01 13:08:02.073</td>
  <td style="text-align: center;">150</td>
  <td style="text-align: center;">003</td>
  <td style="text-align: center;">FOOD</td>
  </tr>
  <tr>
  <td style="text-align: center;">2021-02-27 00:00:00.000</td>
  <td style="text-align: center;">1754-01-01 18:33:53.847</td>
  <td style="text-align: center;">95</td>
  <td style="text-align: center;">001</td>
  <td style="text-align: center;">FOOD</td>
  </tr>
  <tr>
  <td style="text-align: center;">2021-02-20 00:00:00.000</td>
  <td style="text-align: center;">1754-01-01 21:30:24.007</td>
  <td style="text-align: center;">180</td>
  <td style="text-align: center;">002</td>
  <td style="text-align: center;">DRINK</td>
  </tr>
  <tr>
  <td style="text-align: center;">2021-04-09 00:00:00.000</td>
  <td style="text-align: center;">1754-01-01 07:36:33.930</td>
  <td style="text-align: center;">110</td>
  <td style="text-align: center;">003</td>
  <td style="text-align: center;">FOOD</td>
  </tr>
  <tr>
  <td style="text-align: center;">2021-01-29 00:00:00.000</td>
  <td style="text-align: center;">1754-01-01 08:02:49.703</td>
  <td style="text-align: center;">180</td>
  <td style="text-align: center;">002</td>
  <td style="text-align: center;">FOOD</td>
  </tr>
  <tr>
  <td style="text-align: center;">2021-01-24 00:00:00.000</td>
  <td style="text-align: center;">1754-01-01 11:01:00.953</td>
  <td style="text-align: center;">110</td>
  <td style="text-align: center;">003</td>
  <td style="text-align: center;">FOOD</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What I need is to pull out monthly data by daypart that would result to this</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;"></th>
  <th style="text-align: center;">Jan 2021</th>
  <th style="text-align: center;">Feb 2021</th>
  <th style="text-align: center;">Mar 2021</th>
  <th style="text-align: center;">Apr 2021</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">4am to 10:59am</td>
  <td style="text-align: center;">180</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">100</td>
  <td style="text-align: center;">110</td>
  </tr>
  <tr>
  <td style="text-align: center;">11am to 1:59pm</td>
  <td style="text-align: center;">110</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">150</td>
  <td style="text-align: center;">0</td>
  </tr>
  <tr>
  <td style="text-align: center;">2pm to 3:59am</td>
  <td style="text-align: center;">120</td>
  <td style="text-align: center;">95</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">160</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Here's the query I'm currently using:</p>
  <pre><code>with FoodSales as
  (
      select distinct
             concat(year(s.[Date]),'',datename(month,s.[Date])) as TransMonth
             ,sum( s.[Net Amount]*-1) as Sales
  
      from [dbo].[vw_Sales Entry] as s
  
      left join [dbo].[vw_Discount Entry] as d
            on s.[Receipt No_]=d.[Receipt No_] 
  
  
      where s.[Date] &gt;= '2021-01-01 04:00:00' and s.[Date] &lt;= '2021-04-30 10:59:59'
            and s.[Item Category Code]='FOOD'
            and (d.[Discount Name] is null or d.[Discount Name]='')
           
  
      group by 
             concat(year(s.[Date]),'',datename(month,s.[Date]))
  
  )
  
      select * 
      from FoodSales
      pivot( sum(Sales)
      for TransMonth in ([2021January]
                         ,[2021February]
                         ,[2021March]
                         ,[2021April]
  
        )
        ) as p
  
  
  ;
  
  </code></pre>
  <p>I've also tried using</p>
  <pre><code>[Date] between '2021-01-01 04:00:00' and '2021-04-30 10:59:59'
  </code></pre>
  <p>but when both are tried with another interval, the sales amount just comes out the same.</p>