erikTsomik
asked on
sql query help
I Need to find records when less than 24 hours left till start of the session
SELECT DateAdd(HOUR,24,GETDATE()),SB.ID,SB.sessionKey,SU.sessionStart,SU.sessionEnd,Su.instructorKey,L.name,SB.userKey,
I.lastname,I.firstname,I.email,SUBSTRING(replace(i.cellphone,'-',''),1,10) as cellphone,lc.carrieremail as InsCarEmail
FROM sessionBuyOut SB
inner join sessionUnit SU on SU.sessionKey = SB.SessionKey
inner join session S on S.sessionKey = SU.sessionKey
inner join location L on L.locationKey = S.locationKey
inner join instructors I on I.instructorKey = SU.instructorKey
inner join lkup_carrier lc on i.carrierkey = lc.carrierkey
where SU.sessionStart between dateadd(day,datediff(day,1,GETDATE()),0) AND dateadd(hour,24,dateadd(day,datediff(day,0,GETDATE()),0))
What's your current SELECT returning so we can know what's wrong or missing?
ASKER
Here is the return. What I need to return is record is ID 2 because it is less that 24 hours left till the session start.
ID sessionKey sessionStart sessionEnd
1 225230 2017-03-17 17:00:00.000 2017-03-17 19:00:00.000
2 225228 2017-03-17 10:00:00.000 2017-03-17 12:00:00.000
Using between is not a good idea. I'll explain later.
Where clause something like
Not tested, but I *think* this should do it.
Where clause something like
where sessionStart < getdate() + 1 and sessionStart >= getdate()
Not tested, but I *think* this should do it.
Here is the problems of using BETWEEN in SQL
https://www.experts-exchange.com/articles/11210/Beware-of-Between.html
https://www.experts-exchange.com/articles/11210/Beware-of-Between.html
The problem is that your BETWEEN clause only tests for the begin of the day. The time part is set to 00:00:00.
So your 2017-03-17 10:00:00.000 is excluded cause you're filtering for between 2017-03-15 00:00:00.000 and 2017-03-17 00:00:00.000. And 10 o'clock is outside that boundary.
This should work:
So your 2017-03-17 10:00:00.000 is excluded cause you're filtering for between 2017-03-15 00:00:00.000 and 2017-03-17 00:00:00.000. And 10 o'clock is outside that boundary.
This should work:
WHERE SU.sessionStart >= DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0)
AND SU.sessionStart < DATEADD(HOUR, 24, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() + 1), 0));
ASKER
Ideally I would like to have less than 24 hours but not grater that 23 hours
For Example,
if my session Starts at 2017-03-17 13:00:00.000 Then 24 hours will be 2017-03-16 13:00:00.000 and 23 hours will be 2017-03-17 14:00:00.000. If I can specify this range then I will not retrieve this record again withing 1 hour time. I am planing on scheduling this job to run every hour
For Example,
if my session Starts at 2017-03-17 13:00:00.000 Then 24 hours will be 2017-03-16 13:00:00.000 and 23 hours will be 2017-03-17 14:00:00.000. If I can specify this range then I will not retrieve this record again withing 1 hour time. I am planing on scheduling this job to run every hour
where datediff(mi, getdate(), session_start) / 1440 < 1
WHERE session_start >= DATEADD(HOUR, -23, GETDATE()) AND session_start <= GETDATE()
ASKER
@Scott Pletcher. sessionStart is in the future date. I am sending reminders.
Here is the data sample
Here is the data sample
ID sessionKey sessionStart sessionEnd
1 225230 2017-03-17 17:00:00.000 2017-03-17 19:00:00.000
2 225228 2017-03-17 10:00:00.000 2017-03-17 12:00:00.000
You can subtract dates. The result will be in days so 24h is 1 day:
WHERE SU.sessionStart-GETDATE() BETWEEN 0 AND 1
ASKER
Vitor Montalvão I dont get BETWEEN 0 and 1. The result from SU.sessionStart-GETDATE() is a dateTime Format .
The result from SU.sessionStart-GETDATE() is a dateTime Format .Yes. The number of days passed from 1900. Between 0 and 1 is to assure that you'll get only positive values (future date) and that not returning records more that 1 day in the future, so the 24h period that you required.
ASKER
Will it not produce a record if I run the query 1 hour later. I need something that I can spot within 10 minutes. The session Start 10AM tomorrow, I send the email then there is another session at 10:10 AM I do not want to send another message to 10:00 AM but only to to 10:10 AM and so forth
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
erikTsomik, is this issue solved?
If affirmative please close this question by accepting the comment or comments that helped you out.
If negative, let us know what still going wrong so we can fix it for you.
Cheers
If affirmative please close this question by accepting the comment or comments that helped you out.
If negative, let us know what still going wrong so we can fix it for you.
Cheers