[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to modify SELECT query to return time interval results from @start to @end?

Posted on 2011-10-17
7
Medium Priority
?
244 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:Danzigger
  • 3
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36978836
you add something like this to the where clause:

AND date_added >= @start
 AND date_added < @end  --- you might need <= @end, depends on the input data vs info needed vs data stored ...

0
 

Author Comment

by:Danzigger
ID: 36982745
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!
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37001282
The original code is starting all intervals at midnight.  So, for 45 mins, the intervals would be:

00:45, 01:30, 02:15, ..., 15:45, 16:30, ...

If you need to start at a particular time, that time has to be passed into the code and the code adjusted to handle what you want.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Danzigger
ID: 37003628
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.
0
 

Author Comment

by:Danzigger
ID: 37004440
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.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37009371
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?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37009375
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.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question