I need an email sent on a specific date/time. Query needs to pop data ext. 1 day before date/time

Question

I need an email sent on a specific date, my current query populates a data extension from the difference of days since the users received a particular "email id". What would be the best way to have this query populate a new data ext 1 day before the given date?

SELECT 
  EmailAddress as SubscriberKey
  , EmailAddress
  , first_name
  , last_name
  , registration_date 
FROM Wild_Spirit_12pm h 
WHERE h.EmailAddress IN (
   SELECT s.SubscriberKey   
   FROM _Sent s     
   INNER JOIN _Job j ON s.JobID = j.JobID   
   WHERE j.EmailID = '2457224'
   AND DATEDIFF(Day, s.EventDate, Getdate()) = 1) /* this is where i need it send 1 day before 6/28/16 */

Answer 1

Something like this perhaps? Casting the dates as date will set the time portion to midnight.

SELECT 
  h.EmailAddress as SubscriberKey
  , h.EmailAddress
  , h.first_name
  , h.last_name
  , h.registration_date 
FROM Wild_Spirit_12pm h 
WHERE h.EmailAddress IN (
   SELECT s.SubscriberKey   
   FROM _Sent s     
   INNER JOIN _Job j ON (s.JobID = j.JobID)
   WHERE j.EmailID = 2457224 /* emailid is a number */
   AND DATEDIFF(d, cast(s.EventDate as date), cast(Getdate() as date)) = 1 /* was yesterday*/
)

or a more sargable version:

SELECT 
  h.EmailAddress as SubscriberKey
  , h.EmailAddress
  , h.first_name
  , h.last_name
  , h.registration_date 
FROM Wild_Spirit_12pm h 
WHERE h.EmailAddress IN (
   SELECT s.SubscriberKey   
   FROM _Sent s     
   INNER JOIN _Job j ON (s.JobID = j.JobID)
   WHERE j.EmailID = 2457224 /* emailid is a number */
   AND s.eventDate >= convert(date, getDate()-1) 
   and s.eventDate < convert(date, getDate())
)