28|SQL – Big Money Budget

[Alex_Garrett]

BYU Student Author: @Alex_Garrett
Reviewers: @Marco, @DylanKing, @Spencer
Estimated Time to Solve: 40 Minutes

We provide the solution to this challenge using:

• SQL (Microsoft Access)

Need a program? Click here.

Overview
PANIC! COVID-19 has been difficult for the audit firm because so much of your work used to be done at the client site. Additionally, travel restrictions and client withdrawal have made it even more challenging. Your manager is unsure of the firm’s future if COVID continues to disrupt its operations.

The budget for 2020 travel expenditures has been adjusted to reflect these changes, but your manager suspects that employees have been using the firm’s funds to vacation while “working from home.” While it is not the end of the year, you are tasked to investigate budget discrepancies in order to identify areas that require further investigation. Your manager shares with you the “Expense Database” which has expense data since the firm’s inception. Read the data dictionary to understand how the database is organized before running queries.

Instructions
This challenge focuses on manually typing SQL statements to retrieve the desired data. Query Design can also be used to solve this challenge, but it’s extremely helpful to be able to read SQL statements and know what is happening. I recommend manually typing the SQL statements before using Query Design. Click here for a quick video on how to get your SQL statements started.

1. Show employees who have the most missing receipts (include all years), but show only employees who are missing more than 5 receipts. Use the following headers for your output: “EmpName” and “MissingReceipts”. Show the employee with the most missing receipts first.
2. Show employees who underestimated their total costs for trips that began in 2020. Use the following headers for your output: “EmpName”, “TotalEstCosts”, “TotalActCosts”, and “Difference” (configure the “Difference” column so that an underestimated budget results in a negative difference). Round the difference column to 2 decimal places, and show the employee with the greatest difference first.
3. Show employees who spent more than their budget in 2020. Include all trips that began in 2020. Use the following headers for your output: “EmpName”, “BudgetAmount”, “Expenses”, and “Difference”. Round the difference column to 2 decimal places, and show the employee with the greatest difference first.
4. Show the company-wide budget for the past three years (2018-2020), and show how much money the company over- or under-spent for each of those years. Use the start date of trips to determine in what year we account for them. Use the following headers for your output: “BudgetYear”, “TotalBudget”, “TotalExpense”, and “Difference”. Round the expense and the difference columns to 2 decimal places and order this table by “BudgetYear” ascending.

Data Files

• Challenge28_Big_Money_Budget.accdb
• Challenge28_Data_Dictionary.docx

Suggestions and Hints

The UNION operator is a less-known tool, but it can be very useful in completing this challenge.

Solution

Solution Image

Challenge28_Solution.accdb
Solution Video: Challenge 28|SQL – Big Money Budget

[Boston]

I loved this SQL challenge! The UNION was a nice little complex wrinkle in the problem. I was reminded of the importance of the syntax in using SQL queries. Here is what I got!

[MitchFrei]

Flashback to the Junior Core! Fun to refresh my SQL skills. I didn’t use a UNION for the last question… instead I created two separate tables (one for budget and one for actuals) then joined them together on Year. Here’s my code:

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 AS 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, SUM(td.Amount) as TotalExpense
FROM TripDetail as td
LEFT JOIN Trip as 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
;

[Gage_Smith]

Here are my results for the four problems:
1)
select empname, Count as MissingReceipts from (
SELECT t.empid, td.receiptincluded, e.empname, count(t.tripid) as Count from ((trip t
inner join tripdetail td on td.tripid=t.tripid)
inner join employee e on t.empid=e.empid)
where td.receiptincluded= “N”
group by t.empid, td.receiptincluded, e.empname
order by count(t.tripid) desc)
where Count>5
;

select e.empname, e.empid, Estimatedcosts as TotalEstCosts,ExpenseAMount as TotalActCosts, Difference from employee e
inner join
(
select t.empid, sum(t.estimatedcosts) as Estimatedcosts, sum(td.ExpAmt) as ExpenseAMount, sum(t.estimatedcosts)- sum(td.ExpAmt) as Difference from trip t
inner join

(select sum(amount) as ExpAmt, tripid from tripdetail
group by tripid) td

on t.tripid=td.tripid
where year(t.startdate)=2020
group by t.empid
) dt
on e.empid=dt.empid
where Estimatedcosts<ExpenseAMount
order by difference

select * from (
select empname, b.budgetamount, sum(td.amount) as Expenses, sum(amount)-b.budgetamount as Difference from (((trip t
inner join employee e on t.empid=e.empid)
inner join budget b on b.empid=e.empid)
inner join tripdetail td on td.tripid=t.tripid)
where year(startdate)=2020
and budgetyear=2020
group by empname, b.budgetyear, b.budgetamount
)
where budgetamount <Expenses
order by Difference desc

select distinct b.budgetyear, round(yrexp.exp,2) as TotalExpense, BA as TotalAmount, round(yrexp.exp-BA,2) as Difference from

(select sum(budgetamount) as BA, budgetyear from budget
group by budgetyear) b

inner join
(
SELECT year(t.startdate) as year, sum(amount) as exp from trip t
inner join tripdetail td on td.tripid=t.tripid
group by year(t.startdate)
) yrexp
on b.budgetyear=yrexp.year
where budgetyear in (2018,2019,2020)
order by budgetyear
;

[Gage_Smith]

@Alex_Garrett
I believe that your query and subsequent results for query 2 may be incorrect. In the solution query that was posted, the join of the trip detail and trip table cause the trip data to join as many times as there is a detail record and then sum the estimated costs. So for example, employee 1581 had only one trip starting in the year 2020, but has 5 trip detail rows. Therefore, when you sum the estimated costs for the single trip id, it multiples the single estimated cost by 5, rather than retaining the single estimated cost for single trip.
I may be mistaken, so please feel free to push back! Thanks!

[Hunter_Williams]

Great practice! My solutions for queries 1-3 were very similar to the solution given, but I did my 4th query a bit differently:

SELECT B.BudgetYear, B.TotalBudget, TD.TotalExpense, ROUND((B.TotalBudget - TD.TotalExpense), 2) AS Difference
FROM (SELECT b.BudgetYear, SUM(b.BudgetAmount) AS TotalBudget
FROM Budget AS b
WHERE b.BudgetYear BETWEEN 2018 AND 2020
GROUP BY b.BudgetYear) AS B INNER JOIN (SELECT YEAR(t.StartDate) AS BudgetYear, SUM(td.Amount) AS TotalExpense
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 TD ON B.BudgetYear=TD.BudgetYear
GROUP BY B.BudgetYear, B.TotalBudget, TD.TotalExpense
ORDER BY B.BudgetYear;

Here are the results from all of my queries as well:

[Andrew]

This was a great challenge! I loved learning how to import the access database, so I could complete the challenge in SQL Server.

My solution code in T-SQL for SQL server is here:

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

--Query 2
SELECT
    e.EmpName
    , CAST(SUM(t.EstimatedCosts) AS DECIMAL(7,2)) AS TotalEstCosts
    , CAST(SUM(td.Amount) AS DECIMAL(7,2)) AS TotalActCosts
    , CAST(SUM(t.EstimatedCosts) - SUM(td.Amount) AS DECIMAL(7,2)) AS Difference
FROM 
    dbo.Employee e
    JOIN dbo.Trip t ON e.EmpID = t.EmpID
    JOIN dbo.TripDetail 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)
;

--Query 3
SELECT
    e.EmpName
    , CAST(AVG(b.BudgetAmount) AS DECIMAL(7,2)) AS BudgetAmount
    , CAST(SUM(td.Amount) AS DECIMAL(7,2)) AS TotalActCosts
    , CAST(AVG(b.BudgetAmount) - SUM(td.Amount) AS DECIMAL(7,2)) AS Difference
FROM 
    dbo.Budget b
    JOIN dbo.Employee e ON b.EmpID = e.EmpID    
    JOIN dbo.Trip t ON e.EmpID = t.EmpID
    JOIN dbo.TripDetail td ON t.TripID = td.TripID
WHERE
    b.BudgetYear = 2020
    AND YEAR(t.StartDate) = 2020
GROUP BY e.EmpName
HAVING AVG(b.BudgetAmount) - SUM(td.Amount) < 0
ORDER BY AVG(b.BudgetAmount) - SUM(td.Amount)
;

--Query 4
SELECT
    b.BudgetYear
    , CAST(b.BudgetAmount AS DECIMAL(10,2)) AS TotalBudget
    , CAST(SUM(td.Amount) AS DECIMAL(10,2)) AS TotalExpense
    , CAST(AVG(b.BudgetAmount) - SUM(td.Amount) AS DECIMAL(10,2)) AS Difference
FROM 
    ( SELECT BudgetYear, SUM(BudgetAmount) AS BudgetAmount
        FROM dbo.Budget 
        GROUP BY BudgetYear) b
    JOIN dbo.Trip t ON b.BudgetYear = YEAR(t.StartDate)
    JOIN dbo.TripDetail td ON t.TripID = td.TripID
WHERE b.BudgetYear IN (2018,2019,2020)
GROUP BY b.BudgetYear, b.BudgetAmount
ORDER BY b.BudgetYear
;

[Katie_Morgan]

Looks like a fun challenge!

[jeffwint]

Since I’m a novice to SQL, this was a challenge, especially the last query, but this was really good! My queries are below:

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 t.TripID = td.TripID
WHERE td.ReceiptIncluded = “N”
GROUP BY e.EmpName
HAVING COUNT(td.ReceiptIncluded) > 5
ORDER BY COUNT(td.ReceiptIncluded) DESC;

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

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 e.EmpID=b.EmpID) INNER JOIN Trip AS t ON e.EmpID=t.EmpID) INNER JOIN TripDetail AS td ON t.TripID=td.TripID
WHERE b.BudgetYear = 2020 AND Year(t.StartDate) = 2020
GROUP BY e.EmpName, b.BudgetAmount
HAVING b.BudgetAmount - SUM(td.Amount) < 0
ORDER BY ROUND(b.BudgetAmount - SUM(td.Amount), 2);

SELECT u.BudgetYear, SUM(u.Budget) AS TotalBudget, SUM(u.Expenses) AS TotalExpenses, SUM(u.Budget) -SUM(u.Expenses) AS Difference
FROM (SELECT b.BudgetYear, SUM(b.BudgetAmount) AS Budget, 0 AS Expenses
FROM Budget 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 t INNER JOIN TripDetail td ON t.TripID=td.TripID
WHERE YEAR(t.StartDate) BETWEEN 2018 AND 2020
GROUP BY YEAR(t.StartDate)) AS u
GROUP BY u.BudgetYear;

[Garrett_Jones]

I’m pretty slow with SQL but this was a good opportunity to practice.

-Query 1
SELECT EmpName , COUNT(ReceiptIncluded) AS MissingReceipts FROM dbo.Employee e JOIN dbo.Trip t ON e.EmpID = t.EmpID JOIN dbo.TripDetail td ON t.TripID = td.TripID WHERE ReceiptIncluded = ‘N’ GROUP BY EmpName HAVING COUNT(ReceiptIncluded) > 5 ORDER BY COUNT(ReceiptIncluded) DESC ;

–Query 2
SELECT e.EmpName , CAST(SUM(t.EstimatedCosts) AS DECIMAL(7,2)) AS TotalEstCosts , CAST(SUM(td.Amount) AS DECIMAL(7,2)) AS TotalActCosts , CAST(SUM(t.EstimatedCosts) - SUM(td.Amount) AS DECIMAL(7,2)) AS Difference FROM dbo.Employee e JOIN dbo.Trip t ON e.EmpID = t.EmpID JOIN dbo.TripDetail 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) ;

–Query 3
SELECT e.EmpName , CAST(AVG(b.BudgetAmount) AS DECIMAL(7,2)) AS BudgetAmount , CAST(SUM(td.Amount) AS DECIMAL(7,2)) AS TotalActCosts , CAST(AVG(b.BudgetAmount) - SUM(td.Amount) AS DECIMAL(7,2)) AS Difference FROM dbo.Budget b JOIN dbo.Employee e ON b.EmpID = e.EmpID JOIN dbo.Trip t ON e.EmpID = t.EmpID JOIN dbo.TripDetail td ON t.TripID = td.TripID WHERE b.BudgetYear = 2020 AND YEAR(t.StartDate) = 2020 GROUP BY e.EmpName HAVING AVG(b.BudgetAmount) - SUM(td.Amount) < 0 ORDER BY AVG(b.BudgetAmount) - SUM(td.Amount)

–Query 4
SELECT b.BudgetYear , CAST(b.BudgetAmount AS DECIMAL(10,2)) AS TotalBudget , CAST(SUM(td.Amount) AS DECIMAL(10,2)) AS TotalExpense , CAST(AVG(b.BudgetAmount) - SUM(td.Amount) AS DECIMAL(10,2)) AS Difference FROM ( SELECT BudgetYear, SUM(BudgetAmount) AS BudgetAmount FROM dbo.Budget GROUP BY BudgetYear) b JOIN dbo.Trip t ON b.BudgetYear = YEAR(t.StartDate) JOIN dbo.TripDetail td ON t.TripID = td.TripID WHERE b.BudgetYear IN (2018,2019,2020) GROUP BY b.BudgetYear, b.BudgetAmount ORDER BY b.BudgetYear ;

[luisg99]

Good challenge, I had to look up some things and ask about some others learned a lot from it! I decide to also sort them in a way that I though they were better to look at going always from big to small either difference or in case or query 1 by the amount of missing receipts as you can see in the highlighted columns of the screenshots.

[Morgan_M_Hansen]

This was a great practice problem as we are learning about these problems currently in the junior core. The first two problems were also great reviews from my IS 201 class.

[Stevey_W]

Today in class we discussed the different between using WHERE and HAVING. It was fun to practice that during this challenge. Once again, thanks for the helpful solution video as well to explain some confusing parts of the 4th Query. Here is my SQL for that one:

[Genevieve_Nichols]

Q1: SELECT e.EmpName, COUNT(td.ReceiptIncluded) AS MissingReceipts
FROM (Employee AS e INNER JOIN Trip ON e.EmpID = Trip.EmpID) INNER JOIN TripDetail AS td ON Trip.TripID = td.TripID
WHERE td.ReceiptIncluded = “N”
GROUP BY e.EmpName
HAVING COUNT(td.ReceiptIncluded) > 5
ORDER BY COUNT(td.ReceiptIncluded) 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(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 ON e.EmpID = Trip.EmpID) INNER JOIN TripDetail AS td ON Trip.TripID = td.TripID
WHERE b.BudgetYear = 2020 AND YEAR(Trip.StartDate) = 2020
GROUP BY e.EmpName, b.BudgetAmount
HAVING (b.BudgetAmount - SUM(td.Amount)) < 0
ORDER BY (b.BudgetAmount - SUM(td.Amount));

Q4: SELECT b.BudgetYear, SUM(b.BudgetAmount) AS TotalBudget, ROUND(SUM(td.Amount), 2) AS TotalExpense, ROUND(SUM(b.BudgetAmount) - SUM(td.Amount), 2) AS Difference
FROM ((Budget AS b INNER JOIN Employee ON b.EmpID = Employee.EmpID) INNER JOIN Trip AS t ON Employee.EmpID = t.EmpID) INNER JOIN TripDetail AS td ON t.TripID = td.TripID
WHERE b.BudgetYear IN (2018, 2019, 2020) AND YEAR(t.StartDate) IN (2018, 2019, 2020)
GROUP BY b.BudgetYear
ORDER BY b.BudgetYear;

