SQL- Number of times each item is ordered each day
I have a table that is a log of orders with the
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:
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)