December 12, 2006 at 12:55 pm
This query works when I put in an actual date. When I try to put the date into a variable called @today, the query crashes on the aggregate functions using cast. I also tried convert with the same result. Weird thing is that it will accept a date variable if I take away the q.question and q.question_data_type_id from the select statement. I'm pulling my hair out. Oh, and this statement works fine in SQL Server 2000.
declare
@today datetime
set
@today = '6/4/2006 11:59:59 PM'
SELECT
q.question_id,cap.service_provider_id,
count
(a.answer_value) as iCount,
avg
(CAST(a.answer_value AS float)) as fAvg,
SUM
(CAST(a.answer_value AS INT)) AS iSum,
q
.question,
q
.question_data_type_id
FROM
dbo.answers a RIGHT OUTER JOIN
dbo
.questions q ON q.question_id = a.question_id INNER JOIN
dbo.customer_answer_profile cap ON cap.customer_answer_profile_id = a.customer_answer_profile_id
WHERE
len
(a.answer_value) <> 0
and
q.question_data_type_id not in (3,5)
and
ca_timestamp > @today
GROUP
BY cap.service_provider_id, q.question_id, q.question, q.question_data_type_id
ORDER
BY cap.service_provider_id, q.question_id
December 12, 2006 at 2:00 pm
What is the error you are getting you it crashes?
Is there any computed column in the table?
MohammedU
Microsoft SQL Server MVP
December 12, 2006 at 3:04 pm
Also, what is the data type for q.question?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply