[Homework] Functions, Triggers and Transactions - Problem 10.	People with Balance Higher Than

[Homework] Functions, Triggers and Transactions - Problem 10. People with Balance Higher Than

Victuss avatar Victuss 18 Точки

[Homework] Functions, Triggers and Transactions - Problem 10. People with Balance Higher Than

Някаква идея защо това не минава в judge(5 грешни отговора). Аз ли не разбирам условието или нещо по кода е сгрешено:

CREATE PROCEDURE usp_GetHoldersWithBalanceHigherThan(@balanceThreshold DECIMAL(19,4))
AS
BEGIN
	WITH TotalBalanceOnAllAccounts_CTE(Id, TotalBalance) AS
	(
		SELECT a.AccountHolderId, SUM(a.Balance)
		FROM Accounts AS a
		GROUP BY a.AccountHolderId	
	)
	SELECT ah.FirstName AS [First Name], ah.LastName AS [Last Name]
	FROM AccountHolders AS ah
	INNER JOIN TotalBalanceOnAllAccounts_CTE AS tboaa
	ON ah.Id = tboaa.Id
	WHERE tboaa.TotalBalance > @balanceThreshold;
END

 

Тагове:
1
Databases Basics
Xzahn avatar Xzahn 34 Точки

Аз така я реших. Дава ми грешка при компилация. Нямам представа защо. 

CREATE PROCEDURE usp_GetHoldersWithBalanceHigherThan
(
	@sum MONEY
)
AS
BEGIN 
	SELECT FirstName, LastName FROM
	(
		SELECT FirstName, LastName, SUM(l.Amount) AS TotalBalance FROM Customers AS c
		JOIN Loans AS l
		ON l.CustomerID = c.CustomerID
		GROUP BY c.FirstName, c.LastName
	) AS tb
	WHERE tb.TotalBalance > @sum
END

П.С. Виждам, че ползваш различни таблици. Аз използвах базата от Exam Preparation-а.

П.С. 2 Пуснах това и мина всички тестове:

CREATE PROCEDURE usp_GetHoldersWithBalanceHigherThan
(
	@sum MONEY
)
AS
BEGIN 
	SELECT FirstName AS [First Name], LastName AS [Last Name] FROM
	(
		SELECT FirstName, LastName, SUM(a.Balance) AS TotalBalance FROM AccountHolders AS ah
		JOIN Accounts AS a
		ON a.AccountHolderId = ah.Id
		GROUP BY ah.FirstName, ah.LastName
	) AS tb
	WHERE tb.TotalBalance > @sum
END

 

0
15/10/2016 20:33:13