New Requirements were introduced: Agent can change Manager and we need to calcul
ID: 3726509 • Letter: N
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 IDExplanation / 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)