Advertisement
JimboLP

DB Basics - May 2018

Jun 20th, 2018
153
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create proc usp_GetHoldersWithBalanceHigherThan @balance decimal(18,4)
  2. as
  3. begin
  4.  
  5. --Take the IDs of the accounts with total balance bigger than the given amount (@balance)
  6. with CET_AccountBalance as(
  7. select ah.Id, sum(a.Balance) Balance from AccountHolders ah
  8. left join Accounts a on a.AccountHolderId = ah.Id
  9. group by ah.Id
  10. having sum(a.Balance) > @balance
  11. )
  12.  
  13. select ah.FirstName, ah.LastName from AccountHolders ah
  14. right join CET_AccountBalance ab on ab.Id = ah.Id
  15.  
  16. end
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement