How to modify SELECT query to return time interval results from @start to @end?
Posted on 2011-10-17
I have a query (below) which I've put together based on help from a clever EE member. It gets sales amounts at given time intervals for a particular promotion. It works, but it only gets the intervals where sales actually occurred. That's ok most of the time because my web app loops through all the intervals from promotion start to promotion end and assumes the amount is zero if it doesn't find a record from the query results matching a particular time interval.
But for some reason if I set the @minutesInterval to something like 45 problems arise if the first sales data was inserted at say 4:25pm. In those cases the first record returned is for interval 4:30pm (I don't get why) and then the next one is 5:15pm etc. If that particular promotion started at 2pm then the web app is looking for intervals at 2pm, 2:45pm, 3:30pm, 4:15pm, 5pm etc and so it never finds a match because the SQL results are 4:30pm, 5:15pm etc - they're never in sync.
I'm open to suggestion about the best way to resolve this, but it seems that it'd work fine if the SQL query returned a result for every interval from promotion start to promotion end even if the amount was 0 (or null) - then the intervals of the web app and SQL results would always be the same.
How would you modify the query below to resolve this?
SELECT CONVERT (varchar(19), DATEADD(mi, interval * @minutesInterval, CONVERT (int, GETDATE())), 121) AS interval, amount FROM (SELECT DATEDIFF(mi, CONVERT (int, GETDATE()), sales.date_added) / @minutesInterval AS interval, SUM(sales.amount) AS amount FROM sales INNER JOIN employees ON sales.EmployeeId = employees.ID INNER JOIN Terminals ON employees.TerminalId = Terminals.Id INNER JOIN Departments ON Terminals.DepartmentId = Departments.ID INNER JOIN Promotions ON Departments.PromotionId = Promotions.ID WHERE (Departments.PromotionId = @PromotionId) AND (sales.amount > 0) GROUP BY DATEDIFF(mi, CONVERT (int, GETDATE()), sales.date_added) / @minutesInterval) AS salesData ORDER BY interval
The sales table data looks like eg:
ID amount date_added employeeID
2915 10 2011-10-11 23:15:49.083 52
2916 45 2011-10-11 23:16:09.077 47
2917 156 2011-10-11 23:16:19.080 19
2918 87 2011-10-11 23:16:29.070 52
2919 15 2011-10-11 23:17:19.073 24
Thanks in advance!