Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

SQL Query Help, With Date Condition

I am trying to get a list of all Institution codes where the patient is still active. This occurs when the [On Study Date] is greater than 1/1/2006, so I am trying to put a Y if they have more than one patient with an On Study Date greater than 1/1/2006 and a N if they have 0 patients meeting that requirement. However my query will not work...

SELECT qryInstitution_Ancillary.[Institution Code], (IIF COUNT(tblPatientDS.[On Study Date]>='1/1/2006') > 0,'Y','N') AS Expr1
FROM qryInstitution_Ancillary LEFT JOIN tblPatientDS ON qryInstitution_Ancillary.[Institution Code] = tblPatientDS.[Principal Institution];

My error is.. Syntax error (comma) in query expression '(IIF COUNT(tblPatientDS.[On Study Date]>='1/1/2006') > 0,'Y','N')'.

Any thoughts would be greatly appreciated. Thanks.
0
kpurchase
Asked:
kpurchase
  • 5
  • 3
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:


SELECT qryInstitution_Ancillary.[Institution Code], IIF((select  COUNT(*) from  tblPatientDS where [On Study Date]>=#1/1/2006#) > 0,'Y','N') AS Expr1
FROM qryInstitution_Ancillary LEFT JOIN tblPatientDS ON qryInstitution_Ancillary.[Institution Code] = tblPatientDS.[Principal Institution];


0
 
kpurchaseAuthor Commented:
That looks good.. But it now lists a Y for every patient with that institution code. Anyway that we could get it so that it is just a single Y or N for each institution code?
0
 
kpurchaseAuthor Commented:
Actually I think thats just.. adding select distinct. But I get all Y's when doing so, which is incorrect.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rey Obrero (Capricorn1)Commented:
so you want individual listing

SELECT qryInstitution_Ancillary.[Institution Code], IIF([On Study Date]>=#1/1/2006#,'Y','N') AS Expr1
FROM qryInstitution_Ancillary LEFT JOIN tblPatientDS ON qryInstitution_Ancillary.[Institution Code] = tblPatientDS.[Principal Institution];


0
 
mdouganCommented:
You are missing a right parenthesis on your COUNT statement.  However, I'm not so sure that count is going to work unless you add a Group By clause.  I think I'd prefer to do this with a UNION

SELECT DISTINCT qryInstitution_Ancillary.[Institution Code], 'Y'
FROM qryInstitution_Ancillary
LEFT JOIN tblPatientDS ON qryInstitution_Ancillary.[Institution Code] = tblPatientDS.[Principal Institution]
WHERE tblPatientDS.[On Study Date]>='1/1/2006'

UNION

SELECT DISTINCT qryInstitution_Ancillary.[Institution Code], 'N'
FROM qryInstitution_Ancillary
LEFT JOIN tblPatientDS ON qryInstitution_Ancillary.[Institution Code] = tblPatientDS.[Principal Institution]
WHERE NOT EXISTS (SELECT tblPatientDS.[Principal Institution]
                                   FROM tblPatientDS
                                   WHERE tblPatientDS.[Principal Institution] = qryInstitution_Ancillary.[Institution Code]
                                   AND  tblPatientDS.[On Study Date]>='1/1/2006')
0
 
kpurchaseAuthor Commented:
We are on the right track, and real close.. But I think we are missing each other slightly. What I need is a listing of either a Y for an Institution Code or a N. The last solution you listed gave me a Y or N for each patient in tblPatientDS and when I put SELECT DISTINCT in front of it, it gave me a Y or N for every Institution Code that had both or just a Y or just a N for Institution Codes that just had one or the other. Does that make more sense?
0
 
Rey Obrero (Capricorn1)Commented:
kpurchase,
which post are you referring to?
0
 
mdouganCommented:
Another way to do this would be this way:

SELECT  qryInstitution_Ancillary.[Institution Code],
MIN(IIF(tblPatientDS.[On Study Date]>='1/1/2006', 'Y', 'N')) AS flag
FROM qryInstitution_Ancillary
LEFT JOIN tblPatientDS ON qryInstitution_Ancillary.[Institution Code] = tblPatientDS.[Principal Institution]
GROUP BY qryInstitution_Ancillary.[Institution Code]

This will return the Y value if it exists for an institution code, otherwise will return an N
0
 
kpurchaseAuthor Commented:
capricorn, my previous post was in response to yours as I had not seen mdougan's response until just now so I will try that out.
0
 
mdouganCommented:
Actually, maybe that MIN has to be MAX
0
 
kpurchaseAuthor Commented:
worked beautifully once adding the MAX to it...
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now