Assign values: conditional case when

  • My data are arranged like:

    IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1

    --===== Create the test table with

    CREATE TABLE #Table1

    (

    Name1 Char(5),

    Name2 Char(5),

    Total int

    )

    INSERT INTO #Table1 (Name1, Name2,Total)

    SELECT 'X1','Y1',8

    UNION ALL

    SELECT 'X2','Y2',38

    UNION ALL

    SELECT 'X3','Y3',2

    UNION ALL

    SELECT 'X4','Y4',29

    UNION ALL

    SELECT 'X4','Y5',18

    UNION ALL

    SELECT 'X4','Y6',7

    UNION ALL

    SELECT 'X4','Y7',10

    UNION ALL

    SELECT 'X5','Y8',4

    UNION ALL

    SELECT 'X5','Y9',80

    UNION ALL

    SELECT 'X5','Y10',32

    UNION ALL

    SELECT 'X5','Y11',93

    UNION ALL

    SELECT 'X6','Y12',54

    UNION ALL

    SELECT 'X6','Y13',22

    UNION ALL

    SELECT 'X6','Y14',68

    UNION ALL

    SELECT 'X7','Y15',6

    UNION ALL

    SELECT 'X7','Y16',9

    UNION ALL

    SELECT 'X7','Y17',100

    UNION ALL

    SELECT 'X8','Y18',3

    Select * from #Table1

    I am trying to assing values (0 or 1) in columns D thru G based on the following logic. THe first wo case when conditions work. The last two are not working and assigning wrong values. The desired outcome for example for Name1=X7 is:

    Name1 Name2 Total Name1>100&Name2<25 Name1<100&Name2<25 Max of Name1>100&Name2<25 Max of Name1<100&Name2<25

    X7 Y15 6 1 0 1 0

    X7 Y16 9 1 0 0 0

    X7 Y17 100 0 0 0 0

    select name1, name2,total,

    case when (sum(total) over (partition by name1) >100) and total <25 Then 1

    else 0

    end as [Name1>100&Name2<25], --Works

    case when (sum(total) over (partition by name1) <100) and total <25 Then 1

    else 0

    end as [Name1<100&Name2<25], --Works

    case when (sum(total) over (partition by name1) > 100) and (min(total) over (partition by name1) <25) Then 1

    else 0

    end as [Max of Name1>100&Name2<25], ---not working

    case when (sum(total) over (partition by name1) <100) and (min(total)over (partition by name1) <25) Then 1

    else 0

    end as [Max of Name1<100&Name2<25] --not working

    from #Table1

    group by name1,total,name2

    order by name1, name2, total

    Any suggesitons?

    Thank you for your help,

    Helal

  • Actually, your code is working just the way it is supposed to based on how it is written.

    Would you please take the time to explain what it is you are trying to accomplish, because what it appears to be is a logic problem. You aren't asking the right question in the code.

  • Lynn,

    you were right, I should have provided descriptons as what I am tryin gto do. Here is the when running the query:

    Name1Name2Total

    X1 Y1 8

    X2 Y2 38

    X3 Y3 2

    X4 Y4 29

    X4 Y5 18

    X4 Y6 7

    X4 Y7 10

    X5 Y8 4

    X5 Y9 80

    X5 Y10 32

    X5 Y11 93

    X6 Y12 54

    X6 Y13 22

    X6 Y14 68

    X7 Y15 6

    X7 Y16 9

    X7 Y17 100

    X8 Y18 3

    FIrst I need to populate Column D with 0 or 1 if:

    sum of Total (column C) over Name1 > 100 and Total for name2 < 25 then =1 otherwise = 0

    I accomplished that by:

    case when (sum(total) over (partition by name1) >100) and total <25 Then 1

    else 0

    end as [Name1>100&Name2<25],

    Second, I need to same thing as above except that the sum is < 100. I accomplished that by:

    case when (sum(total) over (partition by name1) <100) and total <25 Then 1

    else 0

    end as [Name1<100&Name2<25],

    Third, I need to assign (1 or 0) is:

    sum of Total over Name1 > 100 and min of Total < 25 then 1 otherwise 0. What I need here, take records for X7:

    X7 Y15 6

    X7 Y16 9

    X7 Y17 100

    The sum of Total is > 100 but I need to assign value of 1 only for first record (X7 Y15 6) and 0 for the rest of record. THe query I posted for this doesn't work and assign value of 1 for all the records despite I had min.

    and the last scenario, is just the opposite of the latter with sum < 100. Thus desired outcome for X7 for all scenarios will be:

    name1name2totalsum>100&Total<25 sum<100&Total<25 sum>100&minTotal<25 sum>100&minTotal<25

    X7Y156 1 0 1 0

    X7 Y16 9 1 0 0 0

    X7 Y17 100 0 0 0 0

    and for X4 records:

    name1name2totalsum>100&Total<25 sum<100&Total<25 sum>100&minTotal<25 sum>100&minTotal<25

    X4Y429 0 0 0 0

    X4 Y5 18 0 1 0 0

    X4 Y6 7 0 1 0 1

    X4 Y7 10 0 1 0 0

    again, the case when functions I posted are working correctly to populate Columns D&E but not for F&G. I hope this helps.

  • helal.mobasher 13209 (3/10/2013)


    Lynn,

    you were right, I should have provided descriptons as what I am tryin gto do. Here is the when running the query:

    Name1Name2Total

    X1 Y1 8

    X2 Y2 38

    X3 Y3 2

    X4 Y4 29

    X4 Y5 18

    X4 Y6 7

    X4 Y7 10

    X5 Y8 4

    X5 Y9 80

    X5 Y10 32

    X5 Y11 93

    X6 Y12 54

    X6 Y13 22

    X6 Y14 68

    X7 Y15 6

    X7 Y16 9

    X7 Y17 100

    X8 Y18 3

    FIrst I need to populate Column D with 0 or 1 if:

    sum of Total (column C) over Name1 > 100 and Total for name2 < 25 then =1 otherwise = 0

    I accomplished that by:

    case when (sum(total) over (partition by name1) >100) and total <25 Then 1

    else 0

    end as [Name1>100&Name2<25],

    Second, I need to same thing as above except that the sum is < 100. I accomplished that by:

    case when (sum(total) over (partition by name1) <100) and total <25 Then 1

    else 0

    end as [Name1<100&Name2<25],

    Third, I need to assign (1 or 0) is:

    sum of Total over Name1 > 100 and min of Total < 25 then 1 otherwise 0. What I need here, take records for X7:

    X7 Y15 6

    X7 Y16 9

    X7 Y17 100

    The sum of Total is > 100 but I need to assign value of 1 only for first record (X7 Y15 6) and 0 for the rest of record. THe query I posted for this doesn't work and assign value of 1 for all the records despite I had min.

    and the last scenario, is just the opposite of the latter with sum < 100. Thus desired outcome for X7 for all scenarios will be:

    name1name2totalsum>100&Total<25 sum<100&Total<25 sum>100&minTotal<25 sum>100&minTotal<25

    X7Y156 1 0 1 0

    X7 Y16 9 1 0 0 0

    X7 Y17 100 0 0 0 0

    and for X4 records:

    name1name2totalsum>100&Total<25 sum<100&Total<25 sum>100&minTotal<25 sum>100&minTotal<25

    X4Y429 0 0 0 0

    X4 Y5 18 0 1 0 0

    X4 Y6 7 0 1 0 1

    X4 Y7 10 0 1 0 0

    again, the case when functions I posted are working correctly to populate Columns D&E but not for F&G. I hope this helps.

    The case statements may not being working the way you want them to but the code is working the it is supposed to, that is why it is a logic error. We need to determine what code you actually need to meet the requirements you provided.

  • I am not sure what you mean by logic error! I think the code I am suing is not suitable for what I need...is that what yo urefer to logic error?

  • If the code isn't suitable, then it is a logic error. In this case what you think it should be doing, it isn't. We need to figure out what the code actually needs to be to answer the question you are asking, i.e. to meet your requirements.

  • Hi GrassHopper,

    Try this one for third scenario. I believe this will work for you..

    case when (sum(total) over (partition by name1) >100) AND (MIN(total) over (partition by name1) < 25) AND MIN(total) over (partition by name1) = TOTAL Then 1 else 0 end as ForCase3

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply