microsoft.public.sqlserver.programming - Microsoft SQL Server
I am seeing an issue in one of my cubes where the sum returns as a negative number (int) but there are no negative values in leaf members. The case I am seeing is rolling up days to month on an integer type measure. All of the days are positive. The rolled up sum for the month is negative. I can see no reason for this to happen. There are no custom rollups, this is a straightforward calculation. Any help or suggestions would be greatly appreciated.
I am working with a tablix in a matrix report. When I hide a row group, it leaves an undsired white space where the column was that was hidden. I would like all of the columns to the left of the hidden column to "move in" on the white space. Is there a way to do this?
I have the following scenario: Stored procedure with 4 tables - using INNER JOINs. Results come up in 1 second (4400 records). Add an additional table-valued function in the FROM clause - using INNER JOIN. Again, the results take less than 2 seconds (2250 records). Now I modify the last addition to make it a LEFT OUTER JOIN, so that I will retrieve the original 4400 records and values from the table-valued function where there is a match. This one runs for over a minute and never gives me any results. I finally cancel the operation in Query Analyzer. Is there something about table-valued functions that won't work well in outer join situations? Is there a better way to design this? SELECT E.EncounterID, S.Name AS SubjectName, S.Phone, H.Name AS HelperName, U.DateOfContact, U.Notes FROM tblEncounter AS E INNER JOIN tblSubject AS S ON E.SubjectID = S.SubjectID INNER JOIN tblHelper AS H ON E.HelperID = H.HelperID LEFT OUTER JOIN udf_GetMostRecentHistoryForSubject(@pintSubjectID) AS U ON E.SubjectID = U.SubjectID
I asked this in the Access adp section and didn't get a response, so I am asking here as it appears to be more of a SQL question than just an ADP question. I need to show the total number of hours entered for employees in a view with a group by, sum and where clause. I need all employees to show up even if they have not put any details in for the time period specified. If I take the where clause out, I get 0 values for emplyees with no time entries, but as soon as I put the where clause back in I now only see emplyees that have entered time values. The view is below, if anyone can help it would be greatly appreciated. SELECT dbo.tblEmployee.FullName, SUM(ISNULL(dbo.tblWorkDetail.WorkHours, 0)) AS Hours FROM dbo.tblEmployee LEFT OUTER JOIN dbo.tblWorkDetail ON dbo.tblEmployee.LogOn = dbo.tblWorkDetail.Employee WHERE (dbo.tblWorkDetail.WorkDate BETWEEN CONVERT(DATETIME, '2006-10-30 00:00:00', 102) AND CONVERT(DATETIME, '2006-10-31 00:00:00', 102)) OR (dbo.tblWorkDetail.WorkDate IS NULL) GROUP BY dbo.tblEmployee.FullName Thanks