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;

23

Time to Complete: 40 Minutes
Rating: Intermediate

This was a great challenge! I was looking to refresh my SQL skills and I thought this one hit the spot between too easy and too challenging for me to help with that. Due to the “join distance” between the budget and actuals tables, I found it simpler and cleaner to create two separate tables to join together instead of taking the Union approach for Q4. Here’s what I got!

Q1:

Select EmpName, COUNT(ReceiptIncluded) AS MissingReceipts
FROM
(SELECT * FROM (Employee AS e LEFT JOIN Trip AS t ON e.EmpID = t.EmpID) LEFT JOIN TripDetail AS td ON t.TripID = td.TripID
WHERE td.ReceiptIncluded = "N")
GROUP BY EmpName
HAVING COUNT(ReceiptIncluded) > 5
ORDER BY 2 DESC;

Q2:

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);

Q3:

SELECT e.EmpName, b.BudgetAmount, SUM(td.Amount) AS Expenses, ROUND(SUM(td.Amount) - b.BudgetAmount,2) AS Difference
FROM (((SELECT EmpID, BudgetAmount FROM Budget WHERE BudgetYear = 2020) AS b INNER JOIN Employee AS e ON b.EmpID = e.EmpID) 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, b.BudgetAmount
ORDER BY (SUM(td.Amount) - b.BudgetAmount) DESC;

Q4:

SELECT BudgetYear, TotalBudget, TotalExpense, ROUND(TotalBudget - TotalExpense, 2) AS Difference
FROM
   (SELECT b.BudgetYear, SUM(b.BudgetAmount) AS TotalBudget
   FROM Budget AS b
   WHERE b.BudgetYear > 2017
   GROUP BY b.BudgetYear) AS bb
   INNER JOIN
   (SELECT YEAR(t.StartDate) AS ExpenseYear, ROUND(SUM(td.Amount),2) AS TotalExpense
   FROM Trip AS t INNER JOIN TripDetail AS td ON t.TripID = td.TripID
   WHERE YEAR(t.StartDate) > 2017
   GROUP BY YEAR(t.StartDate)) AS tt
   ON bb.BudgetYear = tt.ExpenseYear