[Jane_Cai]

This challenge was harder than I thought. It wasn’t necessarily hard in terms of concept, but it helped me be more careful with my syntax and order of operations.

Originally, I did Q4 with a multi-joint table (budget, employee, trip, and trip detail). When comparing with the solution, I realized that I was over-summing expenses and under-summing revenue by only doing an inner join with all the tables mentioned above. Using union allows me to sum budget and expenses separately before subtracting the two which avoids the multiplication effect with joining tables. It was a great learning experience!

Q1
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 t.TripID=td.TripID

WHERE td.ReceiptIncluded=“N”

GROUP BY e.EmpName

HAVING Count(td.ReceiptIncluded)>5

ORDER BY Count(td.ReceiptIncluded) 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 RIGHT(t.StartDate, 4)=2020

GROUP BY e.EmpName

HAVING Round(SUM(t.EstimatedCosts)-SUM(td.Amount), 2)<0

ORDER BY Round(SUM(t.EstimatedCosts)-SUM(td.Amount), 2);

Q3
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 e.EmpID = b.EmpID)
INNER JOIN Trip AS t ON e.EmpID = t.EmpID)
INNER JOIN TripDetail AS td ON t.TripID = td.TripID
WHERE
b.BudgetYear = 2020
AND YEAR(t.StartDate) = 2020
GROUP BY
e.EmpName, b.BudgetAmount
HAVING
(b.BudgetAmount - SUM(td.Amount)) < 0
ORDER BY
ROUND(b.BudgetAmount - SUM(td.Amount), 2);

