SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Assign values: conditional case when


Assign values: conditional case when

Author
Message
helal.mobasher 13209
helal.mobasher 13209
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38905 Visits: 38508
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
helal.mobasher 13209
helal.mobasher 13209
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

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.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
helal.mobasher 13209
helal.mobasher 13209
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

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?
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38905 Visits: 38508
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Gopal R
Gopal R
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search