August 8, 2013 at 5:00 am
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!!
*/
August 8, 2013 at 5:28 am
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'
August 8, 2013 at 5:33 am
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..
August 8, 2013 at 5:35 am
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
August 8, 2013 at 6:40 am
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
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
August 8, 2013 at 6:49 am
('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.
August 8, 2013 at 7:20 am
didn't know partition by existed - very nice func.
You can rank within a group.
Thank you...
August 8, 2013 at 7:55 am
Thanks All!
it works for me.
August 11, 2013 at 11:52 pm
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!!
August 12, 2013 at 1:04 am
I've no idea what you mean - "update the Amount_Flag for same" means do nothing in English. Please provide an example.
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
August 12, 2013 at 1:15 am
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.
August 12, 2013 at 2:07 am
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.
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
August 12, 2013 at 6:21 am
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?
August 12, 2013 at 6:23 am
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?
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
August 13, 2013 at 1:27 am
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 !!
*/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply