# 167|SQL – Annual Order Reports

**URL:** <https://www.techhub.training/t/167-sql-annual-order-reports/2391>\
**Category:** Challenges\
**Tags:** alteryx, intermediate, sql, realdata, orderreports\
**Created:** [April 9, 2024, 1:25am UTC](https://www.techhub.training/t/167-sql-annual-order-reports/2391 "2024-04-09T01:25:09Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Andrew\_Wilson](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/andrew_wilson/32/5487_2.png) [@Andrew\_Wilson](https://www.techhub.training/u/Andrew_Wilson)\
**Post date:** [April 9, 2024, 1:25am UTC](https://www.techhub.training/t/167-sql-annual-order-reports/2391/1 "2024-04-09T01:25:10Z")

</div>

_BYU Student Author_: @Andrew_Wilson  
_Reviewers_: @Benjamin_Lau, @Millie_K_B  
_Estimated Time to Solve:_ 30 Minutes

We provide the solution to this challenge using:

- SQL

Need a program? Click [here](https://www.techhub.training/t/information-about-software-used-on-techhub-training/138).

**Overview**  
At Simple Screenprinting we produce branded apparel for customers and businesses. As businesses order higher quantities of goods, they receive a bulk discount. Some companies choose to purchase large quantities of branded goods and then sell them to their employees as a way of fundraising for charities they are associated with. One such company is called VistaPoint Properties. In our system, we have two accounts for VistaPoint Properties. The first account is for when the corporate office places a bulk order, the second is for their employees to purchase the goods and donate to the fundraiser. At the end of each year, Simple Screenprinting runs a report to identify how much money VistaPoint Properties has raised and sends VistaPoint Properties= a check.

The report you are preparing should contain only the amount that we owe to VistaPoint Properties. You need to figure out how many items were ordered by VistaPoint employees during 2019. For each item that was ordered, we will be paying $5 to VistaPoint Charity. You can distinguish between orders placed by the corporate office and orders placed by employees through the order name or the CustomerID. Orders placed by Employees are placed using the CustomerID “2842”.

**Instructions**

1. Download and access the Access Database
2. Generate a report showing the following information  
a. JT\_Num  
b. CustomerID  
c. OrderDate  
d. QtyOrdered
3. Make sure all rows have all fields filled out
4. We are only interested in orders placed in 2019
5. Sort by the JT\_Num in descending order

**Data Files**

- [Challenge167\_Data](https://byu.box.com/shared/static/n77rmsxfxy06w5rczqjlpfppikfim2zx)
- [Challenge167\_Data\_Dictionary](https://byu.box.com/shared/static/6y5p1jlcsx9yhohpkw3yz77boqr1361e)

> **Suggestions and Hints**
>
> 1. Instead of filtering by order name, it may be easiest to figure out which CustomerID is associated with employee orders and filter based on that
> 2. When filtering based on the date, try using #XXXX/XX/XX#

**Solution**

Here are your first 10 rows of data:  
 ![image](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/0/08d833e43963bba66b4e4e6d2df0b55b4d7dc5c6.png)  
There should be 129 rows of data total  
[Challenge167\_Solution.yxmd](https://byu.box.com/shared/static/ntyfhfdwqixpvz6wlq0858blgiyo6tg4)  
_Solution Video:_ [Challenge 167|SQL – Annual Order Reports](https://youtu.be/FM0-PQDarbE)

---

<div class="post-metadata">

**Author:** ![Scott\_Johnston](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/scott_johnston/32/7418_2.png) [@Scott\_Johnston](https://www.techhub.training/u/Scott_Johnston)\
**Post date:** [March 14, 2025, 4:23am UTC](https://www.techhub.training/t/167-sql-annual-order-reports/2391/3 "2025-03-14T04:23:43Z")

</div>

Time to complete: 20 min  
Difficulty: Beginner/Intermediate  
Solution:

 ![image](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/f/fca4a63cfbd905368c119ffa71c5e2e0ec24b2da.png)