# 28|SQL – Big Money Budget

**URL:** <https://www.techhub.training/t/28-sql-big-money-budget/220>\
**Category:** Challenges\
**Tags:** intermediate, managerial, sql, budget\
**Created:** [March 27, 2023, 4:41pm UTC](https://www.techhub.training/t/28-sql-big-money-budget/220 "2023-03-27T16:41:29Z")\
**Posts on this page:** 3\
**Page:** 2

<div class="post-metadata">

**Author:** ![Meilan\_Rhoton](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/meilan_rhoton/32/7386_2.png) [@Meilan\_Rhoton](https://www.techhub.training/u/Meilan_Rhoton)\
**Post date:** [March 14, 2025, 1:28am UTC](https://www.techhub.training/t/28-sql-big-money-budget/220/21 "2025-03-14T01:28:34Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![Yoomin\_Jang](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/yoomin_jang/32/7460_2.png) [@Yoomin\_Jang](https://www.techhub.training/u/Yoomin_Jang)\
**Post date:** [March 15, 2025, 5:02am UTC](https://www.techhub.training/t/28-sql-big-money-budget/220/22 "2025-03-15T05:02:39Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![Sterling\_Lane](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/sterling_lane/32/399_2.png) [@Sterling\_Lane](https://www.techhub.training/u/Sterling_Lane)\
**Post date:** [April 9, 2025, 9:51pm UTC](https://www.techhub.training/t/28-sql-big-money-budget/220/23 "2025-04-09T21:51:21Z")

</div>

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:

```auto
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:

```auto
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:

```auto
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:

```auto
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

```

[Previous page](https://www.techhub.training/t/28-sql-big-money-budget/220.md?page=1)