28|SQL – Big Money Budget

Challenges
, , ,
21

Time to Complete 45
Difficulty: Intermediate-Advanced
This problem had three intermediate problems and one advanced problem.
Solutions:

  1. SELECT e.EmpName, COUNT(td.ReceiptIncluded)
    FROM (Employee AS e INNER JOIN Trip AS t ON e.EmpID=t.EmpID) INNER JOIN TripDetail AS td ON td.TripID=t.TripID
    WHERE td.ReceiptIncluded=“N”
    GROUP BY e.EmpName
    HAVING COUNT(td.ReceiptIncluded) > 5
    ORDER BY COUNT(td.ReceiptIncluded) DESC;

  2. SELECT e.EmpName, SUM(t.EstimatedCosts) AS TotalEstCosts, SUM(td.Amount) AS TotalActCosts, ROUND(SUM(t.EstimatedCosts) - SUM(td.Amount) ,2) AS Difference
    FROM (Employee AS e INNER JOIN Trip AS t ON e.EmpID=t.EmpID) INNER JOIN TripDetail AS td ON td.TripID=t.TripID
    WHERE YEAR(t.StartDate) = 2020
    GROUP BY e.EmpName
    HAVING SUM(t.EstimatedCosts) - SUM(td.Amount) < 0
    ORDER BY SUM(t.EstimatedCosts) - SUM(td.Amount);

  3. SELECT e.EmpName, b.BudgetAmount, SUM(td.amount ) AS Expenses, ROUND((b.BudgetAmount - SUM(td.Amount)),2) AS Difference
    FROM ((Budget AS b INNER JOIN Employee AS e ON b.EmpID=e.EmpID) INNER JOIN Trip AS t ON t.EmpID=e.EmpID) INNER JOIN TripDetail AS td ON td.TripID=t.TripID
    WHERE YEAR( t.StartDate )= 2020 AND b.BudgetYear = 2020
    GROUP BY e.EmpName, b.BudgetAmount
    HAVING (b.BudgetAmount - SUM(td.Amount)) <0
    ORDER BY (b.BudgetAmount - SUM(td.Amount));

  4. SELECT x.BudgetYear, SUM(x.budget) AS TotalBudget, SUM(x.Expenses) AS TotalExpenses, SUM(x.budget) - SUM(x.Expenses) AS Difference
    FROM (SELECT b.BudgetYear, SUM(b.BudgetAmount) AS Budget, 0 AS Expenses
    FROM Budget AS b
    WHERE b.BudgetYear BETWEEN 2018 AND 2020
    GROUP BY b.BudgetYear
    UNION
    SELECT YEAR(t.StartDate) AS BudgetYear, 0 AS Budget, ROUND(SUM(td.Amount),2) AS Expenses
    FROM Trip AS t INNER JOIN TripDetail AS td ON t.TripID=td.TripID
    WHERE YEAR(t.StartDate) BETWEEN 2018 AND 2020
    GROUP BY YEAR(t.StartDate)) AS x
    GROUP BY x.BudgetYear;

22

Time to complete: 30 mins
Difficulty: Intermediate

  1. SELECT e.EmpName, COUNT(td.ReceiptIncluded) AS MissingReceipts
    FROM (Employee AS e INNER JOIN Trip AS t ON e.EmpID=t.EmpID) INNER JOIN TripDetail AS td ON td.TripID=t.TripID
    WHERE td.ReceiptIncluded = “N”
    GROUP BY e.EmpName
    HAVING Count(td.ReceiptIncluded) >5
    ORDER BY COUNT(td.ReceiptIncluded) DESC;

  2. SELECT e.EmpName, Sum(t.EstimatedCosts) AS TotalEstCosts, SUM(td.Amount) AS TotalActCosts, ROUND(SUM(t.EstimatedCosts)-Sum(td.Amount),2) AS Difference
    FROM (Employee AS e INNER JOIN Trip AS t ON e.EmpID=t.EmpID) INNER JOIN TripDetail AS td ON t.TripID=td.TripID
    WHERE Year(t.StartDate) =2020
    GROUP BY e.EmpName
    HAVING SUM(t.EstimatedCosts)-Sum(td.Amount) <0
    ORDER BY SUM(t.EstimatedCosts)-Sum(td.Amount) DESC;

  3. SELECT e.EmpName, b.BudgetAmount, SUM(td.Amount) AS Expenses, ROUND(b.BudgetAmount-SUM(td.Amount),2) AS Difference
    FROM ((Employee AS e INNER JOIN Budget AS b ON b.EmpID=e.EmpID) INNER JOIN Trip AS t ON e.EmpID=t.EmpID) INNER JOIN TripDetail AS td ON td.TripID=t.TripID
    WHERE Year(t.StartDate) =2020
    AND b.BudgetYear=2020
    GROUP BY e.EmpName, b.BudgetAmount
    HAVING b.BudgetAmount < Sum(td.Amount)
    ORDER BY b.BudgetAmount-SUM(td.Amount);

  4. SELECT bb.BudgetYear, bb.TotalBudget, ee.TotalExpense, ROUND(bb.TotalBudget-ee.TotalExpense,2) AS Difference
    FROM (SELECT b.BudgetYear, SUM(b.BudgetAmount) as TotalBudget
    FROM Budget b
    WHERE b.BudgetYear in (2018,2019,2020)
    GROUP BY b.BudgetYear
    ORDER BY b.BudgetYear ASC) AS bb
    INNER JOIN (SELECT Year(t.StartDate) as BudgetYear, ROUND(SUM(td.Amount),2) as TotalExpense
    FROM TripDetail as td
    LEFT JOIN Trip t on td.TripID=t.TripID
    WHERE Year(t.StartDate) in (2018,2019,2020)
    GROUP BY Year(t.StartDate)
    ORDER BY Year(t.StartDate) ASC) AS ee ON bb.BudgetYear=ee.BudgetYear;