case issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

case issue

Author

Topic

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-11-07 : 13:53:46

hey how would i say
CASE WHEN oepordnum MATCH THEN SUM( sell - returnsell) ELSE sell END AS newsell

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 13:59:26

case when oepordnum = <some column>|<some value> THEN sum(sell - returnsell) else sell end as newsell

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-11-07 : 14:06:11

do i place somethign in where the some column is and the some value?
I tried to just place that line in you wrote and it did not work.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 14:14:46

show me your query...

case when xxxxxx = <SOME COLUMN>

or if you want to check some value

case when xxxxxx= 'somevalue'

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-11-07 : 14:36:10

i want it to be case when order number = order number then sum sell - return sell

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 14:41:19

Please post your SQL

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-11-07 : 14:48:47

I have created 3 views to refine my data and now am trying to get this to work. The view that I am using to create the grouping is this.

SELECT oepordnum, kbranch, kmfg, sell, cost, date1, NULL AS returnsell
FROM dbo.counterpartsgp
UNION
SELECT oepordnum, NULL AS kbranch, NULL AS kmfg, NULL AS sell, NULL AS cost, NULL AS date1, returnsell
FROM dbo.partsreturngp

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-11-07 : 14:51:50

What you are seeing is I have one table that shows all that info the first select line. The next table only shows me order numbers and returnsell price. Hence why I have those Null's for the UNION. The problem I am trying to get past is i have all the data returning to me great but I need them to take any order # that matches to sum up the returnsell value. If order number does not have a matching return that just post the sell price.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 14:53:05

and what are you trying to do with column OEPORDNUM? You mentioned when OEPORDNUM MATCH.. please Define Match.

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-11-07 : 14:56:47

meaning if 1000000 is the order number from 1 table and from the other table its 1000000 then sum each of their respective rows togeather

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-11-07 : 14:59:45

example
(Table 1)
order #, sell, cost
1234, 12, 8
1234, 34,23

Table 2
Order # returnsell, NULL AS sell, NULL as cost
1234, 12, NULL, NULL

I want it to take even though they are from different rows i want them to match up and then say 12-12 0

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 15:15:02

Join them:


select a.orderno, 
isnull(a.sell,0)- isnull(b.returnsell,0) as newsell, a.cost
from <tab1> a left join <tab2> b on a.orderno = b.orderno

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-11-07 : 15:37:24

the only problem doing it this way unless you know how to fix it is some order numbers are on my list 2 to 3 times so it puts the value down for each one. If you know a way to just do it to one order # and not everyone of the same. That would fix my issue.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 15:45:38

I have trouble understanding your question. ARE You saying Table 1 above should only apply to the 1st order number found???
Please show some sample Results...

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-11-07 : 15:51:42

oepordnum/newsell/cost/returnsell/sell/kmfg/kbranch/date1/oeqtyship
1024158 -38.22000400000001 21.44 -71.200004 32.98 NA 01 6/26/2008 12:00:00 AM 2
1024158 -59.140004000000005 7.84 -71.200004 12.06 NA 01 6/26/2008 12:00:00 AM 2
1024158 -60.380004000000007 7.04 -71.200004 10.82 NA 01 6/26/2008 12:00:00 AM 2
1024158 -55.860004 9.98 -71.200004 15.34 NA 01 6/26/2008 12:00:00 AM 2
1024160 12.7 7.7 NULL 12.7 LB 01 6/26/2008 12:00:00 AM 2
1024160 4.72 2.86 NULL 4.72 LB 01 6/26/2008 12:00:00 AM 1
1024160 11.22 6.81 NULL 11.22 LB 01 6/26/2008 12:00:00 AM 3
1024167 237.65 142.59 NULL 237.65 PH 01 6/26/2008 12:00:00 AM 1
1024169 39.28 29.48 NULL 39.28 GV 01 6/30/2008 12:00:00 AM 4
1024174 149.94 85.5 NULL 149.94 BC 01 6/27/2008 12:00:00 AM 1
1024183 857.59 519.75 NULL 857.59 LB 01 6/27/2008 12:00:00 AM 1
1024185 104.7 68.06 NULL 104.7 GV 01 6/27/2008 12:00:00 AM 1
1024192 19.709999 19.71 NULL 19.709999 TR 01 6/27/2008 12:00:00 AM 1
1024192 54.1944 54.18 NULL 54.1944 TR 01 6/27/2008 12:00:00 AM 3
1024194 281.54 281.54 NULL 281.54 LB 01 7/9/2008 12:00:00 AM 2
1024194 280.91 280.91 NULL 280.91 LB 01 7/9/2008 12:00:00 AM 1
1024199 208 130.7 NULL 208 SI 01 6/27/2008 12:00:00 AM 2
1023486 8.09 4.9 NULL 8.09 LB 01 6/17/2008 12:00:00 AM 1
1023486 37.88 22.96 NULL 37.88 LB 01 6/17/2008 12:00:00 AM 1
1023486 51.51 31.22 NULL 51.51 LB 01 6/16/2008 12:00:00 AM 1
1023486 47.36 28.7 NULL 47.36 LB 01 6/17/2008 12:00:00 AM 1
1023486 49.88 30.24 NULL 49.88 LB 01 6/16/2008 12:00:00 AM 4
1023487 233.76 163.7 NULL 233.76 KP 01 6/16/2008 12:00:00 AM 1
1023487 1242.53 869.78 NULL 1242.53 KP 01 6/16/2008 12:00:00 AM 1
1023488 44.51 31.16 NULL 44.51 JC 01 6/20/2008 12:00:00 AM 1
1023504 30.28 21.2 NULL 30.28 PC 01 6/24/2008 12:00:00 AM 1
1023521 340.02 206.08 NULL 340.02 LB 01 6/18/2008 12:00:00 AM 2
1023521 287.24 174.09 NULL 287.24 LB 01 6/18/2008 12:00:00 AM 1

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-11-07 : 15:52:01

using this code
SELECT counterpartsgp.oepordnum, ISNULL(counterpartsgp.sell, 0) + ISNULL(partsreturngp.returnsell, 0) AS newsell, counterpartsgp.cost,
partsreturngp.returnsell, counterpartsgp.sell, counterpartsgp.kmfg, counterpartsgp.kbranch, counterpartsgp.date1, counterpartsgp.oeqtyship
FROM counterpartsgp LEFT OUTER JOIN
partsreturngp ON counterpartsgp.oepordnum = partsreturngp.oepordnum

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 15:59:17

let me help you to help me understand:

In your example:

(Table 1)
order #, sell, cost
1234, 12, 8
1234, 34,23

Table 2
Order # returnsell, NULL AS sell, NULL as cost
1234, 12, NULL, NULL

What is the EXPECTED output?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources