0

I am trying to pull send data from some scheduled sends. I am using below query:

SELECT
s.SubscriberKey as Email_Address,
s.EventDate as SendTime,
s.JobID as JobID,
d.VER as VER,
d.CellMarket as CellMarket 
from _Sent s
inner join  [Source DE for Monitoring] d
on d.Email_Address = s.SubscriberKey
where s.EventDate between '2019-10-15 06:00:00 PM' and '2019-10-16 11:00:00 AM'

This is the time when my automation run. It is in pacific. It gives me very limited records as compared to the actual sends went.

When I use EventDate between dateadd(d,-2,getdate()) and dateadd(d,-1,getdate()), it gives me more records but still not matching with the exact sends. I have also tried using hours(hh) instead of days(d) but not working.

All the automation run between 5 PM PST to next day 9 AM PST. I am aware that the Server time is CST irrespective of daylight savings.

I am not sure where I am missing on the time?

  • Hi - welcome to the community. Have you checked the send reporting to see if all emails were processes successfully? It is possible some records were not sent due to exclusion/suppression or error. – Cameron Robert Oct 17 '19 at 12:25
  • Yes, I have checked in the tracking for that time frame's send and can see the sends there. There are suppression but not much. – user75247 Oct 17 '19 at 12:38
0

I guess it would be something like this[This might not be a valid solution or does work], you just need to make sure that your string will be parsed to the correct date type.

SELECT
s.SubscriberKey as Email_Address,
s.EventDate as SendTime,
s.JobID as JobID,
d.VER as VER,
d.CellMarket as CellMarket 
from _Sent s
inner join  [Source DE for Monitoring] d
on d.Email_Address = s.SubscriberKey
where s.EventDate between CONVERT(datetime, '10/15/2019 06:00:00PM')
and CONVERT(datetime,'10/16/2019 11:00:00AM') 
| improve this answer | |
  • Thanks Johannes, this query is somehow working and giving me few records. It is covering the start time and end time of the sends. But so many records are still missing. Ideally it should be giving ~300 but only giving 34. Any further suggestion would be much appreciated. – user75247 Oct 17 '19 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.