Link to home
Start Free TrialLog in
Avatar of erikTsomik
erikTsomikFlag for United States of America

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))

Open in new window

Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

What's your current SELECT returning so we can know what's wrong or missing?
Avatar of erikTsomik

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

Open in new window

Using between is not a good idea. I'll explain later.

Where clause something like

where sessionStart < getdate() + 1 and sessionStart >= getdate()

Open in new window


Not tested, but I *think* this should do it.
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:
WHERE   SU.sessionStart >= DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0)
        AND SU.sessionStart < DATEADD(HOUR, 24, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() + 1), 0));

Open in new window

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
where datediff(mi, getdate(), session_start) / 1440 < 1
WHERE session_start >= DATEADD(HOUR, -23, GETDATE()) AND session_start <= GETDATE()
@Scott Pletcher. sessionStart is in the future date. I am sending reminders.

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

Open in new window

You can subtract dates. The result will be in days so 24h is 1 day:
WHERE   SU.sessionStart-GETDATE() BETWEEN 0 AND 1

Open in new window

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.
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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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