How to write this statement with another way best without repeate team id ? RRS feed

  • Question

  • User696604810 posted

    I work on SQL server 2012 i update two fields depend on every team id

    update dpt
    set Package=case when tmp.TeamId=1 then 1 end ,
    PackageDate=case when tmp.TeamId=1 then getdate() end ,
    Parametric=case when tmp.TeamId=2 then 2 end,
    ParametricDate=case when tmp.TeamId=2 then getdate() end ,
    Scribing=case when tmp.TeamId=3 then 3 end,
    ScribingDate=case when tmp.TeamId=3 then getdate() end ,
    Lifecycle=case when tmp.TeamId=4 then 4 end,
    LifecycleDate=case when tmp.TeamId=4 then getdate() end 
    from PCN.DocumentPartTeams dpt inner join #Temp tmp on dpt.PartID=tmp.PartID and dpt.DocumentID=tmp.DocumentID
    and tmp.Status is null

    I need to rewrite statement with another way best as following

    because i repeat same condition multi time

    i need to write it one time only meaning as below

    case when teamid=2

    then date=getdata and parametric=2

    so how to do that please ?

    Monday, March 23, 2020 6:50 PM

All replies

  • User-1330468790 posted

    Hi, ahmedbarbary,


    As you said that "I need to write it one time only meaning as below case when teamid=2 then date=getdata and parametric=2", I am afraid that CASE WHEN could not be used in this way . If you would not like to use CASE WHEN , you can use IFF to change your script . Logical Functions - IIF (Transact-SQL)


    But unfortunately, it still can't change to that repeat situation. According to the code you give now, I have no method to change your code at present. If forcing a change, it may make your code look more complicated.


    Could you please share us your table structure and some sample data along with your expected result? These information might provide a right direction and I will make some test. Thanks in advance .


    Best regards,


    Wednesday, March 25, 2020 8:25 AM