Results 1 to 2 of 2

Thread: Help/Advice?

  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Question Unanswered: Help/Advice?

    DECLARE @returnDay int
    SELECT @returnDay = DatePart(day,GetDate())
    If @returnDay = 8
    BEGIN
    select * from Hospitals left join Units ON Units.HospitalID = Hospitals.HospitalID where Units.HospitalID is null
    RETURN
    END

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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    SELECT *
       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.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •