By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,131 Members | 1,494 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,131 IT Pros & Developers. It's quick & easy.

using a count to filter excess data

P: 15
SELECT Sheet1$.[Trial ID] AS [Trail ID], CASE Sheet1$.[Trial ID] WHEN 'CSPP100A2344' THEN '0' END AS Extension, SUBSTRING(Sheet1$.[Patient ID], 1, 4)
AS [Center Number], SUBSTRING(Sheet1$.[Patient ID], 5, 8) AS [Patient Number], SUBSTRING(Sheet1$.[Patient ID], 1, 4)
+ '_' + SUBSTRING(Sheet1$.[Patient ID], 5, 8) AS [Patient Identifier], Sheet1$.[Trial ID] + '_' + SUBSTRING(Sheet1$.[Patient ID], 1, 4)
+ '_' + SUBSTRING(Sheet1$.[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(CONVERT(char(11), Sheet1$.[Date Of Birth], 106), ' ', ''))
AS [Date of Birth], M_PWA.SP, M_PWA.DP, UPPER(REPLACE(CONVERT(char(11), Sheet1$.DATETIME, 106), ' ', '')) AS Date, CONVERT(char(5), Sheet1$.DATETIME,
108) AS Time, M_PWA.SUB_TYPE, CASE Sheet1$.Inconclusive WHEN 'Yes' THEN '1' WHEN 'No' THEN '2' END AS Inconclusive, M_PWA.ED,
Sheet1$.[Operator Index], M_PWA.C_AP, Sheet1$.C_AP_HR75, 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$.DATETIME = M_PWA.DATETIME
WHERE (CASE Sheet1$.Inconclusive 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.DATETIME
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.Patient ID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Sheet1$_1.DATETIME" 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.
Jan 22 '07 #1
Share this Question
Share on Google+
2 Replies


iburyak
Expert 100+
P: 1,017
Can you successfully do?

[PHP]SELECT * FROM Sheet1$ [/PHP]
Jan 22 '07 #2

P: 15
Yes, no problem with that
Jan 26 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.