Looking for help with a conditional statement to define new columns

Question

Looking for help with a conditional statement to define new columns

Rod Martin 136

I would like a little help using a input column to generate some additional columns of information.

Given SNValue in a SELECT, I want to create two additional columns, First_SN and Addnl_SN

Any idea how to do this?

Here's what I'm thinking

SELECT dbo.database.SNValue, CASE( something something something) AS First_SN, CASE (something something something) AS Addnl_SN

I'd love some help with the somethings.

Thanks!

EchoLiu-MSFT 14,621 Reputation points

2020-12-11T05:46:40+00:00

Experts have provided some solutions, do you have any updates？
Please also remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

Accepted answer

7 additional answers

Your answer

EchoLiu-MSFT 14,621 Reputation points

2020-12-11T05:46:40+00:00

Experts have provided some solutions, do you have any updates？
Please also remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

Answer 1

Review this one...

 CASE WHEN od.OrderNumber LIKE 'Kel%' 
      THEN 2
      WHEN od.OrderNumber LIKE 'RMA%'
        Or od.OrderNumber LIKE 'DC%'
        Or od.OrderNumber LIKE 'NW%'
        Or od.OrderNumber LIKE 'BT%'
        Or od.OrderNumber LIKE 'DC%'
        Or od.OrderNumber LIKE 'HF%'
        Or (ISNUMERIC(LEFT(od.ProductID, 1)) = 1 AND SUBSTRING(od.ProductID, 2, 1) = '-')
      THEN 5
      WHEN ISNUMERIC(SUBSTRING(od.ProductID, 1, 6)) = 1
        Or (LEFT(od.ProductID, 1) = 'K' AND ISNUMERIC(SUBSTRING(od.ProductID, 2, 4)) = 1)
      THEN 4
      ELSE 1
  END AS OrderType

Answer 2

Try somethings:

declare @table table (SNValue varchar(max))

insert @table values
( ' ' ),
( NULL ),
( '98432' ),
( '98450-98455' ),
( 'PC2232' ),
( 'PC3253-PC3256' ),
( '98456-98457  34323' )

select SNValue,
    isnull(case when a > 0 then left(SNValue, a-1) else SNValue end, '') as First_SN,
    isnull(case when a > 0 and b > 0 then ltrim(substring(SNValue, b+1, len(SNValue) - b)) end, '') as Addnl_SN
from @table
cross apply (values (charindex('-', SNValue))) t1(a)
cross apply (values (charindex(' ', SNValue, a))) t2(b)

Answer 3

Nasreen Akter 10,811 Volunteer Moderator

Hi @Rod Martin ,

You could try something like

 SELECT   
    SNValue,  
      
    CASE   
    WHEN PATINDEX('%-%', SNValue) > 0  
    THEN SUBSTRING(SNValue, 0, PATINDEX('%-%', SNValue))  
    ELSE SUBSTRING(SNValue, 0, LEN(SNValue))  
    END as First_SN,  
      
    CASE   
    WHEN PATINDEX('% %', SNValue)>0   
    THEN SUBSTRING(SNValue, PATINDEX('% %', SNValue), LEN(SNValue) )  
    ELSE ''  
    END as Addnl_SN  
      
    FROM [dbo].[database]

Answer 4

@Viorel ,

You definitely have proven your logic works. This is fantastic!

I wanted to do this outside of my full sql statement to make it simpler. I am very sorry that I am high maintenance. I am having trouble how to merge this into my existing statement. Here is the full statement, with my attempt to integrate. I know that the 'VALUE' portion in the cross apply statements do not refer to anything. I'm not sure how this is supposed to work in this way.

SELECT TOP (100) PERCENT dbo.OrderDetail.ProductID,   
                         dbo.OrderDetail.LineNumber,   
                         dbo.OrderDetail.QtyOrdered,   
                         dbo.OrderDetail.QtyInvoiced,   
                         dbo.OrderDetail.QtyScheduled,   
                         dbo.OrderDetail.QtyPicked,   
                         dbo.OrderDetail.QtyShipped,   
                         dbo.OrderDetail._SNValue,  
                         dbo.OrderDetail.QtyBackordered,   
                         dbo.OrderDetail.QtyOutstanding,   
                         dbo.OrderDetail.OrderNumber,   
                         ISNULL(CASE  
                                    WHEN a > 0  
                                    THEN LEFT(_SNValue, a - 1)  
                                    ELSE _SNValue  
                                END, '') AS First_SN,   
                         ISNULL(CASE  
                                    WHEN a > 0  
                                         AND b > 0  
                                    THEN LTRIM(SUBSTRING(_SNValue, b + 1, LEN(_SNValue) - b))  
                                END, '') AS Addnl_SN,   
                         dbo.Orders.EntryDate,   
                         dbo.OrderDetail.OrderNumber + '-' + CAST(dbo.OrderDetail.LineNumber AS VARCHAR(5)) AS OrderNumOB,   
                         dateadd(hour, 5, dbo.OrderDetail._ShipDate) AS _ShipDate2,   
                         dbo.OrderDetail._ShipDate,   
                         dbo.OrderDetail.Description,   
                         dbo.OrderDetail.ShortDescription,   
                         dbo.OrderDetail.Completed,   
                         dbo.OrderDetail.LineCancelled,   
                         dbo.OrderDetail.Price,   
                         dbo.OrderDetail.PriceUnit,   
                         dbo.OrderDetail.Amount,   
                         dbo.OrderDetail.SpecialInstructions,   
                         dbo.OrderDetail.InvoiceComment,   
                         dbo.OrderDetail.Note,   
                         dbo.OrderDetail.InventoryControlType,   
                         CAST(dbo.OrderDetail.ActivityDate AS DATE) AS ActivityDate,   
                         CAST(dbo.OrderDetail._InPlantDate AS DATE) AS _InPlantDate,   
                         dbo.OrderDetail.Reference,   
                         CAST(dbo.Orders.OrderDate AS DATE) AS OrderDate,   
                         dbo.Orders.OrderStatus,   
                         CAST(dbo.Orders.StatusDate AS DATE) AS StatusDate,   
                         dbo.Orders.StatusChangedBy,   
                         CAST(dbo.Orders.RequestedShipDate AS DATE) AS RequestedShipDate,   
                         DATEPART(year, dbo.OrderDetail._ShipDate) AS RSD_Year,   
                         DATEPART(Month, dbo.OrderDetail._ShipDate) AS RSD_Month,   
                         DATEPART(day, dbo.OrderDetail._ShipDate) AS RSD_Day,   
                         dbo.Customer.Name,   
                         dbo.Orders.SchedTotalAmount,   
                         dbo.Orders.TotalAmount,   
                         ROW_NUMBER() OVER(  
ORDER BY dbo.Orders.OrderNumber ASC) AS RowNumberTmp,   
                         DATEDIFF(month, EntryDate, GETDATE()) AS EntryDateMonthsAgo,   
                         DATEDIFF(minute, EntryDate, GETDATE()) AS EntryDateMinutesAgo,  
                         CASE  
                             WHEN(dbo.OrderDetail.QtyOrdered = dbo.OrderDetail.QtyShipped  
                                  AND dbo.OrderDetail.QtyOrdered <> 0)  
                             THEN 'Shipped'  
                             ELSE CASE  
                                      WHEN(dbo.OrderDetail.QtyPicked <> 0)  
                                      THEN 'Picked'  
                                      ELSE CASE  
                                               WHEN(dbo.OrderDetail.QtyScheduled <> 0)  
                                               THEN 'Scheduled'  
                                               ELSE CASE  
                                                        WHEN(dbo.OrderDetail.QtyInvoiced <> 0)  
                                                        THEN 'Invoiced'  
                                                        ELSE CASE  
                                                                 WHEN(dbo.OrderDetail.QtyOrdered <> 0)  
                                                                 THEN 'Ordered'  
                                                                 ELSE NULL  
                                                             END  
                                                    END  
                                           END  
                                  END  
                         END AS StatusCheck,  
                         CASE  
                             WHEN(dbo.OrderDetail.OrderNumber LIKE 'Kel%')  
                             THEN 2  
                             ELSE CASE  
                                      WHEN(dbo.OrderDetail.OrderNumber LIKE 'RMA%')  
                                      THEN 5  
                                      ELSE CASE  
                                               WHEN(dbo.OrderDetail.OrderNumber LIKE 'DC%')  
                                               THEN 5  
                                               ELSE CASE  
                                                        WHEN(dbo.OrderDetail.OrderNumber LIKE 'NW%')  
                                                        THEN 5  
                                                        ELSE CASE  
                                                                 WHEN(dbo.OrderDetail.OrderNumber LIKE 'BT%')  
                                                                 THEN 5  
                                                                 ELSE CASE  
                                                                          WHEN(dbo.OrderDetail.OrderNumber LIKE 'DC%')  
                                                                          THEN 5  
                                                                          ELSE CASE  
                                                                                   WHEN(dbo.OrderDetail.OrderNumber LIKE 'HF%')  
                                                                                   THEN 5  
                                                                                   ELSE CASE  
                                                                                            WHEN(ISNUMERIC(SUBSTRING(dbo.OrderDetail.ProductID, 1, 6)) = 1)  
                                                                                            THEN 4  
                                                                                            ELSE CASE  
                                                                                                     WHEN(ISNUMERIC(LEFT(dbo.OrderDetail.ProductID, 1)) = 1  
                                                                                                          AND SUBSTRING(dbo.OrderDetail.ProductID, 2, 1) = '-')  
                                                                                                     THEN 5  
                                                                                                     ELSE CASE  
                                                                                                              WHEN(LEFT(dbo.OrderDetail.ProductID, 1) = 'K'  
                                                                                                                   AND ISNUMERIC(SUBSTRING(dbo.OrderDetail.ProductID, 2, 4)) = 1)  
                                                                                                              THEN 4  
                                                                                                              ELSE 1  
                                                                                                          END  
                                                                                                 END  
                                                                                        END  
                                                                               END  
                                                                      END  
                                                             END  
                                                    END  
                                           END  
                                  END  
                         END AS OrderType  
FROM dbo.OrderDetail  
     INNER JOIN dbo.Orders ON dbo.OrderDetail.GUIDOrder = dbo.Orders.GUIDOrder  
     INNER JOIN dbo.Customer ON dbo.Orders.GUIDCustomer = dbo.Customer.GUIDCustomer  
     CROSS APPLY(VALUES(CHARINDEX('-', _SNValue))) t1(a)  
     CROSS APPLY(VALUES(CHARINDEX(' ', _SNValue, a))) t2(b)  
WHERE((dbo.Orders.OrderStatus = 'S')  
      OR (dbo.Orders.OrderStatus = 'C')  
      OR (dbo.Orders.OrderStatus = 'K')  
      OR (dbo.Orders.OrderStatus = ''))  
     AND (dbo.OrderDetail.QtyOrdered <> 0)  
ORDER BY OrderType DESC,   
         dbo.Orders.RequestedShipDate DESC,   
         OrderNumOB ASC;

Viorel 122.6K Reputation points

2020-12-08T21:12:30.833+00:00

Is any issue with this attempt? You can probably write dbo.OrderDetail._SNValue instead of _SNValue.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-12-08T22:53:47.707+00:00

I don't want to jump into Viorel's solution which seems fine to me, but a few general comments on your query:

That TOP (100) PERCENT serves no purpose, so I recommend that you take it out.

You can make your code more readable by using aliases. Instead of repeating dbo.OrderDetails again and again, say
FROM dbo.OrderDetail AS od
and then use od. throughout the query.

Those nested CASE are difficult to read, and they don't seem to be needed, you can do: CASE WHEN (od.QtyOrdered = od.QtyShipped AND od.QtyOrdered <> 0) THEN 'Shipped'
WHEN (odQtyPicked <> 0) THEN 'Picked'

and so one. Also, this is not a matter being pretty, but your current style could take you into problems, as SQL Server only permits a nesting level of 10 for CASE expressions.

Answer 5

Hi @Rod Martin ,

Please check if the following method can match your complete code：

create table test (SNValue varchar(25))  
      
 insert test values  
 ( ' ' ),  
 ( NULL ),  
 ( '98432' ),  
 ( '98450-98455' ),  
 ( 'PC2232' ),  
 ( 'PC3253-PC3256' ),  
 ( '98456-98457  34323' )  
   
select  SNValue,case when charindex('-', SNValue) is not null and charindex('-', SNValue)<>0  then left(SNValue,charindex('-', SNValue)-1)   
             when charindex('-', SNValue)=0 then SNValue else '' end First_SN,  
        case when charindex(' ', SNValue) is not null and charindex(' ', SNValue)>1 then right(SNValue,len(SNValue)-charindex(' ', SNValue))  
        else '' end Addnl_SN	    
 from test  
   
drop table test

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

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.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Share via

Looking for help with a conditional statement to define new columns

7 additional answers

Your answer