DECLARE @returnDay int
SELECT @returnDay = DatePart(day,GetDate())
If @returnDay = 8
select * from Hospitals left join Units ON Units.HospitalID = Hospitals.HospitalID where Units.HospitalID is null
this is just a part of the procedure I am trying to create, I am getting hospitals that haven't submitted any data and wish to send them an email.
on the other hand I have two tables that have all the data for emailing to hospitals but are not linked to tables giving the list of hospitals
I have been advised to create a cursor(easier said then done) that will go through my list of records that need to receive an email
nothing going very well with that at the moment.
so I was hoping to see if somebody has any other suggestions for me.....
FROM Hospitals AS h
WHERE NOT EXISTS (SELECT *
FROM Units AS u
WHERE u.HospitalID = h.HospitalID)
That should produce exactly the same list as your example, it is just more intuitive for me to read. Once the result set comes back, I'd have either DTS or the client side application build the email to go out.