建议条件所需的存储程序 [英] store procedure required by suggested condition
问题描述
create table Table1
(
ProductID int identity primary key,
Product varchar(50),
Type varchar(50),
Qty int,
Rate money,
NetAmt money,
Customer varchar(50),
CustomerAdd varchar(50),
Process int
)
insert into Table1 values('Dove', 'Shampoo', 15,50,750,'Bharat','Andheri',3)
insert into Table1 values('Dove', 'Shampoo', 5,51,255,'Bharat','Andheri',3)
insert into Table1 values('Dove', 'Shampoo', 10,50.50,505,'Bharat','Andheri',1)
insert into Table1 values('Dove', 'Shampoo', 20,49,980,'Bharat','Andheri',1)
insert into Table1 values('Lux', 'Soap', 10,20,200,'Bharat','Andheri',1)
insert into Table1 values('Vatika', 'Shampoo', 20,55,1100,'Nisha','Ville Parle',1)
insert into Table1 values('Lux', 'Soap', 15,20,300,'Kusum','Bhandup',3)
insert into Table1 values('Vatika', 'Shampoo', 40,54,2160,'Nisha','Ville Parle',1)
create table Table2
(
PID int identity primary key,
Product varchar(50),
Type varchar(50),
NetQty int,
NetAmt money,
AverageAmt money,
Customer varchar(50),
MinRate money,
MaxRate money,
)
insert into Table2 values('Dove', 'Shampoo', 20,1005,50.25,'Bharat',50,51)
满足要求的建议步骤。
1创建视图 calc on table1 其中 process = 1
总和数量和净金额，获得最小最高费率和分组产品，类型，客户
2支票 table2 组合产品，类型，客户
2 if（产品，类型，客户） table2 中不存在该组合，在 table2 中插入一个带有派生结果的新行。
3 if（ product，type，customer ）组合存在于table2中，将派生结果添加到net qty和net amt。更新最小和最大费率，比较现有和从查看结果
4 set process = 2 其中 process = 1 in table1
5 drop view
6 set average amt as net amt / net qty
查看calc
Suggested steps to meet the Requirement.
1 "create view calc on table1 where process=1
Sum the quantity and Net Amount,get min max rate and group by Product , type, customer"
2 Check table2 for combination product, type,customer
2 if (product,type,customer) combination does not exist in table2 ,insert a new row in table2 with the derived result.
3 if (product,type,customer) combination exist in table2 ,add the derived result to the net qty and net amt. update the min and max rate, comparing the existing and from view result
4 set process=2 where process=1 in table1
5 drop view
6 set average amt as net amt / net qty
view calc
Product Type (Qty) (Net Amt) (Customer) (Min Rate) (Max Rate)
column1 column2 column3 column5 column6 column14 column15 Process
Dove Shampoo 30 1485.00 Bharat 49.00 50.50 1
Lux soap 10 200.00 Bharat 20.00 20.00 1
Vatika Shampoo 60 3260.00 Nisha 54.00 55.00 1
Lux soap 15 300.00 Kusum 20.00 20.00 1
预期产出
表2
Expected Output
Table2
Product Type (Net Qty) (Net Amt) (Average Amt) (Customer) (Min Rate) (Max Rate)
column8 column9 column10 column11 column12 column13 column14 column15
Dove Shampoo 50 2490.00 49.8 Bharat 49.00 51.00
Lux soap 10 200.00 20 Bharat 20.00 20.00
Lux soap 60 3260.00 54.33333333 Nisha 54.00 55.00
Vatika Shampoo 15 300.00 20 Kusum 20.00 20.00
推荐答案
我们不做你的作业：这是有原因的。它就是为了让你思考你被告知的事情，并试着理解它。它也在那里，以便您的导师可以识别您身体虚弱的区域，并将更多的注意力集中在补救措施上。
亲自尝试，你可能会发现它不是和你想的一样困难！
We do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you are weak, and focus more attention on remedial action.
Try it yourself, you may find it is not as difficult as you think!
这篇关于建议条件所需的存储程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
