question

ArunChandramouli-6978 avatar image
0 Votes"
ArunChandramouli-6978 asked ArunChandramouli-6978 commented

Getting the order status of the vehicles on order

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

1) 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

2) if the orderstatus column has * or BAM or any date (For ex: 5/13,05/17/2022) then the BAMYN column would be BAM

3) 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


sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How do I explicitly recognize a date in the 'orderstatus' column?


Does this field contain a date? 1286664 051222

You have to make sure that a string like this - in order it should be a date - is always formatted correctly; otherwise, it is absolutely impossible to write a filter for that.

0 Votes 0 ·

Hi @BjoernPeters ...Sorry for nOt being clear ...Only the ones in the column orderstatus that have date in the format mm/dd/yy or mm/dd/yyyy or mm/dd

0 Votes 0 ·

1 Answer

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered ArunChandramouli-6978 commented

Hi @ArunChandramouli-6978
Check this:

 SELECT *,CASE WHEN PATINDEX('%S%LD%',orderstatus)>0 THEN 'Sold'
               WHEN PATINDEX('%STOCK%',orderstatus)>0 OR PATINDEX('%STK%[0-9]/[0-9]%',orderstatus)>0 THEN 'Stock' ELSE'' END AS comment 
         ,CASE WHEN PATINDEX('%[0-9]/[0-9]%',orderstatus)>0 OR CHARINDEX('*',orderstatus)>0 OR CHARINDEX('BAM',orderstatus)>0
               THEN 'BAM' ELSE'' END AS BAMYN 
         ,CASE WHEN PATINDEX('%[0-9]/[0-9]%',orderstatus)>0 
               THEN CAST(SUBSTRING(orderstatus,CHARINDEX('/',orderstatus)-2,5)+'/2022' AS DATE)
               ELSE orddate END AS soldorstockdate 
 FROM ##input

Best regards,
LiHong


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @LiHongMSFT-3908 ..This works !..Really appreciate your help!

0 Votes 0 ·