join query help !

Question

Hey,

I am dealing with some queries for my assignment and any help would be greatly appreciated.

1. List branches together with the number of employees and assigned to them customers, total loan amount, total account balance, assets for branches residing in the given city.
2. List customers who made account and loan operations in the given time period
3. List employees and the number of served by each of them customers in the given time period

I guess a simple example would be enough for me to solve rest.

Here's what I've tried so far for the first one:

ALTER PROCEDURE [dbo].[SelecBranchesByCity]
    (@City varchar(50))
AS
    select
        Br.Name as BranchName,
        COUNT(emps.ID) as NumberOfEmployee,
        SUM(emps.NumberOfCustomers) as TotalCustomers,
        SUM(lo.Amount) as TotalAmountOfLoan,
        SUM(acc.Balance) as TotalBalance,
        Br.Assets as Assets
    from Branches Br
    left outer join Employees emps on emps.[BranchName] = Br.Name
    left outer join Loans lo on lo.[BranchName] = Br.Name
    left outer join Accounts acc on acc.[BranchName] = Br.Name
    where
        Br.[Address] like '%'+@City+'%'
    GROUP BY
        Br.ID,
        Br.Name,
        Br.Assets

Here is the schema !

Answer 1

Schema is frightening. Many-to-many on customers-loans? Many-to-many on customers-accounts? Why?? Employees has a branchname column instead of an FK relationship to branches. loan_operations has FK to employees!? I don't mean to stray from the topic or sound flippant, but there are so many anti-patterns here I don't even know where to begin. But I will try to help with the specific question anyway.

Q1:

CREATE PROCEDURE GetBranchSummaryByCity
    @City varchar(50)
AS
SELECT
    b.id, b.address, b.name, b.assets,
    b2.EmployeeCount, b2.CustomerCount,
    b2.TotalLoanAmount, b2.TotalAccountBalance
FROM branches b
INNER JOIN
(
    SELECT
        b.id,
        ISNULL(COUNT(DISTINCT e.id), 0) AS EmployeeCount,
        ISNULL(COUNT(c.id), 0) AS CustomerCount,
        ISNULL(SUM(l.amount), 0) AS TotalLoanAmount,
        ISNULL(SUM(a.balance), 0) AS TotalAccountBalance
    FROM branches b
    LEFT JOIN employees e
        -- Fix your schema so this matches the branch ID instead!
        ON e.branchname = b.name
    LEFT JOIN employee_customer ec
        ON ec.employeeid = e.id
    LEFT JOIN customers c
        ON c.id = ec.customerid
    LEFT JOIN customer_accounts ca
        ON ca.customerid = c.id
    LEFT JOIN accounts a
        ON a.id = ca.accountid
    LEFT JOIN loan_customer lc
        ON lc.customerid = c.id
    LEFT JOIN loans l
        ON l.id = lc.loanid
    WHERE b.Name LIKE '%' + @City + '%'
    GROUP BY b.id
) b2
ON b2.id = b.id

I'll note that you have an amount column in both loans and loan_operations. It's hard to know what the difference is between these two - it's entirely possible that loans shouldn't have this column at all, and instead it should be summed from the column in loan_operations.

Q2:

CREATE PROCEDURE FindCustomersWithLoansByDateRange
    @BeginDate datetime,
    @EndDate datetime
AS
SELECT c2.id, c2.name, c2.address, ...
FROM
(
    SELECT DISTINCT c.id
    FROM customers c
    INNER JOIN loan_customer lc
        ON lc.customerid = c.id
    INNER JOIN loans l
        ON l.id = lc.loanid
    INNER JOIN loan_operations lo
        ON lo.loanid = l.id
    WHERE lo.date BETWEEN @BeginDate AND @EndDate
) c1
INNER JOIN customers c2
ON c2.id = c1.id

Q3:

CREATE PROCEDURE GetEmployeeServiceSummaryByDateRange
    @BeginDate datetime,
    @EndDate datetime
AS
SELECT e.id, ISNULL(es.CustomerCount, 0) AS CustomerCount, e.name, e.position, ...
FROM employees e
LEFT JOIN
(
    SELECT e.id, COUNT(DISTINCT c.id) AS CustomerCount
    FROM employees e
    INNER JOIN employee_customer ec
        ON ec.employeeid = e.id
    INNER JOIN customers c
        ON c.id = ec.customerid
    INNER JOIN loan_customer lc
        ON lc.customerid = c.id
    INNER JOIN loans l
        ON l.id = lc.loanid
    INNER JOIN loan_operations lo
        ON lo.loanid = l.id
    WHERE lo.date BETWEEN @BeginDate AND @EndDate
    GROUP BY e.id
) es
ON es.id = e.id