title: Generate Pivot in SQL (Sales per department by hourly slots)
body: <p>I am trying to generate a Pivot in SQL but unable to do so</p>
  
  <p>****This is what I am try to achieve :****</p>
  
  <p>Determine how much was the total sales value (Sum of Value) per Department(1-99) during a given period,based on time intervals(1 hour).</p>
  
  <p><strong>Note</strong>
  Some days the Store opens past midnight and we need to capture the sales everyday for the next day until 2am
  How do i include this in my query</p>
  
  <p>This is the PIVOT expected
  <a href="https://imgur.com/a/p00FlAT" rel="nofollow noreferrer">https://imgur.com/a/p00FlAT</a></p>
  
  <p>This is the Data Generated
  <a href="https://imgur.com/a/A7hYJXi" rel="nofollow noreferrer">https://imgur.com/a/A7hYJXi</a></p>
  
  <p>From the Data Generated I need to write a query to build the PIVOT</p>
  
  <p><strong>This is my Query</strong></p>
  
  <pre><code>select * from
  (
  SELECT  
  case 
  when [Time] between '1754-01-01 07:00:00.000' and '1754-01-01 07:59:59.999' then '07am - 07:59am'
  when [Time] between '1754-01-01 08:00:00.000' and '1754-01-01 08:59:59.999' then '08am - 08:59am'
  when [Time] between '1754-01-01 09:00:00.000' and '1754-01-01 09:59:59.999' then '09am - 09:59am'
  when [Time] between '1754-01-01 10:00:00.000' and '1754-01-01 10:59:59.999' then '10am - 10:59am'
  when [Time] between '1754-01-01 11:00:00.000' and '1754-01-01 11:59:59.999' then '11am - 11:59am'
  when [Time] between '1754-01-01 12:00:00.000' and '1754-01-01 12:59:59.999' then '12pm - 12:59pm'
  when [Time] between '1754-01-01 13:00:00.000' and '1754-01-01 13:59:59.999' then '13pm - 13:59pm'
  when [Time] between '1754-01-01 14:00:00.000' and '1754-01-01 14:59:59.999' then '14pm - 14:59pm'
  when [Time] between '1754-01-01 15:00:00.000' and '1754-01-01 15:59:59.999' then '15pm - 15:59pm'
  when [Time] between '1754-01-01 16:00:00.000' and '1754-01-01 16:59:59.999' then '16pm - 16:59pm'
  when [Time] between '1754-01-01 17:00:00.000' and '1754-01-01 17:59:59.999' then '17pm - 17:59pm'
  when [Time] between '1754-01-01 18:00:00.000' and '1754-01-01 18:59:59.999' then '18pm - 18:59pm'
  when [Time] between '1754-01-01 19:00:00.000' and '1754-01-01 19:59:59.999' then '19pm - 19:59pm'
  when [Time] between '1754-01-01 20:00:00.000' and '1754-01-01 20:59:59.999' then '20pm - 20:59pm'
  when [Time] between '1754-01-01 21:00:00.000' and '1754-01-01 21:59:59.999' then '21pm - 21:59pm'
  when [Time] between '1754-01-01 22:00:00.000' and '1754-01-01 22:59:59.999' then '22pm - 22:59pm'
  when [Time] between '1754-01-01 23:00:00.000' and '1754-01-01 23:59:59.999' then '23pm - 23:59pm'
  when [Time] between '1754-01-01 00:00:00.000' and '1754-01-01 00:59:59.999' then '24am - 24:59am'
  
  End [Time Slot],
  [Company A$Sales Entries].[Total Rounded Amt_]*-1 ,
  [Company A$Item].[Department No_] as Department
  FROM            [Company A$Sales Entries] INNER JOIN
                           [Company A$Item] ON [Company A$Sales Entries].[Item No_] = [Company A$Item].No_
  WHERE        [Company A$Sales Entries].Date BETWEEN '2017-06-05' AND  '2017-06-06'
  and [Company A$Item].[Department No_] in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,50,60,92,93,94,95,96,97,99)
  
  and [POS Terminal] in ('01','02','03','04','05','06','07','08','09','10','11','12')
  
  ))as P
  
          PIVOT (SUM([Company A$Sales Entries].[Total Rounded Amt_])
           for Department in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[50],[60],[92],[93],[94],[95],[96],[97],[99]
                                    )as PVT;
  </code></pre>