That means there are duplicates by your 'left join' Here is another one to eliminate that issue. Note that I am using a
derived/immediate view with the summation:
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( 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 (
SELECT
ex.ExceptionDate
, ex.UUID
, SUM(ex.ExceptionTime) As ExceptionTime
FROM
LSMTM_Exception ex
GROUP BY
ex.ExceptionDate
, ex.UUID
) e ON
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:
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.00 hours 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]
<mailto:[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
_______________________________________________
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/
