Hello All,
Hope you are doing well!...I am trying to determine the status of the vehicle on order -Whether it is stock or Sold , whether it is part of the BAM campaign or not and the sold or stock date based on the following set of rules
-
If the data column orderstatus has *SLD ,SLD or SOLD then the comment column is sold ; if the orderstatus column has STOCK or *STOCK or STK followed by date (like STK 05/13) or *STK followed by date then the comment column is stock
-
if the orderstatus column has * or BAM or any date (For ex: 5/13,05/17/2022) then the BAMYN column would be BAM
-
For the Soldorstockdate output column if the column orderstatus has a date then that date would be the soldorstock date otherwise the date in the column orddate would be the Soldorstockdate
Please find the DDL for the input and output tables...Can you please help me here..
Input table
create table ##input
(segment varchar(20),
mmodel varchar(40),
brand varchar(30),
orderstatus varchar(100),
orddate date)
insert into ##input values
('maka','M12E4','Nimg','*SLD 05/11/2022','5/1/2022'),
('sika','KL6781','Cheung','37141 SLD BAM','5/5/2022'),
('kloi','NB1290','Vloti','1277489 STK#39298.32831','5/4/2022'),
('Ping','BN1289','gower','36888 SLD FLOREN ANGEL','5/6/2022'),
('Melow','VB1901','operw','1286664 051222','5/10/2022'),
('Bekow','XC901','mewar','*SLD 5/14/22 Heman','5/3/2022'),
('Nakin','JH121','korew','STOCK','5/16/2022'),
('Verura','CV123','thilla','1287002 LONGMINT','5/12/2022'),
('Chaluli','BN8901','dora','STOCK BAM 5/17/22','5/11/2022'),
('Kroger','XC123','iops','*STOCK BAM 5/23/22','5/8/2022'),
('beqow','VB123','pirar','3902120 STOCK','5/20/2022'),
('Viast','NM41W','kolpe','SOLD BRANDON BOX 36790','5/15/2022'),
('Chimmin','BN123','tyrow','STK 5/13','5/3/2022'),
('Bellow','Vio23','Callow','*STK 5/13/22','5/5/2022'),
('Nalla','Krowmin','Gilqa','37938 STOCK 5/18/22 PER SARA','5/18/2022')
Output table
create table ##output
(segment varchar (20),
mmodel varchar(40),
brand varchar(30),
orderstatus varchar(100),
orddate date,
comment varchar(40),
BAMYN varchar(10),
soldorstockdate date)
insert into ##output values
('maka','M12E4','Nimg','*SLD 05/11/2022','5/1/2022','Sold','BAM','5/11/2022'),
('sika','KL6781','Cheung','37141 SLD BAM','5/5/2022','Sold','BAM','5/5/2022'),
('kloi','NB1290','Vloti','1277489 STK#39298.32831','5/4/2022','','',''),
('Ping','BN1289','gower','36888 SLD FLOREN ANGEL','5/6/2022','Sold','','5/6/2022'),
('Melow','VB1901','operw','1286664 051222','5/10/2022','','',''),
('Bekow','XC901','mewar','*SLD 5/14/22 Heman','5/3/2022','Sold','BAM','5/14/2022'),
('Nakin','JH121','korew','STOCK','5/16/2022','Stock','','5/16/2022'),
('Verura','CV123','thilla','1287002 LONGMINT','5/12/2022','','',''),
('Chaluli','BN8901','dora','STOCK BAM 5/17/22','5/11/2022','Stock','BAM','5/17/2022'),
('Kroger','XC123','iops','*STOCK BAM 5/23/22','5/8/2022','Stock','BAM','5/23/2022'),
('beqow','VB123','pirar','3902120 STOCK','5/20/2022','Stock','','5/20/2022'),
('Viast','NM41W','kolpe','SOLD BRANDON BOX 36790','5/15/2022','Sold','','5/15/2022'),
('Chimmin','BN123','tyrow','STK 5/13/2022','5/3/2022','Stock','BAM','5/13/2022'),
('Bellow','Vio23','Callow','*STK 5/13/22','5/5/2022','Stock','BAM','5/13/2022'),
('Nalla','Krowmin','Gilqa','37938 STOCK 5/18/22 PER SARA','5/18/2022','Stock','BAM','5/18/2022')
Thanks,
Arun