I have created a view that provides a count on the number of orders placed each hour, on a particular day. I am then accessing the results of this view in Microsoft Excel via odbc. Below is the code for the view;SELECT CASE WHEN DATEPART(hour, audit.time) = '7' THEN '7AM TO 8AM' WHEN DATEPART(hour, audit.time) = '8' THEN '8AM TO 9AM' WHEN DATEPART(hour, audit.time) = '9' THEN '9AM TO 10AM' WHEN DATEPART(hour, audit.time) = '10' THEN '10AM TO 11AM' WHEN DATEPART(hour, audit.time) = '11' THEN '11AM TO 12PM' WHEN DATEPART(hour, audit.time) = '12' THEN '12PM TO 1PM' WHEN DATEPART(hour, audit.time) = '13' THEN '1PM TO 2PM' WHEN DATEPART(hour, audit.time) = '14' THEN '2PM TO 3PM' WHEN DATEPART(hour, audit.time) = '15' THEN '3PM TO 4PM' WHEN DATEPART(hour, audit.time) = '16' THEN '4PM TO 5PM' WHEN DATEPART(hour, audit.time) = '17' THEN '5PM TO 6PM' WHEN DATEPART(hour, audit.time) = '18' THEN '6PM TO 7PM' ELSE CAST(DATEPART(hour, audit.time) AS VARCHAR) END AS [Hour Range], scheme.opheadm.date_entered, COUNT(scheme.opheadm.order_no) AS [Orders Entered]FROM scheme.opheadm INNER JOIN scheme.slcustm ON scheme.opheadm.customer = scheme.slcustm.customer LEFT OUTER JOIN (SELECT audit_key, audit_column, audit_time, CAST(ABS(CAST(audit_time AS int) / 3600) AS varchar(2)) + ':' + RIGHT('00' + CAST(ABS((CAST(audit_time AS int) - 3600 * ABS(CAST(audit_time AS int) / 3600)) / 60) AS varchar(2)), 2) AS time FROM scheme.opaudm AS opaudm_1 WHERE (audit_event = 'C') AND (audit_column = 'status')) AS audit ON scheme.opheadm.order_no = audit.audit_keyWHERE (NOT (scheme.opheadm.order_no LIKE 'C%')) AND (scheme.opheadm.status <> '9') AND (scheme.opheadm.status <> 'Q')GROUP BY CASE WHEN DATEPART(hour, audit.time) = '7' THEN '7AM TO 8AM' WHEN DATEPART(hour, audit.time) = '8' THEN '8AM TO 9AM' WHEN DATEPART(hour, audit.time) = '9' THEN '9AM TO 10AM' WHEN DATEPART(hour, audit.time) = '10' THEN '10AM TO 11AM' WHEN DATEPART(hour, audit.time) = '11' THEN '11AM TO 12PM' WHEN DATEPART(hour, audit.time) = '12' THEN '12PM TO 1PM' WHEN DATEPART(hour, audit.time) = '13' THEN '1PM TO 2PM' WHEN DATEPART(hour, audit.time) = '14' THEN '2PM TO 3PM' WHEN DATEPART(hour, audit.time) = '15' THEN '3PM TO 4PM' WHEN DATEPART(hour, audit.time) = '16' THEN '4PM TO 5PM' WHEN DATEPART(hour, audit.time) = '17' THEN '5PM TO 6PM' WHEN DATEPART(hour, audit.time) = '18' THEN '6PM TO 7PM' ELSE CAST(DATEPART(hour, audit.time) AS VARCHAR) END, scheme.opheadm.date_enteredHAVING (scheme.opheadm.date_entered = CONVERT(DATETIME, '2012-03-26 00:00:00', 102))The date has been manually entered, for this post but is a variable that is controlled on the excel sheet. This view works fine for a single date but I have now been tasked to make this work for a range. As it stands this view will work but will keep the times for each day in the range seperate but I would like to get them all rolled into one entry per time period.Please can you suggest a modifications to my view to do this or an alternative method.Many thanksPaul
View Creation
Posted - 2012-03-28 : 13:36:36
