February 22, 2015 at 10:40 pm
create table #test
( id int ,
typ varchar(10) ,
amt int,
code varchar(10),
createdDt datetime,
AccNo int,
CCY varchar(10),
AppNo int
)
truncate table #test
insert into #test
Select 1,'LP',10,'SALE','9999-12-31 23:59:59',10,'AUD',100
UNION
Select 1,'VA',20,'SALE','9999-12-31 23:59:59',20,'AUD',110
UNION
Select 2,'LP',10,'SALE','9999-12-31 23:59:59',10,'AUD',100
UNION
Select 3,'VA',20,'SALE','9999-12-31 23:59:59',20,'AUD',110
When you have more than one record for an id i need to sum the amount and display one record with the other fields from the record which has Typ-LP else whatever exists
There are only two types so if LP then LP else VA.Account number is unique to Type.
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 22, 2015 at 10:58 pm
Please try the below one for your requirement
---------------------------------------------------------------------------
Selectid,
SumAmt,
Typ,
code ,
createdDt,
CCY,
AppNo
from (
select S.Id,
S.SumAmt,
M.typ,
M.code ,
M.createdDt,
M.CCY,
M.AppNo,
ROW_NUMBER() over (Partition by M.Id Order by M.Typ Asc) Rn
from #test M
RIGHT Join (
select id,
SUM(amt) SumAmt,
code ,
createdDt,
CCY
from #test group by Id,code ,createdDt,CCY
) S on M.Id=S.id
)A where Rn=1
-----------------------------------------------------------------------------
Cheers!...
Sasidhar Pulivarthi
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply