SELECT b.PartID,b.PartDescription,
SUM(a.Amount)
FROM PartCode b
CROSS APPLY (SELECT TOP 2 Amount
             FROM AmountUpdateHistory
             WHERE PartID = b.PartID
             ORDER BY effectiveDate DESC) a
GROUP BY b.PartID,b.PartDescription

SELECT b.PartID,b.PartDescription,
SUM( (SELECT TOP 2 Amount
             FROM AmountUpdateHistory
             WHERE PartID = b.PartID
             ORDER BY effectiveDate DESC))
FROM PartCode b
GROUP BY b.PartID,b.PartDescription

quote:

---

Originally posted by visakh16

If sql 2005 or above

SELECT b.PartID,b.PartDescription,
SUM(a.Amount)
FROM PartCode b
CROSS APPLY (SELECT TOP 2 Amount
             FROM AmountUpdateHistory
             WHERE PartID = b.PartID
             ORDER BY effectiveDate DESC) a
GROUP BY b.PartID,b.PartDescription

if sql 2000

SELECT b.PartID,b.PartDescription,
SUM( (SELECT TOP 2 Amount
             FROM AmountUpdateHistory
             WHERE PartID = b.PartID
             ORDER BY effectiveDate DESC))
FROM PartCode b
GROUP BY b.PartID,b.PartDescription

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

declare @t1 table(
partid varchar(50),
effectivedate date,
amount money
);

declare @t2 table(
partid varchar(50),
partdescription varchar(100)
);

insert into @t1 (partid, effectivedate, amount)
values('partone', '03-31-2010',1.00),
('partone', '04-01-2010',1.01)
,('partone', '04-01-2010',1.00)
,('parttwo', '03-31-2010',2.00)
,('parttwo', '04-01-2010',2.01)
,('partthree', '03-31-2010',3.00)
,('partthree', '04-02-2010',3.01)
,('partfour', '03-31-2010',4.00)
,('partfour', '04-01-2010',4.01)
,('partfour', '04-01-2010',4.02)
,('partfive', '03-31-2010',5.00)
,('partfive', '03-31-2010',5.01)
,('partfive', '03-31-2010',5.02)
,('partfive', '04-01-2010',5.03)
,('partsix', '03-31-2010',6.00)
,('partsix', '04-01-2010',6.01)

insert into @t2 (partid, partdescription)
values('partone', 'part one')
,('parttwo', 'part two')
,('partthree', 'part three')
,('partfour', 'part four')
,('partfive', 'part five')
,('partsix', 'part six')

declare @t1temp table(
pid uniqueidentifier default newid(),
partid varchar(50),
effectivedate date,
amount money
);

insert into @t1temp(partid, effectivedate, amount) select * from @t1;

declare @t2temp table(
pid uniqueidentifier default newid(),
partid varchar(50),
partdescription varchar(100)
);

insert into @t2temp(partid, partdescription) select * from @t2;

select distinct c.partid, c.partdescription, b.ttlamt from (select * from @t1temp z 
where pid in (
select top 2 pid from @t1temp
where partid = z.partid
order by effectivedate desc
)) a join (select partid, sum(amount) as ttlamt from (select * from @t1temp z 
where pid in (
select top 2 pid from @t1temp
where partid = z.partid
order by effectivedate desc
))b
group by partid)b on a.partid = b.partid
join @t2temp c on c.partid = a.partid
;

quote:

---

Originally posted by freakofworks

Sorry, how to find PK? I think there is a "hash" column that has unique values for each record.

Thanks again for your response.

---

alter table AmountUpdateHistory add pid uniqueidentifier not null default newid() primary key;
primary key;

quote:

---

Originally posted by hanbingl

you can create primary keys with newid() function and its easy:

alter table AmountUpdateHistory add pid uniqueidentifier not null default newid() primary key;
primary key;

---