Date time difference complex query not working

[Julia B]

All, I'm relatively new to SQL server and am having problems writing a
complex date/time query. I have a table with a datecreated field and a
priority. The priority is either 1, 2, 3 or 0. The table also has a
datecompleted field.
If the datecompleted is null then I need to work out the number of working
hours from datecreated to now, if not then I need to work out the number of
working hours from datecreated to datecompleted.
I have this working fine..... however I then need to look at the priority
and work out if the number of working hours is over a set number depending on
priority. This is the bit that is causing me problems. Here's my code:

SELECT
(CASE WHEN SDItems5297.DateClosed IS NULL THEN
((DateDiff(HH, SDItems5297.DateCreated, GetDate())) - (DateDiff(WW,
SDItems5297.DateCreated, GetDate())) *2*24/24.0)
ELSE
((DateDiff(HH, SDItems5297.DateCreated, SDItems5297.DateClosed)) -
(DateDiff(WW, SDItems5297.DateCreated, SDItems5297.DateClosed)) *2*24/24.0)
END) AS HoursOpen,
(CASE WHEN SDItems5297.Priority = 1
THEN 'HoursOpen' - 24
WHEN SDItems5297.Priority = 2
THEN 'HoursOpen' - 48
WHEN SDItems5297.Priority = 3
THEN 'HoursOpen' - 120
ELSE 0
END) AS HoursOverSLA,

FROM SDItems5297
WHERE 'HoursOverSLA' > 0

This returns no rows, despite the fact that I know there are records that
meet the criteria. I'm sure it's something to do with the 2nd case statement,
but I'm not sure how to go about resolving it.

Any ideas? Thanks in advance.
Julia

[Hugo Kornelis]

On Tue, 6 Apr 2010 03:24:07 -0700, Julia B wrote:

(snip)

>I have this working fine..... however I then need to look at the priority
>and work out if the number of working hours is over a set number depending on
>priority. This is the bit that is causing me problems. Here's my code:

(snip)

>WHERE 'HoursOverSLA' > 0
>
>This returns no rows

Hi Julia,

In the WHERE, you compare a constant string to a constant numeric value.
I'm surprised you got no rows returned - when I try this, I get an error
because SQL Server tries to convert the string to an integer.

I guess you are running a different version of SQL Server. I know that
some very old versions would, in cases like this, convert the number to
string instead of the other way around, but I hope you're not running
one of those ancient versions! Anyway, however your version of SQL
Server interprets the comparison, since you compare two constant values
the result can only be either true for all rows, or false for all rows.
And since you get no rows back, the latter has to be the case.

I guess what you really wanted is to compare the result of the
computation in the SELECT to 0. That is a bit awkward in SQL Server,
since (conceptually - implementation may differ) the SELECT clause is
evaluated *AFTER* the WHERE clause, so there is no HoursOverSLA column
available to compare to 0. One workaround is to repeat the expression,
but for long and complicated expressions such as the one you are using,
that is not pretty. Another workaround is to use a derived table or a
CTE. Here is an example using a derived table:

SELECT HoursOpen, HoursOverSLA
FROM
(SELECT (first complex expression) AS HoursOpen
, (second complex expression) AS HoursOverSLA
FROM SDItems5297) AS d
WHERE HoursOverSLA > 0;

By nesting the SELECT, the logical order of evaluation has changed, so
now you can refer to the results of the SELECT.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Julia B]

"Hugo Kornelis" wrote:

> (snip)

> In the WHERE, you compare a constant string to a constant numeric value.
> I'm surprised you got no rows returned - when I try this, I get an error
> because SQL Server tries to convert the string to an integer.
>

Another workaround is to use a derived table or a
> CTE. Here is an example using a derived table:
>
> SELECT HoursOpen, HoursOverSLA
> FROM
> (SELECT (first complex expression) AS HoursOpen
> , (second complex expression) AS HoursOverSLA
> FROM SDItems5297) AS d
> WHERE HoursOverSLA > 0;
>
> By nesting the SELECT, the logical order of evaluation has changed, so
> now you can refer to the results of the SELECT.
>

Thanks Hugo, I've never heard of derived tables but changed my sql to read
like this:

SELECT HoursOpen, HoursOverSLA
FROM
(SELECT (CASE WHEN SDItems5297.DateClosed IS NULL THEN

((DateDiff(HH, SDItems5297.DateCreated, GetDate())) - (DateDiff
(WW,SDItems5297.DateCreated, GetDate()))
*2*24/24.0)
ELSE
((DateDiff(HH, SDItems5297.DateCreated,
SDItems5297.DateClosed)) - (DateDiff(WW,
SDItems5297.DateCreated, SDItems5297.DateClosed))
*2*24/24.0)
END) AS HoursOpen,
(CASE WHEN SDItems5297.Priority = 1
THEN 'HoursOpen' - 24
WHEN SDItems5297.Priority = 2
THEN 'HoursOpen' - 48
WHEN SDItems5297.Priority = 3
THEN 'HoursOpen' - 120
ELSE 0
END) AS HoursOverSLA

FROM SDItems5297)As d
WHERE HoursOverSLA > 0

This still returns no rows.
Any ideas?
Julia

[Hugo Kornelis]

On Wed, 14 Apr 2010 05:26:01 -0700, Julia B wrote:

>Thanks Hugo, I've never heard of derived tables but changed my sql to read
>like this:
>

(snip)

>
>This still returns no rows.
>Any ideas?
>Julia

Hi Julia,

Apologies for the late reply.

If you leave out the WHERE HoursOverSLA > 0 condition, do you get any
results then? And does that result set contain any rows where the value
of HoursOverSLA is more than 0?

[Julia B]

Thanks for your reply and sorry for my late reply!

No removing the WHERE clause makes no difference, no rows are returned.

Julia

"Hugo Kornelis" wrote:

> .
>