March 9, 2013 at 9:39 pm
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
March 10, 2013 at 12:43 pm
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.
March 10, 2013 at 5:33 pm
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.
March 10, 2013 at 5:40 pm
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.
March 10, 2013 at 5:43 pm
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?
March 10, 2013 at 5:52 pm
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.
September 9, 2013 at 4:07 am
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