Updated - Declare a value and set with different results

Question

Hi I am not sure if this is possible the way I am going about this problem, but if you have an solution or alternative way to overcome this, it would be greatly appreciated.

To explain, I have a list of tracking numbers and I want to declare the "ShipDate" as the "AddedDate" and after declare the "DueDeliveryDate" as "ShipDate"+days depending what day it was shipped.

The issue here is it will update all the tracking numbers with the same date which is declared first for both "ShipDate" and "DueDeliveryDate".

See SQL code:

begin tran
Declare @ShipDate varchar(max) = (Select cast(AddedDate as date) from
                                    (select distinct a.TrackingNumber, b.AddedDate 
                                     from ConTransaction a
                                     inner join Consignment b 
                                     on a.TrackingNumber = b.TrackingNumber
                                     where a.TrackingNumber in 
                                                          (
                                                          Select TrackingNumber                                                                    
                                                          from Consignment  C WITH (NOLOCK) 
                                                          Where ShipDate > DateAdd(d, 7, Convert(smalldatetime,Convert(Varchar(10),getdate(),126)))
                                                          AND [Status] = 0                                                     
                                                          ))part01)

Declare @DueDeliveryDate datetime 

Begin

if datename(weekday,@ShipDate)  = 'Friday' 
   set @DueDeliveryDate = dateadd(day,3,@ShipDate)
if datename(weekday,@ShipDate)  = 'Saturday' 
   set @DueDeliveryDate = dateadd(day,2,@ShipDate)
else
   set @DueDeliveryDate = dateadd(day,1,@ShipDate)


Update CN
Set ShipDate = @ShipDate
,DueDeliveryDate = @DueDeliveryDate
,UpdatedBy = 'IT ADMIN'
-- Select *    
FROM dbo.Consignment CN
Where cn.TrackingNumber in (
          select distinct a.TrackingNumber 
          from ConTransaction a
          inner join Consignment b 
          on a.TrackingNumber = b.TrackingNumber
          where a.TrackingNumber in (
                       Select TrackingNumber      
                       from Consignment  C WITH (NOLOCK) 
                       Where ShipDate > DateAdd(d, 7,Convert(smalldatetime,Convert(Varchar(10),getdate(),126))) 
                       and [Status] = 0                                                     
                       and TrackingNumber in ( '65560460605' , '50454646064')
                                            ))
end


--rollback tran

Here is a snippet of the table:

TrackingNumber | ShipDate            | UpdatedBy | AddedDate           | Status | DueDeliveryDate
65560460605    | 2018-08-10 00:00:00 | NULL      | 2018-08-06 00:00:00 | 0      | 2018-09-09 00:00:00
50454646064    | 2018-08-12 00:00:00 | NULL      | 2018-08-10 00:00:00 | 0      | 2018-09-10 00:00:00

Answer 1

See solution below:

UPDATE a
SET ShipDate = a.AddedDate             
,DueDeliveryDate = CASE
                  WHEN datename(weekday,a.AddedDate)  = 'Friday' THEN 
                  dateadd(day,3,a.AddedDate)
                  WHEN datename(weekday,a.AddedDate)  = 'Saturday' THEN 
                   dateadd(day,3,a.AddedDate) 
                  ELSE dateadd(day,1,a.AddedDate) 
                  END
,UpdatedBy = 'IT ADMIN'
,UpdatedDate = getdate()
-- Select *    
FROM dbo.Consignment a
Where 
TrackingNumber in 
(
SELECT DISTINCT a.TrackingNumber 
FROM ConTransaction a
INNER JOIN Consignment b 
ON a.TrackingNumber = b.TrackingNumber
WHERE a.TrackingNumber in (SELECT TrackingNumber  
                                FROM Consignment  C WITH (NOLOCK) 
                                WHERE ShipDate > DateAdd(d, 7, 
Convert(smalldatetime,Convert(Varchar(10),getdate(),126)))
                                AND [Status] = 0
                                AND TrackingNumber IN
                                         ( 65560460605,
                                           50454646064 )
                            )
)

*So I update the "ShipDate" with the "AddedDate" then depending on the day I updated the "DueDeliveryDate" using the "AddedDate" *

If any can suggest a different title it make this question stand out a bit better feel free to let me know and I will update it.

Thanks for your help!

Answer 2

Begin

Update CN
Set ShipDate = @ShipDate
,DueDeliveryDate = case 
   when datename(weekday,@ShipDate)  = 'Friday' 
         then dateadd(day,3,@ShipDate)
   when datename(weekday,@ShipDate)  = 'Saturday' 
        then dateadd(day,2,@ShipDate)
   else dateadd(day,1,@ShipDate) end
,UpdatedBy = 'IT ADMIN'
,UpdatedDate = getdate()
-- Select *    
FROM dbo.Consignment CN
Where cn.TrackingNumber in ( **List of tracking number** ) 

Answer 3

I would rewrite it with case expression :

set @DueDeliveryDate = dateadd(day, (case datename(weekday,@ShipDate)
                                          when 'Friday' then 3
                                          when 'Saturday' then 2
                                          else 1 
                                     end), @ShipDate
                              )

update cm
     set ShipDate = @ShipDate,
         DueDeliveryDate = @DueDeliveryDate,
         UpdatedBy = 'IT ADMIN',
         UpdatedDate = getdate()  
from dbo.Consignment cn
where cn.TrackingNumber in ( **List of tracking number** );