Group: General Forum Members
Points: 76
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',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

Group: General Forum Members
Points: 76
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 Total 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:
name1 name2 total sum>100&Total<25 sum<100&Total<25 sum>100&minTotal<25 sum>100&minTotal<25 X7 Y15 6 1 0 1 0 X7 Y16 9 1 0 0 0 X7 Y17 100 0 0 0 0
and for X4 records: name1 name2 total sum>100&Total<25 sum<100&Total<25 sum>100&minTotal<25 sum>100&minTotal<25 X4 Y4 29 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.

Group: General Forum Members
Points: 38905
Visits: 38508

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 Total 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:
name1 name2 total sum>100&Total<25 sum<100&Total<25 sum>100&minTotal<25 sum>100&minTotal<25 X7 Y15 6 1 0 1 0 X7 Y16 9 1 0 0 0 X7 Y17 100 0 0 0 0
and for X4 records: name1 name2 total sum>100&Total<25 sum<100&Total<25 sum>100&minTotal<25 sum>100&minTotal<25 X4 Y4 29 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.
Lynn Pettis For better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction Logs SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Group: General Forum Members
Points: 76
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?

Group: General Forum Members
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
