quote:

---

Originally posted by dalewright24

Hi People
I have forumalated the following sp.. When i check my syntax, there is no errors. When i execute the sp through Query analyser, i get the 'Invalid Object Name 'U' however u is specified as a tbl.... Please can someone point me in the right direction. Thank you.

CREATE PROCEDURE [spYearToDateTotals]
@intuserID Integer
AS
SELECT u.*
FROM tblUser AS u
LEFT JOIN 
(
SELECT     UserID, SUM(CONVERT(int, Miles1)) AS SumOfMiles1, Rate1
FROM         tblApp AS d LEFT OUTER JOIN u ON d.UserID = u.UserID
WHERE     (CONVERT(datetime,AuthorisedHRDate, 103) BETWEEN CONVERT(datetime, '31/03/2007', 103) AND CONVERT(datetime, '31/03/2008', 103))
GROUP BY UserID, Rate1, AuthorisedHRStatus
HAVING      (AuthorisedHRStatus = 1) AND (CONVERT(decimal, Rate1) > 0)
) 
AS d ON d.UserID =u.UserID
LEFT JOIN 
(
SELECT     UserID, SUM(CONVERT(int, Miles2)) AS SumOfMiles2, Rate2
FROM         tblApp AS e LEFT OUTER JOIN u ON e.UserID = u.UserID
WHERE     (CONVERT(datetime,AuthorisedHRDate, 103) BETWEEN CONVERT(datetime, '31/03/2007', 103) AND CONVERT(datetime, '31/03/2008', 103))
GROUP BY UserID, Rate1, AuthorisedHRStatus
HAVING      (AuthorisedHRStatus = 1) AND (CONVERT(decimal, Rate2) > 0)
) 
AS e ON e.UserID =u.UserID

WHERE u.UserID = @intuserID
GROUP BY u.UserID
GO

---

quote:

---

Originally posted by Peso

CREATE PROCEDURE [spYearToDateTotals]
(
	@intuserID Int
(
AS

SET NOCOUNT ON

SELECT		u.*,
		d.sumofmiles1,
		d.rate1,
		e.sumofmiles2,
		e.rate2
FROM		tblUser AS u
LEFT JOIN	(
			SELECT		UserID,
					SUM(CONVERT(int, Miles1)) AS SumOfMiles1,
					Rate1
			FROM		tblApp AS d
			LEFT JOIN	tblUser AS u ON d.UserID = u.UserID
			WHERE		AuthorisedHRDate BETWEEN '31/03/2007' AND '31/03/2008'
					AND AuthorisedHRStatus = 1
					AND CONVERT(decimal, Rate1) > 0
			GROUP BY	UserID,
					Rate1
		) AS d ON d.UserID =u.UserID
LEFT JOIN	(
			SELECT		UserID,
					SUM(CONVERT(int, Miles2)) AS SumOfMiles2,
					Rate2
			FROM		tblApp AS e
			LEFT JOIN	tblUser AS u ON e.UserID = u.UserID
			WHERE		AuthorisedHRDate '31/03/2007' AND '31/03/2008'
					AND AuthorisedHRStatus = 1
					AND CONVERT(decimal, Rate2) > 0
			GROUP BY	UserID,
					Rate1
		) AS e ON e.UserID =u.UserID
WHERE		u.UserID = @intuserID

---

E 12°55'05.25"
N 56°04'39.16"

---

quote:

---

Originally posted by Peso

The LEFT JOIN is not necessary in the derived table, since you only are grouping by userid an rate.

---