Inherited a nightmare.. - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Inherited a nightmare..

Author

Topic

Haggis7
Starting Member

1 Post

Posted - 2009-07-24 : 18:52:29

Hi,

I inherited a nightmare SSRS project. I have a report that I need to simply get the count of the rows of data and I am trying to set that in the query itself but to no avail. All I want is the number of rows returned from the query so I can put it in a function or something to compute some averages. I have removed a lot of it to try and slim it down. I get a Total of 6 rows returned but any type of count I do always returns 16. Here is the query (inherited..):


declare @InstructorId as int
SET @InstructorId = 133138

declare @CampusId as int
SET @CampusId = 1
declare @StartDate as DateTime
SET @StartDate = '6/22/2009'

Select
AQ.question_nbr as QuestionNbr
,c.description as QuestionCategoryDescription
,QP.Question_short_text as QuestionShortText
,count(*) as count1

From Survey_Line_Item SLI
Inner join Assessment_Question AQ 
	on SLI.Assessment_Question_id = AQ.Assessment_question_id
	inner join Question_pool qp
		on aq.question_id = qp.question_id
                                inner join category C
		                 on qp.Category_Code = c.category_code and c.description = 'Instruction'

Inner  join Assessment_Survey ASur
	on SLI.Survey_ID = ASur.Survey_Id
	inner join course_sections CS
	on	Asur.Section_id = cs.section_id
		Left Outer join instructors I
		on Cs.Instructor_id = I.instructor_id
		inner join Courses 
		on CS.course = Courses.Course
		inner join Campuses 
		on cs.campus = campuses.campus
inner join Assessment A
	on Asur.Assessment_Id = A.Assessment_id
Inner Join Assessment_type AT
	on A.Assessment_type_code = AT.Assessment_Type_code
Inner Join Question_type qt
	on qt.question_type_code = sli.question_type_code
left Outer join Survey_line_item_category SLIC
	on SLI.Survey_line_Id = SLIC.Survey_line_id
Inner join REsponse_value RV	
		on SLI.Response_value_code = RV.REsponse_value_code
		and SLI.Question_type_code = RV.Question_type_code

Where 
At.assessment_type_code = 'STDEOC' and 
cs.instructor_id = @InstructorId
and cs.Campus = @CampusId
and cs.start_date in (@StartDate)  
and len(sli.response_value_code) > 0
and c.category_code <> 'Open'


Group BY
c.description
,AQ.question_nbr 
,QP.Question_short_text
, cs.section_id
,CS.end_date

order by c.description