How to update Flag based on Maximum Amount

Question

Post reply

How to update Flag based on Maximum Amount

avdhut.k

SSCrazy

Points: 2834
More actions
August 8, 2013 at 5:00 am

#278457

Create Table #Temp
(
Lnno Varchar(15),
Custcode varchar(10),
Flag varchar(10),
Amount Int,
Amount_Flag varchar(1)
)
Insert Into #Temp
Values ('1','A1','Cust',1000,''),
('1','A2','CoAp',500,''),
('1','A3','CoAp',100,''),
('1','A4','CoAp',2000,''),
('2','B1','Cust',1000,''),
('2','B2','CoAp',1000,''),
('2','B3','CoAp',1000,''),
('2','B4','CoAp',1000,''),
('3','C1','Cust',0,''),
('3','C2','CoAp',1000,''),
('3','C3','CoAp',1000,''),
('3','C4','CoAp',5000,'')
Select * from #Temp
/*
Hi,
I have this data where it has Lnno,Custcode,Amount and Flag.
My requirement is,
I has to Update Amount_Flag as Y,to maximum Amount for that Lnno.
1)Now in case Lnno = 1,there is one Cust and three CoAp ,So Amount_Flag should be updated as Y to Custcode 'A4',
since it has maximum amount.
2)In Case Lnno = 2,Amount is same for all,so in this case Amount_Flag should be Updated to Flag = 'Cust',
that is the priority should be given to Cust.
3)In Case Lnno = 3,Cust has amount 0,but two CoAp has Amount same that is 1000,so Amount_Flag should be updated to any
one of the record.
Please Help me.
Thanks in Advance!!
*/

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

terry999 SSCertifiable Points: 5036 More actions · Answer 1

Not the best I know..

with tMaxVal AS

(select lnno, max(amount) as most

from #temp

group by lnno)

SELECT t.lnno, count(*) as noitems, tMax.most

INTO #tOccurs

FROM #temp as t

inner join tMaxVal as tMax

on t.lnno = tMax.lnno

and t.amount = tMax.most

group by t.lnno, tMax.most

UPDATE #temp

SET amount_flag='y'

from #tOccurs

WHERE #temp.lnno = #tOccurs.lnno

and #temp.amount = #tOccurs.most

and noitems=1

UPDATE #temp

SET amount_flag='y'

FROM #tOccurs

WHERE #temp.lnno = #tOccurs.lnno

and #temp.amount = #tOccurs.most

and noitems>1

and flag = 'cust'

terry999 SSCertifiable Points: 5036 More actions · Answer 2

Doesn't include when all are the same amount and you do not have type='cust'

You can do another check for this and update one of them with some other criteria..

Stefan_G SSCertifiable Points: 6609 More actions · Answer 3

Would this work for you:

;with cte as (

Select row_number() over (partition by Lnno order by amount desc, flag desc, custcode) as rn,*

from #Temp

)

update cte

set Amount_Flag = 'Y'

where rn=1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

This is an exact match for your stated requirements and results. However, if I were a betting person, I'd put money on it being wrong - because you've missed out vital information from your selection criteria.

;WITH FlaggedData AS (

SELECT Lnno, Custcode, Flag, Amount, Amount_Flag,

MAXCoAp= MAX(CASE WHEN Flag = 'CoAp' THEN Amount ELSE 0 END) OVER(PARTITION BY Lnno),

MINCoAp= MIN(CASE WHEN Flag = 'CoAp' THEN Amount ELSE NULL END) OVER(PARTITION BY Lnno),

CustAmount= SUM(CASE WHEN Flag = 'Cust' THEN Amount ELSE NULL END) OVER(PARTITION BY Lnno),

seq= ROW_NUMBER() OVER(PARTITION BY Lnno, Amount ORDER BY Custcode)

FROM #Temp

)

SELECT Lnno, Custcode, Flag, Amount, Amount_Flag,

New_Amount_Flag = CASE

WHEN Flag = 'Cust' AND MAXCoAp = MINCoAp THEN 'Y' -- case 2)

WHEN Flag = 'CoAp' AND MAXCoAp <> MINCoAp AND CustAmount > 0 AND Amount = MAXCoAp THEN 'Y' -- case 1)

WHEN Flag = 'CoAp' AND CustAmount = 0 AND seq = 2 THEN 'Y' -- case 3)

ELSE '' END

FROM FlaggedData

ORDER BY Lnno, Flag DESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Stefan_G SSCertifiable Points: 6609 More actions · Answer 5

('3','C1','Cust',0,''),
('3','C2','CoAp',1000,''),
('3','C3','CoAp',1000,''),
('3','C4','CoAp',5000,'')
I has to Update Amount_Flag as Y,to maximum Amount for that Lnno.
3)In Case Lnno = 3,Cust has amount 0,but two CoAp has Amount same that is 1000,so Amount_Flag should be updated to any
one of the record.

I am a bit confused about how you want to handle Lnno=3. Your first requirement says that the row with the maximum value should be updated. This would mean that CoAp C4 would be updated since 5000 > 1000

In your text about Lnno=3 you say that either C2 or C3 could be updated since they have the same 1000 value.

Which version is correct?

My code assumes that we should look at the value first, so I update the row with value=5000.

terry999 SSCertifiable Points: 5036 More actions · Answer 6

didn't know partition by existed - very nice func.

You can rank within a group.

Thank you...

avdhut.k SSCrazy Points: 2834 More actions · Answer 7

avdhut.k

SSCrazy

Points: 2834

August 8, 2013 at 7:55 am

#1639782

Thanks All!

it works for me.

avdhut.k SSCrazy Points: 2834 More actions · Answer 8

Hi,

Can any one suggest me how to update the Amount_Flag for same if they have same Flag as "Cust" and same Amount.

Please help me !!

Thanks in Advance!!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

I've no idea what you mean - "update the Amount_Flag for same" means do nothing in English. Please provide an example.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

avdhut.k SSCrazy Points: 2834 More actions · Answer 10

Hi,

I mean Now rest of the things are correct in my requirement,

Now, In case where for two records, if flag = cust and Amount_Flag = Y and Amount is also same.

Then for only one record Amount_Flag = 'Y' should be updated.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

It might be easier for us to understand if you can describe how the result set from my query is different to what you want.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Stefan_G SSCertifiable Points: 6609 More actions · Answer 12

avdhut.k (8/12/2013)
Hi,
I mean Now rest of the things are correct in my requirement,
Now, In case where for two records, if flag = cust and Amount_Flag = Y and Amount is also same.
Then for only one record Amount_Flag = 'Y' should be updated.

If I understand you correctly, my code already handles this case.

If not, what is wrong with it?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Stefan_G (8/12/2013)
avdhut.k (8/12/2013)
Hi,
I mean Now rest of the things are correct in my requirement,
Now, In case where for two records, if flag = cust and Amount_Flag = Y and Amount is also same.
Then for only one record Amount_Flag = 'Y' should be updated.
If I understand you correctly, my code already handles this case.
If not, what is wrong with it?

I think mine does too, but our results are different. Haven't we been here before recently?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

avdhut.k SSCrazy Points: 2834 More actions · Answer 14

As per Stefen_G,query it working expect in one case,

Create Table #Temp

(

Lnno Varchar(15),

Custcode varchar(10),

Flag varchar(10),

Amount Int,

Amount_Flag varchar(1)

)

Insert Into #Temp

Values ('1','A1','Cust',1000,''),

('1','A1','Cust',1000,'')

Select * from #Temp

/*

Hi,

As per my requirement it is working all right but only in one scenario it is not working,that I mentioned in sample data,

For Lnno = 1,

Custcode = A1

flag = Cust

And Amount is also same,then it is Updated Amount_Flag = 'Y' for both the records,

My requirement is in this case it should be updated to only any one record.

Please Help me As I not able find out this.

Thanks in Advance !!

*/