Q4
SELECT
b.BudgetYear,
SUM(o.Budget) AS TotalBudget,
SUM(o.Expenses) AS TotalExpense,
SUM(o.Budget) - SUM(o.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 o
GROUP BY o.BudgetYear;

[Claire_Campbell]

Time to complete: 30 Minutes
Difficulty: intermediate
Solution:

1. SELECT EmpName, count() AS MissingReceipts
   FROM (Employee
   INNER JOIN trip ON employee.empID = trip.empID)
   INNER JOIN tripdetail ON trip.tripid = tripdetail.tripid
   WHERE receiptincluded = ‘N’
   GROUP BY EmpName
   HAVING Count() > 5
   ORDER BY Count(*) DESC;
2. SELECT EmpName, EstimatedCosts AS TotalEstCosts, TripDetail.Amount AS TotalActCosts, ROUND((EstimatedCosts - TripDetail.Amount), 2) AS Difference
   FROM (Employee
   INNER JOIN trip ON employee.empid = trip.empid)
   INNER JOIN tripdetail ON trip.tripid = tripdetail.tripid
   WHERE StartDate >= 1/1/2020
   ORDER BY ROUND((EstimatedCosts - TripDetail.Amount), 2) DESC;
3. SELECT EmpName, sum(BudgetAmount), sum(Amount) AS Expenses, ROUND((sum(BudgetAmount)-sum(Amount)), 2)
   FROM ((Budget
   INNER JOIN Employee ON Budget.EmpID = Employee.EmpID)
   INNER JOIN trip ON employee.empid = trip.empid)
   INNER JOIN tripdetail ON trip.tripid = tripdetail.tripid
   WHERE Budgetyear >= 2020
   GROUP BY EmpName
   ORDER BY ROUND((sum(BudgetAmount)-sum(Amount)), 2) DESC;
4. SELECT BudgetYear, SUM(BudgetAmount) AS TotalBudget, ROUND(SUM(Amount), 2) AS TotalExpense, ROUND(SUM(BudgetAmount)-SUM(amount), 2) AS Difference
   FROM ((Budget
   INNER JOIN employee ON budget.empid = employee.empid)
   INNER JOIN trip ON employee.empid = trip.empid)
   INNER JOIN tripdetail ON trip.tripid = tripdetail.tripid
   WHERE startdate BETWEEN #2018-01-01# AND #2020-12-31#
   GROUP BY BudgetYear
   ORDER BY BudgetYear ASC;

After reviewing the solution, I reworked these queries:
2. SELECT EmpName, sum(EstimatedCosts) AS TotalEstCosts, sum(TripDetail.Amount) AS TotalActCosts, ROUND((sum(EstimatedCosts) - sum(TripDetail.Amount)), 2) AS Difference
FROM (Employee
INNER JOIN trip ON employee.empid = trip.empid)
INNER JOIN tripdetail ON trip.tripid = tripdetail.tripid
WHERE Year(StartDate) = 2020
GROUP BY EmpName
HAVING sum(EstimatedCosts) - sum(TripDetail.Amount) < 0
ORDER BY ROUND((sum(EstimatedCosts) - sum(TripDetail.Amount)), 2) ASC;
3. SELECT EmpName, budget.BudgetAmount, sum(Amount) AS Expenses, ROUND(budget.BudgetAmount-sum(Amount), 2) AS difference
FROM ((Budget
INNER JOIN Employee ON Budget.EmpID = Employee.EmpID)
INNER JOIN trip ON employee.empid = trip.empid)
INNER JOIN tripdetail ON trip.tripid = tripdetail.tripid
WHERE Budgetyear = 2020 AND Year(StartDate) = 2020
GROUP BY EmpName, Budget.BudgetAmount
HAVING budget.budgetamount - sum(amount) < 0
ORDER BY ROUND(budget.BudgetAmount-sum(Amount), 2) ASC;
4. SELECT x.BudgetYear,
SUM(x.Budget) AS TotalBudget,
SUM(x.Expenses) AS TotalExpense,
SUM(x.Budget) - SUM(x.Expenses) AS Difference
FROM (Select b.budgetyear,
sum(b.budgetamount) AS Budget,
0 as expenses
FROM Budget 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 t
INNER JOIN TripDetail td ON t.TripID = td.TripID
WHERE YEAR(t.StartDate) BETWEEN 2018 AND 2020
GROUP BY YEAR(t.StartDate)) x
GROUP BY x.Budgetyear

I learned how to use subqueries! Wow I did not even know that that function existed.

[Spencer_Hakes]

Time to Complete: 20
Difficulty: Intermediate
Solutions:

1. SELECT e.EmpName, COUNT(td.ReceiptIncluded) AS MissingReceipts
   FROM (Employee e INNER JOIN Trip t ON e.EmpID = t.EmpID)
   INNER JOIN TripDetail td ON t.TripID = td.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))) AS Difference
   FROM (Employee AS e INNER JOIN Trip t ON e.EmpID = t.EmpID)
   INNER JOIN TripDetail 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) ;
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 ON e.EmpID = Trip.EmpID) INNER JOIN TripDetail AS td ON Trip.TripID = td.TripID
   WHERE b.BudgetYear = 2020 AND YEAR(Trip.StartDate) = 2020
   GROUP BY e.EmpName, b.BudgetAmount
   HAVING (b.BudgetAmount - SUM(td.Amount)) < 0
   ORDER BY (b.BudgetAmount - SUM(td.Amount));
4. SELECT b.BudgetYear, SUM(b.BudgetAmount) AS TotalBudget, ROUND(SUM(td.Amount), 2) AS TotalExpense, ROUND(SUM(b.BudgetAmount) - SUM(td.Amount), 2) AS Difference
   FROM ((Budget AS b INNER JOIN Employee ON b.EmpID = Employee.EmpID) INNER JOIN Trip AS t ON Employee.EmpID = t.EmpID) INNER JOIN TripDetail AS td ON t.TripID = td.TripID
   WHERE b.BudgetYear IN (2018, 2019, 2020) AND YEAR(t.StartDate) IN (2018, 2019, 2020)
   GROUP BY b.BudgetYear
   ORDER BY b.BudgetYear;

