David,
Thanks for the reply. I tested out your code and it worked, but brought
back a value WAY to high for Exception hours. The result below for
252.00hours should really be 6.
EK7824 07-09-2007 252.00 7.5
EK7824 07-10-2007 .00 7.5
EK7824 07-11-2007 .00 7.5
EK7824 07-12-2007 .00 7.5
EK7824 07-13-2007 .00 7.5
EK7824 07-16-2007 .00 7.5
EK7824 07-17-2007 .00 7.5
EK7824 07-18-2007 .00 7.5
EK7824 07-20-2007 .00 7.5
To answer your question in your code, yes exception time is a numeric field
that can be sumed.
On 7/20/07, David L. Penton <[EMAIL PROTECTED]> wrote:
I think it is because of the correlated subquery in your statement. I
rewrote it, adding in something for the date logic you have:
declare @dateMin datetime, @dateMax datetime
select
@dateMin = cast(
cast(@ReportYear as varchar) + '/' + cast(@ReportMonth as varchar) +
'/01'
as datetime)
, @dateMax = dateadd(month, 1, @dateMin)
SELECT
w.CompleteBy AS UUID
, CONVERT(CHAR(10),w.CompleteDate,110) AS CompleteDate
-- is this an INT or some summable value?
, COALESCE( SUM(e.ExceptionTime), 0 ) AS ExceptionHours
-- cast this as the proper type:
-- is it a float or a decimal (with a precision/scale?)
, 7.5 AS WorkHours
FROM
LSMTM_Work w
LEFT OUTER JOIN
LSMTM_Exception e ON
-- I'd rather do some manipulation here
-- similar to the Report Year/Month
e.ExceptionDate = CONVERT(CHAR(10), w.CompleteDate,110)
AND e.UUID = w.CompleteBy
WHERE
w.CompleteDate >= @dateMin
AND w.CompleteDate < @dateMax
AND w.CompleteBy = CASE @UUID
WHEN 'All' THEN w.CompleteBy
ELSE @UUID END
GROUP BY
w.CompleteBy
, CONVERT(CHAR(10),w.CompleteDate,110)
Ryan Everhart wrote:
> Hey Everyone,
> The code linked below returns an "Internal SQL Server error." error when
> I run it in Query Analyzer on my MSSQL 2000 server. Any ideas? It seems
> to have something to do with the date part of the subquery's where
> statement.
>
> *Error:*
> Server: Msg 8624, Level 16, State 21, Line 31
> Internal SQL Server error.
>
> *Code:*
> http://codeshare.ulatu.com/jul85ff0
>
> Any ideas?
> Ryan
>
> --
> Ryan Everhart
> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
> Blogging @ http://blog.ryaneverhart.com/
>
> Have you gotten your InstantSpot yet?
> http://www.instantspot.com/referral?c=n3fpjscv
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.instantspot.com/
www.teksystems.com/
--
Ryan Everhart
[EMAIL PROTECTED]
Blogging @ http://blog.ryaneverhart.com/
Have you gotten your InstantSpot yet?
http://www.instantspot.com/referral?c=n3fpjscv
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.instantspot.com/
www.teksystems.com/
