using a count to filter excess data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using a count to filter excess data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-23 : 10:09:46

Erin writes "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."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources