none
Case structure RRS feed

  • Question

  • Hi,

    I have a scenario in which I allocate a ratio to employees depending on availability of a product.

    Employees, get a '100 %' allocation always, but based on a ratio.

    The Default ratio is 40 : ORA, 40 : PEA, 20 : GUA, to make a 'total' of 100% always.

    So, if GUA, is 0, then I need to adjust ORA to 50, and PEA to 50, to get 100%

    if PEA is 0, then I need to adjust ORA to 60 and GUA to 40, to get 100%

    if ORA and PEA are both 0, then I need to adjust GUA to 100%

    Please assist.

    Many thanks,

    Monday, September 16, 2019 5:26 AM

Answers

  • If Excel picture represents the expected results, then check this preliminary query:

    ;
    with Q as
    (
        select EmpNo, ISNULL([GUA], 0) as GUA, ISNULL([PEA], 0) AS PEA, ISNULL([ORA], 0) AS ORA
        from 
        (
            select EmpNo, Product, [Value]
            from tttt
        ) as t
        pivot
        (
            MAX([Value])
            for Product in ([GUA], [PEA], [ORA] ) 
        ) as p
    ),
    GUAonly as 
    (
        select EmpNo, 1.0 as GUA, 0.0 as PEA, 0.0 as ORA
        from Q
        where GUA <> 0 and PEA = 0 and ORA = 0
    ),
    PEAonly as 
    (
        select EmpNo, 0 as GUA, 1 as PEA, 0 as ORA
        from Q
        where GUA = 0 and PEA <> 0 and ORA = 0
    ),
    ORAonly as 
    (
        select EmpNo, 0 as GUA, 0 as PEA, 1 as ORA
        from Q
        where GUA = 0 and PEA = 0 and ORA <> 0
    ),
    noGUA as
    (
        select EmpNo, 0 as GUA, 0.5 as PEA, 0.5 as ORA
        from Q
        where GUA = 0 and PEA <> 0 and ORA <> 0
    ),
    noPEA as
    (
        select EmpNo, 0.4 as GUA, 0 as PEA, 0.6 as ORA
        from Q
        where GUA <> 0 and PEA = 0 and ORA <> 0
    ),
    noORA as
    (
        select EmpNo, 0.4 as GUA, 0.6 as PEA, 0 as ORA
        from Q
        where GUA <> 0 and PEA <> 0 and ORA = 0
    ),
    [All] as
    (
        select EmpNo, 0.2 as GUA, 0.4 as PEA, 0.4 as ORA
        from Q
        where GUA <> 0 and PEA <> 0 and ORA <> 0
    ),
    U as 
    (
        select * from GUAonly
        union all
        select * from PEAonly
        union all
        select * from ORAonly
        union all
        select * from noGUA
        union all
        select * from noPEA
        union all
        select * from noORA
        union all
        select * from [All]
    ),
    T as 
    (
        select U.EmpNo, U.GUA * Q.GUA + U.PEA * Q.PEA + U.ORA * Q.ORA as Total
        from U
        inner join Q on Q.EmpNo = U.EmpNo
    )
    update tttt
    set Total = T.Total
    from T
    where T.EmpNo = tttt.EmpNo

     

    But if your INSERT statements represent the expected results, then it is not clear how 0.56731455334575664 and 0.3337084073996468 were calculated.

     


    • Edited by Viorel_MVP Tuesday, September 17, 2019 4:24 AM Spelling.
    • Marked as answer by RonTech1 Tuesday, September 17, 2019 7:40 AM
    Monday, September 16, 2019 10:41 AM
  • Hi Ron,

    Is this what you want? 

    CREATE TABLE [dbo].[tttt](
    [EmpNo] [float] NULL,
    [Month] [float] NULL,
    [Value] [float] NULL,
    [Product] [nvarchar](255) NULL,
    [Total] [float] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Product], [Total]) VALUES
    (566, 201908, 0.604535398230088,  N'GUA', 0.58811937153823712),
    (566, 201908, 0.520541324311261,  N'ORA', 0.56731455334575664),
    (566, 201908, 0.647489405419288,  N'PEA', 0.3337084073996468),
    (-34, 201908, 0.510526315789473, N'GUA', NULL),
    (-34, 201908, 0,  N'ORA', NULL),
    (-34, 201908, 0.589720789614085,  N'PEA', NULL),
    (-33, 201908, 0.42627960275019, N'GUA', NULL),
    (-33, 201908, 0,  N'ORA', NULL),
    (-33, 201908, 0.604535398230088,  N'PEA', NULL),
    (-32, 201908, 0.480732177263969, N'GUA', NULL),
    (-32, 201908, 0.489274106175514, N'ORA', NULL),
    (-32, 201908, 0.579157516470353,  N'PEA', NULL),
    (-31, 201908, 0.538853885388538, N'ORA', NULL),
    (-31, 201908, 0,N'GUA', NULL),
    (-31, 201908, 0.649993294890706,  N'PEA', NULL),
    (-30, 201908, 0.63296488946684, N'ORA', NULL),
    (-30, 201908, 0.654368932038834, N'GUA', NULL),
    (-30, 201908, 0.732786631595234,  N'PEA', NULL),
    (-29, 201908, 0.551873198847262, N'GUA', NULL),
    (-29, 201908, 0.60219957081545, N'ORA', NULL),
    (-29, 201908, 0.659031682896721,  N'PEA', NULL),
    (-28, 201908, 0,  N'GUA', NULL),
    (-28, 201908, 0.56548418024928, N'ORA', NULL),
    (-28, 201908, 0.665600730927364, N'PEA', NULL),
    (-26, 201908, 0.530063729564976, N'ORA', NULL),
    (-26, 201908, 0.571220930232558,N'GUA', NULL)
    
    ;with cte1 as (
    select * from (
    select EmpNo,Month,[Value],Product from 
    tttt)s
    pivot 
    (sum(value)for Product in ([GUA],[PEA],[ORA]))PVT
    ),
    cte2 as (
    select empno,month,GUA,CASE 
    when GUA<>0 AND PEA<>0 AND ORA <>0 THEN 0.2
    when GUA=0 AND PEA<>0 AND ORA <>0 THEN 0 
    when GUA<>0 AND PEA=0 AND ORA <>0 THEN 0.4
    when GUA<>0 AND PEA<>0 AND ORA =0 THEN 0.4
    when GUA=0 AND PEA=0 AND ORA <>0 THEN 0
    when GUA=0 AND PEA<>0 AND ORA =0 THEN 0
    when GUA<>0 AND PEA=0 AND ORA =0 THEN 1
    else null
    end as ratiogua,
    PEA,CASE 
    when GUA<>0 AND PEA<>0 AND ORA <>0 THEN 0.4
    when GUA=0 AND PEA<>0 AND ORA <>0 THEN 0.5
    when GUA<>0 AND PEA=0 AND ORA <>0 THEN 0
    when GUA<>0 AND PEA<>0 AND ORA =0 THEN 0.6
    when GUA=0 AND PEA=0 AND ORA <>0 THEN 0
    when GUA=0 AND PEA<>0 AND ORA =0 THEN 1
    when GUA<>0 AND PEA=0 AND ORA =0 THEN 0
    else null
    end as ratiopea,
    ORA,CASE 
    when GUA<>0 AND PEA<>0 AND ORA <>0 THEN 0.4
    when GUA=0 AND PEA<>0 AND ORA <>0 THEN 0.5
    when GUA<>0 AND PEA=0 AND ORA <>0 THEN 0.6
    when GUA<>0 AND PEA<>0 AND ORA =0 THEN 0
    when GUA=0 AND PEA=0 AND ORA <>0 THEN 1
    when GUA=0 AND PEA<>0 AND ORA =0 THEN 0
    when GUA<>0 AND PEA=0 AND ORA =0 THEN 0
    else null
    end as ratioora
    from cte1)
    
    select *, GUA*ratiogua+ pea* ratiopea+ ORA*ratioora as total
    from cte2
    
    /*
    empno  month    GUA                    ratiogua  PEA                    ratiopea   ORA                    ratioora   total
    ------ -------- ---------------------- --------- ---------------------- ---------- ---------------------- ---------- ----------------------
    -34    201908   0.510526315789473      0.4       0.589720789614085      0.6        0                      0.0        0.55804300008424
    -33    201908   0.42627960275019       0.4       0.604535398230088      0.6        0                      0.0        0.533233080038129
    -32    201908   0.480732177263969      0.2       0.579157516470353      0.4        0.489274106175514      0.4        0.523519084511141
    -31    201908   0                      0.0       0.649993294890706      0.5        0.538853885388538      0.5        0.594423590139622
    -30    201908   0.654368932038834      0.2       0.732786631595234      0.4        0.63296488946684       0.4        0.677174394832596
    -29    201908   0.551873198847262      0.2       0.659031682896721      0.4        0.60219957081545       0.4        0.614867141254321
    -28    201908   0                      0.0       0.665600730927364      0.5        0.56548418024928       0.5        0.615542455588322
    -26    201908   0.571220930232558      NULL      NULL                   NULL       0.530063729564976      NULL       NULL
    566    201908   0.604535398230088      0.2       0.647489405419288      0.4        0.520541324311261      0.4        0.588119371538237
    */

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, September 17, 2019 2:07 AM

All replies

  • Hi RonTech1,

    Please provide a DDL, sample data population, logic, and desired output.

    Monday, September 16, 2019 5:32 AM
  • Hi Ron,

    Could you post a script with CREATE TABLE + INSERT statements with sample data and the expected result of that sample data?  So that we’ll get a right direction and make some tests.

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 16, 2019 5:36 AM
  • CREATE TABLE #t (col1 CHAR(3), ratio INT)

    INSERT INTO #t VALUES ('ORA', 40)
    INSERT INTO #t VALUES ('PEA', 20)
    INSERT INTO #t VALUES ('GUA', 40)


    DECLARE @col1 CHAR(3)='ORA', @col2 CHAR(3)='PEA'
    DECLARE @ratio INT=0

    SELECT col1,
    CASE WHEN @col1='GUA' AND @ratio=0 AND @col2='' THEN
         CASE WHEN col1='ORA' OR  col1='PEA' THEN 50 ELSE 0 END 
    ELSE CASE WHEN @col1='PEA' AND @ratio=0 AND @col2='' THEN 
    CASE WHEN col1='ORA' THEN 60  
         WHEN col1='GUA' THEN 40  ELSE  0 END 

       ELSE CASE WHEN @col1='ORA' AND @ratio=0 AND @col2='PEA ' THEN  
            CASE WHEN col1='GUA' THEN 100 ELSE 0 END   END 
               
       
    END END ratio
    FROM #t

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 16, 2019 5:54 AM
    Answerer
  • Hi 

    Please see below

    CREATE TABLE [dbo].[table1](
    [Mth] [float] NULL,
    [EmpNo] [float] NULL,
    [Product] [nvarchar](255) NULL,
    [Ratio] [float] NULL,
    [F5] [nvarchar](255) NULL,
    [Result] [nvarchar](255) NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 10111, N'GUA', 20, N'value * ratio')
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 10111, N'PEA', 40, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 10111, N'ORA', 40, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 69781, N'GUA', 40, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 69781, N'PEA', 0, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 69781, N'ORA', 60, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 23845, N'GUA', 0, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 23845, N'PEA', 50, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 23845, N'ORA', 50, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 698411, N'GUA', 40, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 698411, N'PEA', 60, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 698411, N'ORA', 0, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 554210, N'GUA', 0, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 554210, N'PEA', 0, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 554210, N'ORA', 100, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 574913, N'GUA', 0, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 574913, N'PEA', 100, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 574913, N'ORA', 0, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 657812, N'GUA', 100, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 657812, N'PEA', 0, NULL, NULL)
    INSERT [dbo].[table1] ([Mth], [EmpNo], [Product], [Ratio],[Result]) VALUES (201908, 657812, N'ORA', 0, NULL, NULL)


    Monday, September 16, 2019 6:04 AM
  • Hi

    Running your query I got 

    There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

    See below my solution helps you


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 16, 2019 6:25 AM
    Answerer

  • So, if GUA, is 0, then I need to adjust ORA to 50, and PEA to 50, to get 100%

    if PEA is 0, then I need to adjust ORA to 60 and GUA to 40, to get 100%

    if ORA and PEA are both 0, then I need to adjust GUA to 100%

    Hi Ron,

    Is this your original table? If yes, what is your desired output as I see all the rows matches with your requirement. 

    Please tell us what is your desire output so that we are able to help. Waiting for your reply.

    Sabrina 



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 16, 2019 6:26 AM
  • Hi, 

    The table given is what I desire

    Monday, September 16, 2019 6:35 AM
  • What about your original table Ron? Could you please provide us with your original table ? 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 16, 2019 6:38 AM
  • Hi,

    I have tried to explain differently

    Script for table is 


    CREATE TABLE [dbo].[tttt](
    [EmpNo] [float] NULL,
    [Month] [float] NULL,
    [Value] [float] NULL,
    [Ratio] [float] NULL,
    [Product] [nvarchar](255) NULL,
    [Total] [float] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (566, 201908, 0.604535398230088, 2029, N'GUA', 0.58811937153823712)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (566, 201908, 0.520541324311261, 6207, N'ORA', 0.56731455334575664)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (566, 201908, 0.647489405419288, 7787, N'PEA', 0.3337084073996468)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-34, 201908, 0.510526315789473, 380, N'GUA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-34, 201908, 0, 2515, N'ORA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-34, 201908, 0.589720789614085, 5623, N'PEA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-33, 201908, 0.42627960275019, 1309, N'GUA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-33, 201908, 0, 6603, N'ORA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-33, 201908, 0.604535398230088, 12656, N'PEA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-32, 201908, 0.480732177263969, 1038, N'GUA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-32, 201908, 0.489274106175514, 4615, N'ORA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-32, 201908, 0.579157516470353, 10018, N'PEA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-31, 201908, 0.538853885388538, 3333, N'ORA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-31, 201908, 0, 585, N'GUA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-31, 201908, 0.649993294890706, 7457, N'PEA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-30, 201908, 0.63296488946684, 3076, N'ORA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-30, 201908, 0.654368932038834, 515, N'GUA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-30, 201908, 0.732786631595234, 6463, N'PEA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-29, 201908, 0.551873198847262, 694, N'GUA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-29, 201908, 0.60219957081545, 3728, N'ORA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-29, 201908, 0.659031682896721, 7291, N'PEA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-28, 201908, 0, 1274, N'GUA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-28, 201908, 0.56548418024928, 5215, N'ORA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-28, 201908, 0.665600730927364, 6567, N'PEA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-26, 201908, 0.530063729564976, 3609, N'ORA', NULL)
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Ratio], [Product], [Total]) VALUES (-26, 201908, 0.571220930232558, 688, N'GUA', NULL)

     So if one of the 3 products for each employee is a null, then the remaining 2 are marked up, if ORA is a 0, then PEA will be 60 and GUA will be 40, and so on (to always make a 100)

    Possible ratios are given below

    Product Ratio1 Ratio2 Ratio3 Ratio4 Ratio5 Ratio6 Ratio7
    GUA 0.2 0 0.4 0.4 0 0 1
    PEA 0.4 0.5 0 0.6 0 1 0
    ORA 0.4 0.5 0.6 0 1 0 0

    Many thanks


    • Edited by RonTech1 Monday, September 16, 2019 8:10 AM
    Monday, September 16, 2019 8:06 AM
  • Can you explain the logic  you got  0.2,0.4,0.4 for RATIO1?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 16, 2019 8:46 AM
    Answerer
  • Hi, 

    The 0.2,0.4,0.4 is the default ratio. 

    Thus if there is a value for each product, then use the default ratio, but if 1 or 2 products are zero, then the remaining products need to be multiplied by a 'ratio'

    Many thanks,

    Monday, September 16, 2019 8:59 AM
  • Which column should be updated in tttt table? Ratio, Total?

    Show an example of calculation (the expression) for some rows. If 0.588119371538237 is a sample result, how was it calculated based on other numbers?


    • Edited by Viorel_MVP Monday, September 16, 2019 9:31 AM
    Monday, September 16, 2019 9:31 AM
  • Hi, 

    It was calculated as per the below:

    Because GUA, PEA and ORA all have values in the 'Value' column, then the default ratio is used (2:4:4)

    Hence the result, for that employee is simply

    (0.604535 * 0.2 + 0.520541 * 0.4  + 0.647489 * 0.4) to give a total of 0.588119371538237

    Many thnaks

    Monday, September 16, 2019 9:42 AM
  • UPDATE [tttt] SET total=CASE WHEN  (Product='ORA' OR  Product='PEA'  OR  Product='GUA') AND 
     Total IS NOT NULL THEN CASE 
       WHEN Product='GUA' THEN 0.2
       WHEN Product='ORA' THEN 0.4
       WHEN Product='PEA' THEN 0.4 ELSE 0 END END

    UPDATE [tttt] SET total=CASE WHEN (Product='PEA' AND Total IS NULL) OR
      (Product='ORA' OR  Product='GUA')    THEN
         CASE WHEN Product='ORA' THEN 0.6 
          WHEN Product='GUA' THEN 0.4 
    ELSE 0 END END
    WHERE Total IS NULL AND (Product='ORA' OR Product='GUA') 

    ----But  here the logic doers  not work , because there are already data in Product GUA ... Ca you elaborate on it?
    UPDATE [tttt] SET total=   CASE WHEN Product='GUA' THEN 1.0 ELSE 0  END   
    WHERE ((Product='ORA' OR Product='PEA')  AND Total IS NULL)  ---or Product='GUA'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 16, 2019 9:50 AM
    Answerer
  • If Excel picture represents the expected results, then check this preliminary query:

    ;
    with Q as
    (
        select EmpNo, ISNULL([GUA], 0) as GUA, ISNULL([PEA], 0) AS PEA, ISNULL([ORA], 0) AS ORA
        from 
        (
            select EmpNo, Product, [Value]
            from tttt
        ) as t
        pivot
        (
            MAX([Value])
            for Product in ([GUA], [PEA], [ORA] ) 
        ) as p
    ),
    GUAonly as 
    (
        select EmpNo, 1.0 as GUA, 0.0 as PEA, 0.0 as ORA
        from Q
        where GUA <> 0 and PEA = 0 and ORA = 0
    ),
    PEAonly as 
    (
        select EmpNo, 0 as GUA, 1 as PEA, 0 as ORA
        from Q
        where GUA = 0 and PEA <> 0 and ORA = 0
    ),
    ORAonly as 
    (
        select EmpNo, 0 as GUA, 0 as PEA, 1 as ORA
        from Q
        where GUA = 0 and PEA = 0 and ORA <> 0
    ),
    noGUA as
    (
        select EmpNo, 0 as GUA, 0.5 as PEA, 0.5 as ORA
        from Q
        where GUA = 0 and PEA <> 0 and ORA <> 0
    ),
    noPEA as
    (
        select EmpNo, 0.4 as GUA, 0 as PEA, 0.6 as ORA
        from Q
        where GUA <> 0 and PEA = 0 and ORA <> 0
    ),
    noORA as
    (
        select EmpNo, 0.4 as GUA, 0.6 as PEA, 0 as ORA
        from Q
        where GUA <> 0 and PEA <> 0 and ORA = 0
    ),
    [All] as
    (
        select EmpNo, 0.2 as GUA, 0.4 as PEA, 0.4 as ORA
        from Q
        where GUA <> 0 and PEA <> 0 and ORA <> 0
    ),
    U as 
    (
        select * from GUAonly
        union all
        select * from PEAonly
        union all
        select * from ORAonly
        union all
        select * from noGUA
        union all
        select * from noPEA
        union all
        select * from noORA
        union all
        select * from [All]
    ),
    T as 
    (
        select U.EmpNo, U.GUA * Q.GUA + U.PEA * Q.PEA + U.ORA * Q.ORA as Total
        from U
        inner join Q on Q.EmpNo = U.EmpNo
    )
    update tttt
    set Total = T.Total
    from T
    where T.EmpNo = tttt.EmpNo

     

    But if your INSERT statements represent the expected results, then it is not clear how 0.56731455334575664 and 0.3337084073996468 were calculated.

     


    • Edited by Viorel_MVP Tuesday, September 17, 2019 4:24 AM Spelling.
    • Marked as answer by RonTech1 Tuesday, September 17, 2019 7:40 AM
    Monday, September 16, 2019 10:41 AM
  • Hi Apologies,

    Please see below  screenshot, ignore or remove the Ratio column

    Monday, September 16, 2019 10:51 AM
  • Hi Ron,

    Is this what you want? 

    CREATE TABLE [dbo].[tttt](
    [EmpNo] [float] NULL,
    [Month] [float] NULL,
    [Value] [float] NULL,
    [Product] [nvarchar](255) NULL,
    [Total] [float] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[tttt] ([EmpNo], [Month], [Value], [Product], [Total]) VALUES
    (566, 201908, 0.604535398230088,  N'GUA', 0.58811937153823712),
    (566, 201908, 0.520541324311261,  N'ORA', 0.56731455334575664),
    (566, 201908, 0.647489405419288,  N'PEA', 0.3337084073996468),
    (-34, 201908, 0.510526315789473, N'GUA', NULL),
    (-34, 201908, 0,  N'ORA', NULL),
    (-34, 201908, 0.589720789614085,  N'PEA', NULL),
    (-33, 201908, 0.42627960275019, N'GUA', NULL),
    (-33, 201908, 0,  N'ORA', NULL),
    (-33, 201908, 0.604535398230088,  N'PEA', NULL),
    (-32, 201908, 0.480732177263969, N'GUA', NULL),
    (-32, 201908, 0.489274106175514, N'ORA', NULL),
    (-32, 201908, 0.579157516470353,  N'PEA', NULL),
    (-31, 201908, 0.538853885388538, N'ORA', NULL),
    (-31, 201908, 0,N'GUA', NULL),
    (-31, 201908, 0.649993294890706,  N'PEA', NULL),
    (-30, 201908, 0.63296488946684, N'ORA', NULL),
    (-30, 201908, 0.654368932038834, N'GUA', NULL),
    (-30, 201908, 0.732786631595234,  N'PEA', NULL),
    (-29, 201908, 0.551873198847262, N'GUA', NULL),
    (-29, 201908, 0.60219957081545, N'ORA', NULL),
    (-29, 201908, 0.659031682896721,  N'PEA', NULL),
    (-28, 201908, 0,  N'GUA', NULL),
    (-28, 201908, 0.56548418024928, N'ORA', NULL),
    (-28, 201908, 0.665600730927364, N'PEA', NULL),
    (-26, 201908, 0.530063729564976, N'ORA', NULL),
    (-26, 201908, 0.571220930232558,N'GUA', NULL)
    
    ;with cte1 as (
    select * from (
    select EmpNo,Month,[Value],Product from 
    tttt)s
    pivot 
    (sum(value)for Product in ([GUA],[PEA],[ORA]))PVT
    ),
    cte2 as (
    select empno,month,GUA,CASE 
    when GUA<>0 AND PEA<>0 AND ORA <>0 THEN 0.2
    when GUA=0 AND PEA<>0 AND ORA <>0 THEN 0 
    when GUA<>0 AND PEA=0 AND ORA <>0 THEN 0.4
    when GUA<>0 AND PEA<>0 AND ORA =0 THEN 0.4
    when GUA=0 AND PEA=0 AND ORA <>0 THEN 0
    when GUA=0 AND PEA<>0 AND ORA =0 THEN 0
    when GUA<>0 AND PEA=0 AND ORA =0 THEN 1
    else null
    end as ratiogua,
    PEA,CASE 
    when GUA<>0 AND PEA<>0 AND ORA <>0 THEN 0.4
    when GUA=0 AND PEA<>0 AND ORA <>0 THEN 0.5
    when GUA<>0 AND PEA=0 AND ORA <>0 THEN 0
    when GUA<>0 AND PEA<>0 AND ORA =0 THEN 0.6
    when GUA=0 AND PEA=0 AND ORA <>0 THEN 0
    when GUA=0 AND PEA<>0 AND ORA =0 THEN 1
    when GUA<>0 AND PEA=0 AND ORA =0 THEN 0
    else null
    end as ratiopea,
    ORA,CASE 
    when GUA<>0 AND PEA<>0 AND ORA <>0 THEN 0.4
    when GUA=0 AND PEA<>0 AND ORA <>0 THEN 0.5
    when GUA<>0 AND PEA=0 AND ORA <>0 THEN 0.6
    when GUA<>0 AND PEA<>0 AND ORA =0 THEN 0
    when GUA=0 AND PEA=0 AND ORA <>0 THEN 1
    when GUA=0 AND PEA<>0 AND ORA =0 THEN 0
    when GUA<>0 AND PEA=0 AND ORA =0 THEN 0
    else null
    end as ratioora
    from cte1)
    
    select *, GUA*ratiogua+ pea* ratiopea+ ORA*ratioora as total
    from cte2
    
    /*
    empno  month    GUA                    ratiogua  PEA                    ratiopea   ORA                    ratioora   total
    ------ -------- ---------------------- --------- ---------------------- ---------- ---------------------- ---------- ----------------------
    -34    201908   0.510526315789473      0.4       0.589720789614085      0.6        0                      0.0        0.55804300008424
    -33    201908   0.42627960275019       0.4       0.604535398230088      0.6        0                      0.0        0.533233080038129
    -32    201908   0.480732177263969      0.2       0.579157516470353      0.4        0.489274106175514      0.4        0.523519084511141
    -31    201908   0                      0.0       0.649993294890706      0.5        0.538853885388538      0.5        0.594423590139622
    -30    201908   0.654368932038834      0.2       0.732786631595234      0.4        0.63296488946684       0.4        0.677174394832596
    -29    201908   0.551873198847262      0.2       0.659031682896721      0.4        0.60219957081545       0.4        0.614867141254321
    -28    201908   0                      0.0       0.665600730927364      0.5        0.56548418024928       0.5        0.615542455588322
    -26    201908   0.571220930232558      NULL      NULL                   NULL       0.530063729564976      NULL       NULL
    566    201908   0.604535398230088      0.2       0.647489405419288      0.4        0.520541324311261      0.4        0.588119371538237
    */

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, September 17, 2019 2:07 AM
  • Hi Sabrina,

    This solution works perfectly for my scenario.

    Thanks so much!!

    Regards

    Tuesday, September 17, 2019 7:39 AM
  • Hi Viorel_,

    This solution works perfectly for my scenario.

    Thanks so much!!

    Regards

    Tuesday, September 17, 2019 7:40 AM