代码之家 › 专栏 › 技术社区 › user3496218

向datetime添加数字以获取新日期SQL Server

case type-conversion datetime sql-server sql

user3496218 · 技术社区 · 6 年前

我正在尝试获取下面的查询,以便根据该列中的值为“所需运输时间”列指定一个数字值-如果以a开头,则为10,如果以C开头,则为5。从那里,我想将该数字添加到修订后的出厂价中,以获得新的出厂价。我从未尝试过这样做,使用下面的查询,我得到的结果是日期加上文本“期望的运输时间”——这是有意义的,因为它是一个字符串。如何获取列中的值而不是第20行中列的文本名称?有人告诉我 datetime 去掉时间成分的值是一个很好的方法。

理想的结果是,根据“期望运输时间”的值,在日期后再增加5天或10天

SQL:

USE PDX_SAP_USER

GO 

SELECT E.team_member_name [EMPLOYEE], 
       K.business_segment_desc [BUSINESS SEGMENT],
       G.order_status [GPS ORDER STATUS],
       H.po_type [PO TYPE],
       G.order_no [GPS ORDER NO],
       I.po_number [SAP PO NUMBER],
       I.shipping_instruct [SAP SHIP MODE],
       G.shipping_type [GPS SHIP MODE], 
       CASE 
            WHEN I.shipping_instruct LIKE 'A%'
            THEN '10'
            WHEN I.shipping_instruct LIKE 'C%'
            THEN '5'
       END  [DESIRED TRANSIT TIME], 
       CONVERT(VARCHAR(12),I.revised_ex_factory,101) [LAST CONFIRMED DATE ], 
       CONVERT(VARCHAR(12),I.revised_ex_factory,101) + 'DESIRED TRANSIT TIME' AS 'PROPOSED ETA',
       CONVERT(VARCHAR(12),S.po_estimated_deliv_date,101) [CURRENT DELIV DATE], 
      -- 'days_diff'
       I.material [MATERIAL],
       M.description [DESCRIPTION],
       I.stock_category [STOCK CATEGORY],
       I.po_ordered_quantity [PO ORDERED QUANTITY],
       I.po_recvd_quantity [PO RECVD QUANTITY],
       I.po_balance_quantity [PO BALANCE QUANTITY], 
       I.po_intransit_quantity [PO INTRANSIT QUANTITY], 
       I.plant_code [PLANT], 
       I.direct_ship_code [DS CODE],   
       I.comment [COMMENT]


FROM   (SELECT    order_no,
                  order_status, 
                  shipping_type

FROM asagdwpdx_prod.dbo.SimoxOrder1

UNION ALL

SELECT            order_no,
                  order_status, 
                  shipping_type

FROM asagdwpdx_prod.dbo.SimoxOrder2

UNION ALL 

SELECT            order_no,
                  order_status, 
                  shipping_type

FROM asagdwpdx_prod.dbo.SimoxOrder3) G 

JOIN   pdx_sap_user..vw_po_header H ON G.order_no = H.ahag_number
JOIN   pdx_sap_user..vw_po_item I ON H.po_number = I.po_number 
JOIN   pdx_sap_user..vw_po_size S ON I.po_number = S.po_number 
AND    I.po_item_number = S.po_item_number 
JOIN   pdx_sap_user..vw_mm_material M ON I.material = M.material 
JOIN   pdx_sap_user..vw_kd_business_segment K ON M.business_segment_code = K.business_segment_code
JOIN   adi_user_maintained..scm_po_employee_name E ON I.po_number = E.po_number 


WHERE  I.po_balance_quantity > 0 
AND    I.del_indicator NOT IN ('L','S')
AND    H.PO_TYPE NOT IN ('01','UB')
AND    I.shipping_instruct IN ('A1','A2','A5','C1','C2','C3')

样本结果:

04/30/2016DESIRED TRANSIT TIME

1 回复 | 直到 6 年前

Xedni 6 年前

所以你有两个选择。第一个是复制 CASE 声明您有第二次,并将其与日期连接起来。这可能是最直截了当的,尽管您最终会复制该代码。这是一个小的重复,但仍然如此。如果这让您感到困扰,或者您还需要在查询的其他地方使用它,那么您需要将整个内容包装在子查询中,以便可以引用 case 语句,好像它是一个独立的列。e、 g。

select
    ...
    DesiredTransitTime = 
        case
            when I.shipping_instruct LIKE 'A%'
            then '10'
            when I.shipping_instruct LIKE 'C%'
            then '5'
        end,
    ProposedETA = convert(varchar(12),I.revised_ex_factory,101) +
        case
            when I.shipping_instruct LIKE 'A%'
            then '10'
            when I.shipping_instruct LIKE 'C%'
            then '5'
        end
    ...

或

select 
    [DESIRED TRANSIT TIME],
    [PROPOSED ETA = StringDate + [DESIRED TRANSIT TIME]
    ...
from 
(
    select 
        StringDate = convert(varchar(12), I.revised_ex_factory,101) ,
        [DESIRED TRANSIT TIME] = 
            case
                when I.shipping_instruct LIKE 'A%'
                then '10'
                when I.shipping_instruct LIKE 'C%'
                then '5'
            end,
    ...
) a