Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Assign values: conditional case when Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, March 9, 2013 9:39 PM
 Valued Member Group: General Forum Members Last Login: Wednesday, October 28, 2015 11:53 AM Points: 58, Visits: 219
 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',8UNION ALLSELECT 'X2','Y2',38UNION ALLSELECT 'X3','Y3',2UNION ALLSELECT 'X4','Y4',29UNION ALL SELECT 'X4','Y5',18UNION ALL SELECT 'X4','Y6',7UNION ALL SELECT 'X4','Y7',10UNION ALL SELECT 'X5','Y8',4UNION ALL SELECT 'X5','Y9',80UNION ALL SELECT 'X5','Y10',32UNION ALL SELECT 'X5','Y11',93UNION ALL SELECT 'X6','Y12',54UNION ALL SELECT 'X6','Y13',22UNION ALL SELECT 'X6','Y14',68UNION ALL SELECT 'X7','Y15',6UNION ALL SELECT 'X7','Y16',9UNION ALL SELECT 'X7','Y17',100UNION ALL SELECT 'X8','Y18',3Select * 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 1else 0end as [Name1>100&Name2<25], --Workscase when (sum(total) over (partition by name1) <100) and total <25 Then 1else 0end as [Name1<100&Name2<25], --Workscase when (sum(total) over (partition by name1) > 100) and (min(total) over (partition by name1) <25) Then 1else 0end as [Max of Name1>100&Name2<25], ---not workingcase when (sum(total) over (partition by name1) <100) and (min(total)over (partition by name1) <25) Then 1else 0end as [Max of Name1<100&Name2<25] --not workingfrom #Table1group by name1,total,name2order by name1, name2, total Any suggesitons? Thank you for your help, Helal
Post #1428955
 Posted Sunday, March 10, 2013 12:43 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 9:26 PM Points: 23,522, Visits: 37,753
 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.
Post #1429003
 Posted Sunday, March 10, 2013 5:33 PM
 Valued Member Group: General Forum Members Last Login: Wednesday, October 28, 2015 11:53 AM Points: 58, Visits: 219
 Lynn,you were right, I should have provided descriptons as what I am tryin gto do. Here is the when running the query:Name1 Name2 TotalX1 Y1 8X2 Y2 38X3 Y3 2X4 Y4 29X4 Y5 18X4 Y6 7X4 Y7 10X5 Y8 4X5 Y9 80X5 Y10 32X5 Y11 93X6 Y12 54X6 Y13 22X6 Y14 68X7 Y15 6X7 Y16 9X7 Y17 100X8 Y18 3FIrst 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 = 0I accomplished that by:case when (sum(total) over (partition by name1) >100) and total <25 Then 1else 0end 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 1else 0end 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 6X7 Y16 9X7 Y17 100The 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:name1 name2 total sum>100&Total<25 sum<100&Total<25 sum>100&minTotal<25 sum>100&minTotal<25X7 Y15 6 1 0 1 0X7 Y16 9 1 0 0 0X7 Y17 100 0 0 0 0and for X4 records:name1 name2 total sum>100&Total<25 sum<100&Total<25 sum>100&minTotal<25 sum>100&minTotal<25X4 Y4 29 0 0 0 0X4 Y5 18 0 1 0 0X4 Y6 7 0 1 0 1X4 Y7 10 0 1 0 0again, the case when functions I posted are working correctly to populate Columns D&E but not for F&G. I hope this helps.
Post #1429034
 Posted Sunday, March 10, 2013 5:40 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 9:26 PM Points: 23,522, Visits: 37,753
 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:Name1 Name2 TotalX1 Y1 8X2 Y2 38X3 Y3 2X4 Y4 29X4 Y5 18X4 Y6 7X4 Y7 10X5 Y8 4X5 Y9 80X5 Y10 32X5 Y11 93X6 Y12 54X6 Y13 22X6 Y14 68X7 Y15 6X7 Y16 9X7 Y17 100X8 Y18 3FIrst 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 = 0I accomplished that by:case when (sum(total) over (partition by name1) >100) and total <25 Then 1else 0end 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 1else 0end 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 6X7 Y16 9X7 Y17 100The 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:name1 name2 total sum>100&Total<25 sum<100&Total<25 sum>100&minTotal<25 sum>100&minTotal<25X7 Y15 6 1 0 1 0X7 Y16 9 1 0 0 0X7 Y17 100 0 0 0 0and for X4 records:name1 name2 total sum>100&Total<25 sum<100&Total<25 sum>100&minTotal<25 sum>100&minTotal<25X4 Y4 29 0 0 0 0X4 Y5 18 0 1 0 0X4 Y6 7 0 1 0 1X4 Y7 10 0 1 0 0again, 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.
Post #1429037
 Posted Sunday, March 10, 2013 5:43 PM
 Valued Member Group: General Forum Members Last Login: Wednesday, October 28, 2015 11:53 AM Points: 58, Visits: 219
 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?
Post #1429038
 Posted Sunday, March 10, 2013 5:52 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 9:26 PM Points: 23,522, Visits: 37,753
 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.
Post #1429039
 Posted Monday, September 9, 2013 4:07 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, September 12, 2016 6:21 AM Points: 1, Visits: 97
 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
Post #1492719

 Permissions