SQL- Number of times each item is ordered each day

I have a table that is a log of orders with the itemID and orderTime (in datetime). I know I can get the orders of specific items for a specific day like:

select 
   itemID, count(itemID) as Sep14 
from 
   tableName
where 
   itemID in (5, 6, 9)
   and orderTime between 'sep 14 2014 00:00:01' and 'sep 14 2014 23:59:59'
group by 
   itemID

What I would like to do is get the same results but over a longer time period with results in buckets of days so output would be similar to:

  • SQL Query to search schema of all tables
  • How Do I Insert A Byte Into an SQL Server VARBINARY column
  • When storing a datetime in sql server (datetime type), what format does it store it in?
  • SqlDataReader Index Out Of Range Exception when correct column count is returned
  • Retrieving count - LEFT JOIN VS SELECT COUNT
  • How do I use the results of a stored procedure from within another?
  • itemiD   Sep14   Sep15   Sep16
    ------   -----   -----   -----
    5        0       2       1
    6        3       3       0
    9        2       1       2
    

    Any help is much appreciated.

    2 Solutions collect form web for “SQL- Number of times each item is ordered each day”

    select itemID, convert(DATE, orderTime), count(*)
    from tableName
    where itemID in (5, 6, 9)
    and (orderTime > 'sep 14 2014 00:00:00' and orderTime < 'sep 21 2014 00:0:00')
    group by itemId, convert(DATE, orderTime)
    

    This will give you your results one row per day. If you try to get one column per day it’s not going to scale.

    This will create a dynamic pivot table that will give the output you want. It will create 1 column for each date in the date range for which there is data. If you want it to include dates with no data, you will need to join it to a calendar table that has all the dates.

    DECLARE @StartDate date = '20140101'
    DECLARE @EndDate date = '20140102'
    
    DECLARE @PivotColumnHeaders varchar(MAX)
    SELECT @PivotColumnHeaders =
      COALESCE(
        @PivotColumnHeaders + ',[' + CONVERT(varchar(10),uc.orderTime,110) + ']',
        '[' + CONVERT(varchar(10),uc.orderTime,110) + ']'
      )
    FROM (SELECT DISTINCT orderTime  FROM tablename WHERE orderTime BETWEEN @StartDate AND @EndDate) UC
    
    DECLARE @PQuery varchar(MAX) = '
    SELECT * FROM (SELECT ID, CAST(orderTime as date), Value FROM tablename T0 WHERE CAST(OrderTime as date) BETWEEN ''' + CONVERT(varchar(10),@StartDate,110) + ''' AND ''' + CONVERT(varchar(10),@EndDate,110) +''') T1
    PIVOT (COUNT([Value]) FOR CAST(orderTime as date) IN (' + @PivotColumnHeaders + ') ) AS P'
    EXECUTE (@PQuery)
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.