Danzigger
asked on
How to modify SELECT query to return time interval results from @start to @end?
Hi
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!
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!
ASKER
Thanks angellll. That doesn't seems to make any difference though - still returns results starting with interval 4:30pm for sales data entered starting at 4:25pm regardless of start/end times on sales amounts.
Via the table joins the sales data is related to a particular promotion so the sales data is always between the start and end times of that promotion.
I need some way to have the results contain every interval from promotion start to promotion end (start and end datetimes are fields in the [promotions] table) but the amount would be null or zero if no sales were inserted for a particular interval.
For a promotion that started at 2pm and ended at 8pm, at the moment I'm getting these results:
interval amount
2011-09-16 16:30:00 259
2011-09-16 17:15:00 342
2011-09-16 18:00:00 8
But I want the results to be:
interval amount
2011-09-16 14:00:00 0
2011-09-16 14:45:00 0
2011-09-16 15:15:00 0
2011-09-16 16:00:00 0
2011-09-16 16:45:00 259
2011-09-16 17:30:00 342
2011-09-16 18:00:00 8
2011-09-16 18:45:00 0
2011-09-16 19:30:00 0
How would you modify the query to get these results?
Thanks a lot!
Via the table joins the sales data is related to a particular promotion so the sales data is always between the start and end times of that promotion.
I need some way to have the results contain every interval from promotion start to promotion end (start and end datetimes are fields in the [promotions] table) but the amount would be null or zero if no sales were inserted for a particular interval.
For a promotion that started at 2pm and ended at 8pm, at the moment I'm getting these results:
interval amount
2011-09-16 16:30:00 259
2011-09-16 17:15:00 342
2011-09-16 18:00:00 8
But I want the results to be:
interval amount
2011-09-16 14:00:00 0
2011-09-16 14:45:00 0
2011-09-16 15:15:00 0
2011-09-16 16:00:00 0
2011-09-16 16:45:00 259
2011-09-16 17:30:00 342
2011-09-16 18:00:00 8
2011-09-16 18:45:00 0
2011-09-16 19:30:00 0
How would you modify the query to get these results?
Thanks a lot!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah, thanks Scott - didn't notice that!! So if I replace all three occurrences of GETDATE() with promotions.start_date - that should do it, right?
I realise it will still only return rows where sales actually occurred in that interval, but the times of those rows will then be in sync with the web server calculated intervals - yeah?
Thanks a lot.
I realise it will still only return rows where sales actually occurred in that interval, but the times of those rows will then be in sync with the web server calculated intervals - yeah?
Thanks a lot.
ASKER
Hmmm, still has the same problem in that if the first sales record is at 4:25pm then the result set starts counting in 45 min increments from 4:30pm.
Here's the query with variable @promotionStart declared for the interval start time:
==========================
SELECT CONVERT (varchar(19), DATEADD(mi, interval * @minutesInterval, CONVERT (int, @promotionStart)), 121) AS interval, amount FROM (SELECT DATEDIFF(mi, CONVERT (int, @promotionStart), 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, @promotionStart), sales.date_added) / @minutesInterval) AS salesData ORDER BY interval
==========================
Still produces:
interval amount
2011-09-16 16:30:00 259
2011-09-16 17:15:00 342
2011-09-16 18:00:00 8
Ideally I'd like:
interval amount
2011-09-16 14:00:00 0
2011-09-16 14:45:00 0
2011-09-16 15:15:00 0
2011-09-16 16:00:00 0
2011-09-16 16:45:00 259
2011-09-16 17:30:00 342
2011-09-16 18:00:00 8
2011-09-16 18:45:00 0
2011-09-16 19:30:00 0
Any other ideas? Thanks.
Here's the query with variable @promotionStart declared for the interval start time:
==========================
SELECT CONVERT (varchar(19), DATEADD(mi, interval * @minutesInterval, CONVERT (int, @promotionStart)), 121) AS interval, amount FROM (SELECT DATEDIFF(mi, CONVERT (int, @promotionStart), 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, @promotionStart), sales.date_added) / @minutesInterval) AS salesData ORDER BY interval
==========================
Still produces:
interval amount
2011-09-16 16:30:00 259
2011-09-16 17:15:00 342
2011-09-16 18:00:00 8
Ideally I'd like:
interval amount
2011-09-16 14:00:00 0
2011-09-16 14:45:00 0
2011-09-16 15:15:00 0
2011-09-16 16:00:00 0
2011-09-16 16:45:00 259
2011-09-16 17:30:00 342
2011-09-16 18:00:00 8
2011-09-16 18:45:00 0
2011-09-16 19:30:00 0
Any other ideas? Thanks.
Sorry for the delay. I'm **extremely** busy at work, just do this for a quick break (well, for a db geek it's a break :-) ).
What is the value of @promotionStart, specifically?
What is the value of @promotionStart, specifically?
I try to access this site from at home but the Question screens keep flashing on the screen and then going blank.
It's **VERY** frustrating; makes it impossible for me to follow up later in the day when I'm free.
It's **VERY** frustrating; makes it impossible for me to follow up later in the day when I'm free.
AND date_added >= @start
AND date_added < @end --- you might need <= @end, depends on the input data vs info needed vs data stored ...