[Rylee_Schriever]

1 hour
intermediate

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

SELECT e.EmpName, j.TotalEstCosts, j.TotalActCosts, ROUND((j.TotalEstCosts-j.TotalActCosts),2)AS Difference

FROM ((Employee e INNER JOIN

(SELECT e.EmpName, SUM(t.EstimatedCosts) AS TotalEstCosts, SUM(td.Amount) AS TotalActCosts

FROM ((Employee e INNER JOIN Trip t ON e.EmpId=t.EmpId)

INNER JOIN TripDetail td ON t.TripID=td.TripID)

WHERE YEAR(t.StartDate) = 2020

GROUP BY e.EmpName) AS j ON e.EmpName=j.EmpName)

INNER JOIN Trip t ON e.EmpID=t.EmpID)

WHERE Year(t.StartDate) = 2020 AND j.TotalEstCosts<j.TotalActCosts

GROUP BY e.EmpName, j.TotalEstCosts, j.TotalActCosts

ORDER BY ROUND((j.TotalEstCosts-j.TotalActCosts),2)

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

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 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 t INNER JOIN TripDetail 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;

[David_Hulme]

Time to Complete: 45 mins
Difficulty: Intermediate
Solutions:

Query 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 t.TripID=td.TripID
WHERE td.ReceiptIncluded =‘N’
GROUP BY e.EmpName
HAVING Count(td.ReceiptIncluded) > 5
ORDER BY Count(td.ReceiptIncluded) DESC;

Query 2:
SELECT e.EmpName, Sum(t.EstimatedCosts) as TotalEstCosts, Sum(td.Amount) as TotalActCosts, Round(Sum(t.EstimatedCosts)-Sum(td.Amount),2)
From (Employee e
Inner Join Trip t on e.EmpID=t.EmpID)
Inner Join TripDetail td on t.TripID=td.TripID
Where Year(StartDate)=2020
Group by e.EmpName
Having Sum(t.EstimatedCosts)-Sum(td.Amount) < 0
order by Sum(t.EstimatedCosts)-Sum(td.Amount)

Query 3:
SELECT e.EmpName, b.BudgetAmount, Sum(td.Amount) as Espenses, Round(b.BudgetAmount-Sum(td.Amount),2) as Difference
From ((budget b
Inner join employee e on b.EmpID=e.EmpID)
Inner join trip t on e.EmpID=t.EmpID)
Inner join TripDetail td on t.TripID=td.TripID
Where Year(StartDate)=2020 and b.BudgetYear =2020
Group by e.EmpName, b.BudgetAmount
HAVING b.BudgetAmount - SUM(td.Amount) < 0
order by 4 ASC

Query 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 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 t Inner join TripDetail td on t.TripID=td.TripID
Where Year(t.StartDate) Between 2018 and 2020
Group by Year(t.StartDate)) x
Group by x.BudgetYear

[Katie_S_Richards]

Time to complete: 20
Difficulty: easy
Solution:

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 t.tripid = td.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) ;
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 e.empid = t.empID) INNER JOIN tripdetail as td ON t.tripid = td.TripID
   WHERE b.budgetyear = 2020 AND YEAR(t.startdate) = 2020
   GROUP BY e.EmpName, b.BudgetAmount
   HAVING b.budgetamount - sum(td.amount) < 0
   ORDER BY b.budgetamount - sum(td.amount) ASC;
4. SELECT b.budgetyear, SUM(b.BudgetAmount) AS TotalBudget, ROUND(SUM(td.amount),2) as TotalExpense, ROUND(SUM(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 e.empid=t.empid) INNER JOIN tripdetail as td ON t.tripid = td.TripID
   WHERE b.budgetyear IN (2018,2019,2020) AND YEAR(t.startdate) IN (2018,2019,2020)
   GROUP BY b.BudgetYear
   ORDER BY b.budgetyear ASC;