Parameter in WHERE clause using Functions

I'm trying to use a report parameter in my where clause to compare against
the month portion of a field. Reporting Services (version 2000) does not seem
to like this. Here's the WHERE clause I'm trying to get to work (of course
it's all on one line):
WHERE (company = @.Company) AND (MONTH(close_time) = @.DSMonth1) AND
(YEAR(close_time) = @.DSYear1)
When I try to execute the query, I get an error that states:
Invalid object name "dbo.RAW_DW.MONTH"
Invalid object name "dbo.RAW_DW.YEAR"
It seems Reporting Services thinks MONTH and YEAR are fields when I'm just
trying to use the MONTH and YEAR functions to extract that data from the
datetime field (close_time) and compare it to the report parameter. It may
just be a formatting issue on my part.
Does anyone know what's wrong with the clause?
Thanks in advance.
BrendaHi Brenda,
I dont think that the error you are getting is related to the WHERE clause
in this case. Do you have RAW_DW.Month and ...YEAR in your overall query?
What are the data types of the two parameters, @.DSMonth1 and @.DSYear1?
Without seeing the full query, it is hard to troubleshoot the error, if
indeed these are valid objects in your database.
Rodney Landrum
"BLKeller" <BLKeller@.discussions.microsoft.com> wrote in message
news:69A3593E-8832-46BB-ADD1-A1079829C95E@.microsoft.com...
> I'm trying to use a report parameter in my where clause to compare against
> the month portion of a field. Reporting Services (version 2000) does not
> seem
> to like this. Here's the WHERE clause I'm trying to get to work (of course
> it's all on one line):
> WHERE (company = @.Company) AND (MONTH(close_time) = @.DSMonth1) AND
> (YEAR(close_time) = @.DSYear1)
> When I try to execute the query, I get an error that states:
> Invalid object name "dbo.RAW_DW.MONTH"
> Invalid object name "dbo.RAW_DW.YEAR"
> It seems Reporting Services thinks MONTH and YEAR are fields when I'm just
> trying to use the MONTH and YEAR functions to extract that data from the
> datetime field (close_time) and compare it to the report parameter. It may
> just be a formatting issue on my part.
> Does anyone know what's wrong with the clause?
> Thanks in advance.
> Brenda

Pairing Group

Hello, I have an interesting problem. I have to compare 2 consecutive
records. I'm using Crystal Reports v.10 if that helps. I have a nchar Col4
and a date/time in Col3. I need to compare the date/time of every 2nd of 2
records. The time in the second record of each pair is always a few seconds
,
or a few minutes, greater than in the first record. Col1 and Col2 have
values that are always the same; Col4 varies slightly by the text message.
Any help would be appreciated. Thanks.This would be a self join. For example:
SELECT T1.col1, T1.col2, T1.col3, T1.col4,
MIN(T2.col3)
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.col3 < T2.col3
GROUP BY T1.col1, T1.col2, T1.col3, T1.col4
David Portas
SQL Server MVP
--|||David,
This query is helpful but I suppose I should have added that I need to CALC
a subtraction of the 1st row's date/timestamp from the 2nd row's
date/timestamp to produce an interval value such as :03 (seconds) etc. Is
this possible? Thanks.
"David Portas" wrote:

> This would be a self join. For example:
> SELECT T1.col1, T1.col2, T1.col3, T1.col4,
> MIN(T2.col3)
> FROM YourTable AS T1
> JOIN YourTable AS T2
> ON T1.col3 < T2.col3
> GROUP BY T1.col1, T1.col2, T1.col3, T1.col4
> --
> David Portas
> SQL Server MVP
> --
>
>|||SELECT T1.col1, T1.col2, T1.col3, T1.col4,
MIN(T2.col3), DATEDIFF(S,T1.col3,MIN(T2.col3))
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.col3 < T2.col3
GROUP BY T1.col1, T1.col2, T1.col3, T1.col4
David Portas
SQL Server MVP
--|||This was what I needed and it worked great. I marked it a Helpful Post.
Thanks!
"David Portas" wrote:

> SELECT T1.col1, T1.col2, T1.col3, T1.col4,
> MIN(T2.col3), DATEDIFF(S,T1.col3,MIN(T2.col3))
> FROM YourTable AS T1
> JOIN YourTable AS T2
> ON T1.col3 < T2.col3
> GROUP BY T1.col1, T1.col2, T1.col3, T1.col4
> --
> David Portas
> SQL Server MVP
> --
>
>