Explore BrainMass

SQL Statements for Order Processing Database (Moonlight Distributors)

See the attached file.

One of your team members are a little rusty on their SQL skills, but needed to create the SQL Select statements that would produce running summary files for reports on the following:

Customer volume by month
CustomerID, Month, Total shipments, Total weight
Driver performance by month
Driver ID, Month, Number of manifests on time, Number of manifests delivered late.

The team member sent you an e-mail with the following questions:
* From where should he source the information for the design?
* What difficulties would he have in writing the SQL statements for each of these tables?
* Should any of these tables be created on an as-needed basis?
* Do these summary tables violate the concept of table normalization?


Solution Preview

**********Solution Description******************************

For Customer Volume by Month:
Report 1
Month Number of Customers
Jan 1000
Feb 50

SQL for Summary Customers by Month with delivery complete

SELECT TO_CHAR( Date, 'MON') As Month, COUNT(CustomerID) As [Number of
FROM Manifest
WHERE Manifest.Complete = 1

Report 2
For Customer ID, Month, Total Shipments, Total Weight:
CustID Month ShipTotal WeightTotal
1 Jan 2000 5000
1 Feb 20 100
1 Mar 0 0
1 Apr 0 0
1 May 0 0
1 June 400 800
1 July ...
2 Jan 50 175

2 Feb 35 205

SQL for Summary Customers Volume by Month with delivery complete

SELECT CustomerID , TO_CHAR( Date, 'MON') As Month,
Count(PickupDetails.ManifestID) As [Total Shipments], SUM(
PickupDetails.TotalWeight) As [Total Weight]

FROM Manifest, PickupDetails

WHERE Manifest.Complete = 1 And PickupDetails.ManifestID =
OrderBy Date

Report 3

Driver Performance by Month

For All Drivers
Month On time Late
Jan 1000 65
Feb 2000 125

SQL for Summary of Delivery Stats for Each Month All Drivers

SELECT TO_DATE( Date, 'MON') AS Month, COUNT( * ) WHERE Date <=
Scheduled-date As [On Time], COUNT(*) WHERE Date > Scheduled_date As
FROM Delivery

Report 4
DriverID Month OnTime Late
1234 Jan 40 5
1234 Feb 20 20
1234 Mar 50 0
2345 Jan 100 0
2345 Feb 110 10
SQL for Summary of Delivery Stats for Each Month by Driver

Solution Summary

The goal of this solution is to assist the student in describing data relationships using SQL, and deciding on the appropriate steps to take in deciding on what form of SQL syntax is necessary to implement the SQL statements to create summary reports.