ASSignment

Accounts Receivable Assignment

Steps:

Always start by looking at what the answer should be so you know if you have the right answer
Determine what methods need to be undertaken to achieve the result

Add 2% of invoice amount to total interest and put it in the current month interest charges for invoices more than 1 month old

Steps:

List current interest charges for each customer name (1 line per customer)

Steps:

SELECT customer_name,
       current_month_interest_charges      
FROM ar_invoices
INNER JOIN ar_customers
ON ar_invoices.customer# = ar_customers.customer#

customer_name	current_month_interest_charges
bill	0.00
ed	5.00
john	10.00
ed	0.00

List total owing (amount + interest) for each customer name (1 line per customer)

Steps:

SELECT customer_name,
       (invoice_amount + total_interest_charges) AS TotalOwing
FROM ar_invoices
INNER JOIN ar_customers
ON ar_invoices.customer# = ar_customers.customer#

customer_name	TotalOwing
bill	100.00
ed	60.00
john	90.00
ed	80.00

List total sales for each salesperson name for invoices created during the last year

Steps:

SELECT salesperson_name,
       SUM(invoice_amount) AS TotalSales
FROM ar_salespeople s
INNER JOIN ar_invoices i
ON s.salesperson# = i.salesperson#
WHERE invoice_date >= GETDATE() - 365
GROUP BY salesperson_name

salesperson_name	TotalSales
bob	180.00
mary	120.00

List total sales for each province for invoices created during the last month

Steps:

SELECT customer_province,
       SUM(invoice_amount) AS SalesByProvince
FROM ar_customers c
INNER JOIN ar_invoices i
ON c.customer# = i.customer#
WHERE (invoice_date >= GETDATE() - 30)
GROUP BY customer_province

customer_province	SalesByProvince
ab	170.00
bc	80.00

Find sales people with no sales

Steps:

OUTER JOIN

SELECT salesperson_name,
       invoice#
FROM ar_salespeople FULL OUTER JOIN ar_invoices
    ON ar_invoices.salesperson# = ar_salespeople.salesperson#
WHERE invoice# IS NULL

salesperson_name	invoice#
tim	NULL

Find the best customers (rating 2) who have purchased more than 1 times from us during the last year

Steps:

WHY DOES THIS WORK?

SELECT customer_name, count(*) AS NumRows
FROM ar_customers c inner join ar_invoices i ON c.customer#=i.customer#
WHERE customer_rating=2 and getdate() - invoice_date <= 365
GROUP BY customer_name
HAVING count(*) > 1

customer_name	NumRows
ed	2

List the salespeople who sell to the worst customers (rating 3)

Steps:

SELECT salesperson_name,
       customer_name,
	   customer_rating
FROM ar_salespeople s, ar_invoices i , ar_customers c
WHERE s.salesperson# = i.salesperson# AND i.customer# = c.customer# AND customer_rating = 3

salesperson_name	customer_name	customer_rating
mary	john	3

Find the salesperson with the most sales

Steps:

Select TOP 1 of the SUM of all salespersons invoices
JOIN the name to the invoice

SELECT TOP 1 salesperson_name,
             SUM(invoice_amount) AS Total
FROM ar_invoices
INNER JOIN ar_salespeople
ON ar_invoices.salesperson# = ar_salespeople.salesperson#
GROUP BY salesperson_name

salesperson_name	Total
bob	180.00

SQL Assignment

Accounts Receivable Assignment