New Requirements were introduced: Agent can change Manager and we need to calcul

Question

New Requirements were introduced: Agent can change Manager and we need to calculate commissions according to it.

1. Create new table Agent_Manger ( agent_id, manager_id, date_assignment)

2. Populate it with Max Date from your SalesOrder as "cut-off date "

3. Insert 3 new records into AgentManager table with to show that the Agents Changed their Manger After "cut-off date"

4. Insert 5 new records into SalesOrder table with the SalesOrder dates After dates in your step 3 ( so Commissions for the Agent will be different after "cut-off dates"

5. Write SQL to Select Agent, TotalAmount, Commissions, SalesOrderDate.

make sure to see records that show Agent different Commissions on different SalesOrderDate

Product Family Key Field Type Key Field Type Key Field Type ManagerName varchar(30) CommisionsPct float ManagerPhone char(12) AgentName varchar(30) AgentPhone char(12) PK AgentID FK ManagerlD int int ProductDescr varchar(100) Unit Price PK ProductID int ManagerAgent FK FK Key DeAssign date ManagerlD int AgentID int Sales Order OrderDate date Market Total Amt int PK MarketlD Type MarketDeahar50) FK CustID FK ProductID FK AgentlD PK OrderNo Customer Customer Market int PK Customer Name varchar(50) FK CustID int int FK MarketlD int Customer Phone char(12) achar(20) Customer Zip Customer ID

Explanation / Answer

There are a few clarifications I need with this question:

1. You have asked to populate Agent Manager Table with Max_date as cut_off_date.So what I have done is I have populated the table and every record has been given a cut off date.

2. You said, the Manager can change after cut off date. But what is the criteria to select the next manager

Please give the clarifications so that I can proceed further.

CREATE Agent_Manger (

   agent_id   INT              NOT NULL,

   manager_id INT     NOT NULL,

   date_assignment       DATETIME

   PRIMARY KEY (ID)

);

Insert into Agent_Manger   agent_id   ,   manager_id select ManagerID, AgentID from ManagerAgent -- (to insert the agent id and manager id from already existing table)

Insert into Agent_Manger      date_assignment      SELECT MAX(OrderDate) FROM Sales_Order (Cut off date is selected and populated)

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.