Hi All,
Hope you are doing well!.. I am trying to generate the new promotions list based on a set of rules in my model constraints table...
Following are the constraints at a vendor level:
-
The length in days for which the promotion should run
-
The frequency in days after which a new promotion should be started for that item
-
The number of days that a vendor takes to approve the promotion request
-
The percentage of SKU's that have to be put on promotion
-
The number of SKU's that have to be put on promotion
The Master table is the one which is the data feed of items that have to be put on promotion,The previousperiod table is the one which is the list of items that were put in promotion previously --The same SKU's should not put into promotion the next time...So those SKU's need to be excluded and the remaining from the master list should be given the priority and the number of SKU'S to be taken should be taken based on the conditions in the modelconstraints table (percentage of SKU's and number of SKU'S)...
The current cost should be taken from the Master table and the proposed cost should be calculated based on the discount that is available at the vendor level in the modelconstraints table for that vendor and the duration of the promotion should be decided based on the conditions in the modelconstraints table and taking into account the frequency and the data in the previous period table the promotion period should be decided for the output table...Can you please help me here... I am trying to get the output table as the answer
create table #master
(vendorname varchar(50),
itemid varchar(20),
currentcost int,
proposedcost int,
discount int,
[priority] int)
insert into #master values
('chao ','1234','50','40','25','1'),
('chao ','1235','50','40','25','2'),
('chao ','1236','50','40','25','3'),
('chao ','1237','50','40','25','4'),
('chao ','1238','50','40','25','5'),
('chao ','1239','50','40','25','6'),
('chao ','1240','50','40','25','7'),
('chao ','1241','50','40','25','8'),
('chao ','1242','50','40','25','9'),
('chao ','1243','50','40','25','10'),
('chao ','1244','50','40','25','11'),
('chao ','1245','50','40','25','12'),
('chao ','1246','50','40','25','13'),
('chao ','1247','50','40','25','14'),
('chao ','1248','50','40','25','15'),
('chao ','1249','50','40','25','16'),
('chao ','1250','50','40','25','17'),
('chao ','1251','50','40','25','18'),
('Fiasco ','1300','40','25','60','1'),
('Fiasco ','1301','40','25','60','2'),
('Fiasco ','1302','40','25','60','3'),
('Fiasco ','1303','40','25','60','4'),
('Fiasco ','1304','40','25','60','5'),
('Fiasco ','1305','40','25','60','6'),
('Fiasco ','1306','40','25','60','7'),
('Fiasco ','1307','40','25','60','8'),
('Fiasco ','1308','40','25','60','9'),
('Fiasco ','1309','40','25','60','10'),
('Fiasco ','1310','40','25','60','11'),
('Fiasco ','1311','40','25','60','12'),
('Fiasco ','1312','40','25','60','13'),
('Fiasco ','1313','40','25','60','14'),
('Fiasco ','1314','40','25','60','15'),
('Fiasco ','1315','40','25','60','16'),
('Fiasco ','1316','40','25','60','17'),
('Fiasco ','1317','40','25','60','18'),
('Fiasco ','1318','40','25','60','19'),
('Fiasco ','1319','40','25','60','20'),
('Fiasco ','1320','40','25','60','21'),
('Fiasco ','1321','40','25','60','22'),
('Fiasco ','1322','40','25','60','23'),
('Fiasco ','1323','40','25','60','24'),
('Fiasco ','1324','40','25','60','25'),
('Fiasco ','1325','40','25','60','26'),
('Fiasco ','1326','40','25','60','27'),
('Fiasco ','1327','40','25','60','28'),
('Fiasco ','1328','40','25','60','29'),
('Fiasco ','1329','40','25','60','30'),
('Fiasco ','1330','40','25','60','31'),
('Fiasco ','1331','40','25','60','32'),
('Fiasco ','1332','40','25','60','33')
create table #previousperiod
(vendorname varchar(30),
itemid int,
currentcost int,
proposedcost int,
discount int,
promostartdate datetime2,
promoenddate datetime2,
priority int
)
insert into #previousperiod values
('chao ','1234','50','40','25','2019-08-05 00:00:00.000','2019-08-25 00:00:00.000','1'),
('chao ','1235','50','40','25','2019-08-05 00:00:00.000','2019-08-25 00:00:00.000','2'),
('chao ','1236','50','40','25','2019-08-05 00:00:00.000','2019-08-25 00:00:00.000','3'),
('chao ','1237','50','40','25','2019-08-05 00:00:00.000','2019-08-25 00:00:00.000','4'),
('chao ','1238','50','40','25','2019-08-05 00:00:00.000','2019-08-25 00:00:00.000','5'),
('chao ','1239','50','40','25','2019-08-05 00:00:00.000','2019-08-25 00:00:00.000','6'),
('chao ','1240','50','40','25','2019-08-05 00:00:00.000','2019-08-25 00:00:00.000','7'),
('Fiasco ','1300','40','25','60','2019-08-05 00:00:00.000','2019-08-30 00:00:00.000','1'),
('Fiasco ','1301','40','25','60','2019-08-05 00:00:00.000','2019-08-30 00:00:00.000','2'),
('Fiasco ','1302','40','25','60','2019-08-05 00:00:00.000','2019-08-30 00:00:00.000','3'),
('Fiasco ','1303','40','25','60','2019-08-05 00:00:00.000','2019-08-30 00:00:00.000','4'),
('Fiasco ','1304','40','25','60','2019-08-05 00:00:00.000','2019-08-30 00:00:00.000','5'),
('Fiasco ','1305','40','25','60','2019-08-05 00:00:00.000','2019-08-30 00:00:00.000','6')
create table #modelconstraints
(vendor varchar(30),
length int,
frequency int,
daysforapproval int,
percofskus float,
countofskus int,
maxdiscountnormal int,
maxdiscountholiday int)
insert into #modelconstraints
values
('Chao','20','15','10','0.1','','10','15'),
('Fiasco ','25','45','5','','5','15','15')
create table #output
(vendorname varchar(30),
itemid int,
promostartdate varchar(50),
promoenddate varchar(50),
prepromoprice int,
promoprice int,
promodiscountperc int,
priority int)
insert into #output values
('chao ','1241','20190905','20190925','50','45','10','1'),
('chao ','1242','20190905','20190925','50','45','10','2'),
('Fiasco ','1306','20190906','20191020','40','34','15','1'),
('Fiasco ','1307','20190906','20191020','40','34','15','2'),
('Fiasco ','1308','20190906','20191020','40','34','15','3'),
('Fiasco ','1309','20190906','20191020','40','34','15','4'),
('Fiasco ','1310','20190906','20191020','40','34','15','5')
Thanks,
Arun