February 9, 2009 at 11:16 am
I have a table structure and sample data as follows:
MemberNbr DollarAmt DollarType Code
123 0.00 1 00
123 0.00 2 00
123 12.00 3 00
124 12.00 1 22
124 0 2 22
124 112.00 3 21
I need to update dollar type based on the following condition
if either dollar type 1 or 2 > 0 then dollar type 1 AND 2 = 22 and dollar type 3 = 21.
If dollar type 1 or 2 = 0 then dollar type = 3
Can anyone assist with SQL for this update? There are 9 million rows in this table.
February 9, 2009 at 11:21 am
From your example, I don't see how dollar type 1 can equal 0. It looks like dollar type (a column) can be 1, 2, or 3. Please clarify.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 11:32 am
The dollar type does not equal 0. Dollar type can be viewed as a revenue type code. The valid values for dollar type are 1,2, or 3
February 9, 2009 at 11:43 am
Your post includes "if either dollar type 1 or 2 > 0". Under what conditions would this not be true? Or does this mean something other than what it says?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 11:56 am
Dollar Types 1 or 2 or both can have a positive dollaramt or one or the other can have a positivedollar amount or dollartype 1 and 2 can both be 0.
Doolar type 3 will never be 0 dollar amt.
February 9, 2009 at 12:54 pm
The problem we are having is trying to decode your logic. It would help if you could clarify the logic more like the following:
(This is not SQL code, but more like psuedo-code)
IF (DollarAmt > 0 and
(DollarType = 1 or
DollarType = 2)) or
(DollarType = 3)
THEN
Code = '21'
What you have written is confusing, but the above would be easier to figure out what you are trying to accomplish.
Edit: Fix my logic.
February 10, 2009 at 7:36 am
Assuming I interpreted your rules correctly (you could really do with explaining it better), I beleive your sample data is actually in the final state, so I made up some code values to test.
create table #t (
MemberNbr int,
DollarAmt money,
DollarType int,
Code char(2)
)
/* original - seems to be final state
insert #t
select 123, 0.00, 1, '00' union all
select 123, 0.00, 2, '00' union all
select 123, 12.00, 3, '00' union all
select 124, 12.00, 1, '22' union all
select 124, 0, 2, '22' union all
select 124, 112.00, 3, '21'
*/
insert #t
select 123, 0.00, 1, '99' union all
select 123, 0.00, 2, '00' union all
select 123, 12.00, 3, '00' union all
select 124, 12.00, 1, '00' union all
select 124, 0, 2, '00' union all
select 124, 112.00, 3, '00'
; with upd as (
select
a.membernbr,
a.dollaramt as 'dollar1',
case
when a.dollaramt > 0 or b.dollaramt > 0 then '22'
when a.dollaramt = 0 or b.dollaramt = 0 then c.code
else a.code end as 'code1',
b.dollaramt as 'dollar2',
case
when a.dollaramt > 0 or b.dollaramt > 0 then '22'
when a.dollaramt = 0 or b.dollaramt = 0 then c.code
else b.code end as 'code2',
c.dollaramt as 'dollar3',
case
when a.dollaramt > 0 or b.dollaramt > 0 then '21'
else c.code end as 'code3'
from (
select
membernbr,dollaramt,code
from #t
where dollartype = 1
) a
join (
select
membernbr,dollaramt,code
from #t
where dollartype = 2
) b
on a.membernbr=b.membernbr
join (
select
membernbr,dollaramt,code
from #t
where dollartype = 3
) c
on a.membernbr=c.membernbr
)
update #t
set code=x.code
from
#t a
join (
select
membernbr,dollaramt,dollartype,code
from (
select
membernbr,dollar1 as 'dollaramt',1 as 'dollartype',code1 as 'code'
from
upd
) x union all(
select
membernbr,dollar2 as 'dollaramt',2 as 'dollartype',code2 as 'code'
from
upd
) union all (
select
membernbr,dollar3 as 'dollaramt',3 as 'dollartype',code3 as 'code'
from
upd
)
) x
on a.membernbr=x.membernbr and a.dollartype=x.dollartype
where a.code<>x.code
select * from #t
drop table #t
The end result is your original sample. If the rules are not as I've guessed, then please try to explain them better.
I've used:
-- psuedo-code
for each membernbr
if
((dollaramt where dollartype=1) > 0) or
(dollaramt where dollartype=2) > 0))
then
set code='22' where dollartype=1
set code='22' where dollartype=2
set code='21' where dollartype=3
else
if
((dollaramt where dollartype=1) = 0) or
(dollaramt where dollartype=2) = 0))
then
set code=(code where dollartype=3) where dollartype=1
set code=(code where dollartype=3) where dollartype=2
else
do nothing
endif
endif
next membernbr
Edit P.S.: I've assumed that there a 3 values for each MemberNbr. You will need to use outer joins if this isn't the case.
Derek
February 10, 2009 at 2:54 pm
THANK YOU VERY KINDLY FOR THE ASSISTANCE.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply