using a count to filter excess data

[AtCor]

SELECT Sheet1$.[Trial ID] AS [Trail ID], CASE Sheet1$.[Trial ID] WHEN 'CSPP100A2344' THEN '0' END AS Extension, SUBSTRING(Sheet 1$.[Patient ID], 1, 4)
AS [Center Number], SUBSTRING(Sheet 1$.[Patient ID], 5, 8) AS [Patient Number], SUBSTRING(Sheet 1$.[Patient ID], 1, 4)
+ '_' + SUBSTRING(Sheet 1$.[Patient ID], 5, 8) AS [Patient Identifier], Sheet1$.[Trial ID] + '_' + SUBSTRING(Sheet 1$.[Patient ID], 1, 4)
+ '_' + SUBSTRING(Sheet 1$.[Patient ID], 5, 8) AS [Study Number], Sheet1$.[First Name] + Sheet1$.Surname AS [Patient Initials],
CASE Sheet1$.NOTES WHEN 'visit 3' THEN '777' WHEN 'visit 11' THEN '778' WHEN 'unscheduled visit' THEN '999' END AS [Visit Report Number],
CASE Sheet1$.NOTES WHEN 'unscheduled visit' THEN '777' END AS [Repeat Visit Number],
CASE Sheet1$.NOTES WHEN 'unscheduled visit' THEN '777' END AS [Repeat Page Number], Row_NUMBER() OVER (Partition BY
Sheet1$.[Patient ID]
ORDER BY Sheet1$.[Patient ID]) AS [Record Number],
CASE Sheet1$.NOTES WHEN 'visit 3' THEN 'Day 1' WHEN 'visit 11' THEN 'Day 253' WHEN 'unschedule visit' THEN 'UNSCHEDULED VISIT' END AS [Visit Report Name],
CASE Sheet1$.Sex WHEN 'Female' THEN '2' WHEN 'Male' THEN '1' END AS Gender, UPPER(REPLACE(C ONVERT(char(11) , Sheet1$.[Date Of Birth], 106), ' ', ''))
AS [Date of Birth], M_PWA.SP, M_PWA.DP, UPPER(REPLACE(C ONVERT(char(11) , Sheet1$.DATETIM E, 106), ' ', '')) AS Date, CONVERT(char(5) , Sheet1$.DATETIM E,
108) AS Time, M_PWA.SUB_TYPE, CASE Sheet1$.Inconcl usive WHEN 'Yes' THEN '1' WHEN 'No' THEN '2' END AS Inconclusive, M_PWA.ED,
Sheet1$.[Operator Index], M_PWA.C_AP, Sheet1$.C_AP_HR 75, M_PWA.C_MPS, Sheet1$.HR, M_PWA.C_PH, M_PWA.C_AGPH, Sheet1$.C_AGPH_ HR75,
Sheet1$.C_SP, Sheet1$.C_DP, Sheet1$.C_MEANP , Sheet1$.C_T1, Sheet1$.C_T2, Sheet1$.C_AI, Sheet1$.C_ESP
FROM Sheet1$ INNER JOIN
M_PWA ON Sheet1$.DATETIM E = M_PWA.DATETIME
WHERE (CASE Sheet1$.Inconcl usive WHEN 'Yes' THEN '1' WHEN 'No' THEN '2' END = 2) AND (M_PWA.P_QC_PH >= 80) AND (M_PWA.P_QC_PHV <= 6) AND
(M_PWA.P_QC_DV <= 6) AND (Sheet1$.[Operator Index] >= 80)
GROUP BY Sheet1$_1.[Patient ID], Sheet1$_1.DATET IME
HAVING (COUNT(*) <= 2)

ORDER BY [Patient Identifier], Date, Time

ERROR MESSAGE

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Sheet1$_1.Pati ent ID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Sheet1$_1.DATE TIME" could not be bound.

What I am trying to accomplish is to get a count of parameters with an operator index >=80. Sometimes, I have more data than I can use for a given date. I need to filter the excess data away. All of the other items in the query work properly, it is just when I try to add the count feature (which I can get to work separately in a different query.

[iburyak]

Can you successfully do?

[PHP]SELECT * FROM Sheet1$ [/PHP]

[AtCor]

Yes, no problem with that