-- Sample Data
DECLARE @Foo TABLE (AuctionID INT, BID INT, DateofBid DATE, userID INT)
INSERT @Foo VALUES

(1, 500, '2012-11-21', 10),
(1, 456, '2012-11-18', 11),
(1, 440, '2012-11-17', 21),
(1, 300, '2012-11-21', 13),
(3, 645, '2012-11-20', 2),
(3, 634, '2012-11-15', 6),
(3, 600, '2012-11-14', 8)

-- Query
SELECT 
    AuctionID,
    TotalBidAmount,
    DifferenceInDays
FROM
    (
        SELECT 
            AuctionID,
            SUM(Bid) OVER (PARTITION BY AuctionID) TotalBidAmount,
            DATEDIFF(DAY, LAG(DateofBid) OVER (ORDER BY DateofBid), DateofBid) AS DifferenceInDays,
            ROW_NUMBER() OVER (PARTITION BY AuctionID ORDER BY DateofBid DESC) AS RowNum
        FROM
            @Foo
    ) AS T
WHERE
    RowNum = 1

quote:

---

Originally posted by masterdineen

could you tell me what LAG is please

---

;with BID AS
(
	SELECT 	*
		,[BidRank] = Row_Number() OVER(partition by auctionID order by bid desc)
	FROM 	BIDs
)
SELECT 	bid1.AuctionID, bid1.bid + bid2.bid
	,DATEDIFF(day,bid2.DateOfbid,bid1.dateOfBid)
FROM 	BID bid1
	INNER JOIN BID bid2 ON bid1.auctionId = bid2.AuctionID
WHERE 	bid1.BidRank = 1 and bid2.BidRank = 2

;with cte 
as(
	select
		a.*
	,	bDateofBid=b.DateofBid
	,	rn=ROW_NUMBER() over (partition by a.auctionid, a.bid order by a.bid desc)
	,	rn2 = ROW_NUMBER() over (partition by a.auctionid order by a.bid desc)
	from @Foo a 
	left join @Foo b 
	on a.AuctionID = b.AuctionID and a.BID>b.BID
)
select 
	AuctionID
,	sum(bid)
,	(select DATEDIFF(d, DateofBid, bDateofBid ) from cte b where a.auctionid = b.auctionId and rn2 = 1)
from cte a
where rn = 1
group by AuctionID