Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Assign values: conditional case when Expand / Collapse
Author
Message
Posted Saturday, March 9, 2013 9:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 7:35 AM
Points: 27, Visits: 116
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

Post #1428955
Posted Sunday, March 10, 2013 12:43 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 23,044, Visits: 31,568
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 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)
Post #1429003
Posted Sunday, March 10, 2013 5:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 7:35 AM
Points: 27, Visits: 116
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.
Post #1429034
Posted Sunday, March 10, 2013 5:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 23,044, Visits: 31,568
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 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)
Post #1429037
Posted Sunday, March 10, 2013 5:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 7:35 AM
Points: 27, Visits: 116
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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 23,044, Visits: 31,568
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.



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)
Post #1429039
Posted Monday, September 9, 2013 4:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 4:01 AM
Points: 1, Visits: 80
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse