Syntax help in WHERE clause

Results 1 to 3 of 3

Thread: Syntax help in WHERE clause

  1. #1
    Join Date
    Dec 1969

    Default Syntax help in WHERE clause

    Hi there. I am having a ton of trouble with the last part of this query. My query is returning a record of dates the person visited our office. Here&#039;s what I have - I put comments in the WHERE clause so it&#039;s easier to decipher. I also marked where I&#039;m having trouble - how do I say at least 1 of the visits must be b/t 1st and 2nd birthdays?:<BR><BR>SELECT DISTINCT hp.p_id AS PID, COUNT(DISTINCT h_stat.h_status_dt) AS StatDate <BR>FROM H_STAT INNER JOIN HP ON H_STAT.h_id = HP.h_id <BR>INNER JOIN P ON HP.p_id=P.p_id <BR>WHERE (H_STAT.h_status_type_cd = 9) AND (HP.hp_role_cd=3) <BR><BR>*****find people who will turn 2 this year ****** <BR><BR>AND (hsop.p_id IN(SELECT p_id FROM p WHERE birth_dt &#062;= &#039;1/1/1999&#039; and birth_dt &#060;=&#039;12/31/1999&#039;)) <BR><BR>*****all visits take place on or before 2nd b-day******* <BR>AND (h_stat.h_status_dt &#060;= DATEADD(yyyy, 2,p.birth_dt))<BR><BR>*****HERE&#039;s WHERE I&#039;M HAVING TROUBLE - at least one visit b/t 1st and 2nd b-day****** <BR>AND h_stat.h_status_dt IN (SELECT h_stat.h_status_dt FROM h_stat INNER JOIN hp ON h_stat.h_id=hp.h_id INNER JOIN p ON hp.p_id=p.p_id <BR> WHERE (h_stat.h_status_dt &#062;= DATEADD(yyyy, 1, p.birth_dt)) and (h_stat.h_status_dt &#060;= DATEADD(yyyy, 2, p.birth_dt)) AND h_stat.h_status_type_cd=9) <BR><BR>*****all visits after person&#039;s 38 days old******<BR>AND (h_stat.h_status_dt &#062; DATEADD(dd, 38, p.birth_dt)) <BR><BR>*****different dates of service******<BR>AND (CONVERT(char, h_stat.h_status_dt, 101) IN (SELECT DISTINCT CONVERT(char,h_stat.h_status_dt, 101) FROM h_stat <BR>INNER JOIN hp ON h_stat.h_id=hp.h_id WHERE hp.p_id=p.p_id)) <BR>GROUP BY hp.p_id <BR><BR><BR>Thanks in advance for any help - this is driving me crazy!!<BR>

  2. #2
    Tim Snyder Guest

    Default RE: Syntax help in WHERE clause

    WHERE birth_dt between&#039;1/1/1999&#039; and &#039;12/31/1999&#039; <BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default Thanks but...

    Thanks for the help, but my problem is, I&#039;m not quite sure how to say that at least one of the visits have to be between the 1st and 2nd b-day. It&#039;s the "at least one" part that I&#039;m stuck on.

Posting Permissions

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