Output giving 1 or more lines when i need ONLY 1

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

General SQL Server Forums

New to SQL Server Programming

Output giving 1 or more lines when i need ONLY 1

Author

Topic

jalves
Starting Member

3 Posts

Posted - 2010-08-17 : 06:58:18

Hello,

I am trying to run an embedded select statement within a query that links tables.
The output is giving 1 or more lines of output per patient depending on the number of fields holding data in the Fractionation.NoFractions (Number of Fractions).
I need one line of output per patient, per course calculating the total number of fractions delivered for that patient, for their course and all their plans within it.

SELECT DISTINCT
Patient.LastName,
Patient.PatientId,
Patient.PatientId2,
Course.StartDateTime,
Course.CompletedDateTime,
Course.Intent,
Course.CourseId,
PlanSetup.Status,
-- Fractionation.NoFractions,
TotFractions=(Select sum( DISTINCT Fractionation.NoFractions) FROM Fractionation
WHERE Patient.PatientSer = Course.PatientSer and Course.CourseSer = PlanSetup.CourseSer and PlanSetup.PlanSetupSer = Fractionation.PlanSetupSer
GROUP BY Patient.PatientId )

FROM
Patient,
Course,
PlanSetup,
Fractionation

WHERE
( Patient.PatientSer = Course.PatientSer ) and
( Course.CourseSer = PlanSetup.CourseSer) and
( PlanSetup.PlanSetupSer = Fractionation.PlanSetupSer) and
( ( Upper(substring(Patient.PatientId,1,1)) not in ('A' , 'B' , 'C' , 'D' , 'F' , 'G' , 'H' , 'I' , 'J' , 'K' , 'L' , 'M' , 'N' , 'O' , 'P' , 'Q' , 'R' , 'S' , 'T' , 'U' , 'V' , 'W' , 'X' , 'Y' , 'Z') )) AND
( Upper(substring(Course.CourseId,1,1)) not in ('A' , 'E' , 'C' , 'D' , 'F' , 'G' , 'H' , 'I' , 'J' , 'K' , 'L' , 'M' , 'N' , 'O' , 'P' , 'Q' , 'R' , 'S' , 'T' , 'U' , 'V' , 'W' , 'X' , 'Y' , 'Z') )

Actual Result
Auckland2 5678903 6/23/2008 12:00:00 AM Radical BREAST Unapproved 5
Auckland2 5678903 6/23/2008 12:00:00 AM Radical BREAST Unapproved 25

Expected Result
Auckland2 5678903 6/23/2008 12:00:00 AM Radical BREAST Unapproved 30

Can you help me out to find the expected result?
Regards,

Joao

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-08-17 : 07:14:03

SELECT
Patient.LastName,
Patient.PatientId,
Patient.PatientId2,
Course.StartDateTime,
Course.CompletedDateTime,
Course.Intent,
Course.CourseId,
PlanSetup.Status,
-- Fractionation.NoFractions,

sum(TotFractions=(Select sum( DISTINCT Fractionation.NoFractions) FROM Fractionation
WHERE Patient.PatientSer = Course.PatientSer and Course.CourseSer = PlanSetup.CourseSer and PlanSetup.PlanSetupSer = Fractionation.PlanSetupSer
GROUP BY Patient.PatientId ))

FROM
Patient,
Course,
PlanSetup,
Fractionation

WHERE
( Patient.PatientSer = Course.PatientSer ) and
( Course.CourseSer = PlanSetup.CourseSer) and
( PlanSetup.PlanSetupSer = Fractionation.PlanSetupSer) and
( ( Upper(substring(Patient.PatientId,1,1)) not in ('A' , 'B' , 'C' , 'D' , 'F' , 'G' , 'H' , 'I' , 'J' , 'K' , 'L' , 'M' , 'N' , 'O' , 'P' , 'Q' , 'R' , 'S' , 'T' , 'U' , 'V' , 'W' , 'X' , 'Y' , 'Z') )) AND
( Upper(substring(Course.CourseId,1,1)) not in ('A' , 'E' , 'C' , 'D' , 'F' , 'G' , 'H' , 'I' , 'J' , 'K' , 'L' , 'M' , 'N' , 'O' , 'P' , 'Q' , 'R' , 'S' , 'T' , 'U' , 'V' , 'W' , 'X' , 'Y' , 'Z') )

group by Patient.LastName,
Patient.PatientId,
Patient.PatientId2,
Course.StartDateTime,
Course.CompletedDateTime,
Course.Intent,
Course.CourseId,
PlanSetup.Status

I'm not sure you need that "inner group by"....but you should not need the distinct if the joins are setup properly.

the usual format is to go

select groupingfields, sum(measurex) from
(
select alldetailinfo from mytablea
innner/left join mytableb on b.key= a.key
)
group by groupingfields

jalves
Starting Member

3 Posts

Posted - 2010-08-17 : 07:23:27

Hi Andrew,

If i do the changes that you have suggested, i get the following errors:

Error (102) Incorrect syntax near '='.
Error (107) The column prefix 'Patient' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.
Error (107) The column prefix 'Course' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.
Error (107) The column prefix 'Course' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.
Error (107) The column prefix 'PlanSetup' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.
Error (107) The column prefix 'PlanSetup' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.

Sorry, i´m brand new on this... probably it´s a simple change, but i just don´t know.

Regards,

joao

jalves
Starting Member

3 Posts

Posted - 2010-08-17 : 07:40:10

i can correct some of the errors by changing

sum(TotFractions=(Select sum( DISTINCT Fractionation.NoFractions) FROM Fractionation
WHERE Patient.PatientSer = Course.PatientSer and Course.CourseSer = PlanSetup.CourseSer and PlanSetup.PlanSetupSer = Fractionation.PlanSetupSer
))

to

sum(TotFractions=(Select sum( DISTINCT Fractionation.NoFractions) FROM Fractionation , Patient, Course, PlanSetup
WHERE Patient.PatientSer = Course.PatientSer and Course.CourseSer = PlanSetup.CourseSer and PlanSetup.PlanSetupSer = Fractionation.PlanSetupSer
))

but still not able to correct the following error:

Error (102) Incorrect syntax near '='.
Error (102) Incorrect syntax near ')'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources