SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 datediff query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 11/21/2012 :  10:51:18  Show Profile  Reply with Quote
Hello there

i have the following information
AuctionID BID DateofBid userID

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

I am trying to create the following query and im not sure how i would go about it.

select AuctionID, sum(bid), datediff(highest bid & 2nd highest bid)

the bit iam strugling with is the datediff between the two highest bids.

Regards

Rob

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 11/21/2012 :  11:02:42  Show Profile  Reply with Quote
-- 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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/21/2012 :  11:09:20  Show Profile  Reply with Quote
;with BIDs 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 BIDs bid1
INNER JOIN BIDs bid2 ON bid1.auctionId = bid2.AuctionID
WHERE BID1.BidRank = 1 and bid2.BidRank = 2


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 11/21/2012 :  11:09:54  Show Profile  Reply with Quote
could you tell me what LAG is please
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 11/21/2012 :  11:26:30  Show Profile  Reply with Quote
quote:
Originally posted by masterdineen

could you tell me what LAG is please

A function.

http://msdn.microsoft.com/en-us/library/hh231256.aspx

Edited by - Lamprey on 11/21/2012 11:27:06
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/21/2012 :  11:30:34  Show Profile  Reply with Quote
To add to what Lamprey said: LAG is available only in SQL 2012, so if you are on a earlier version of SQL Server, try the code Jim posted.
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
548 Posts

Posted - 11/22/2012 :  04:36:33  Show Profile  Reply with Quote
Thank you very much Jim and others.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 11/22/2012 :  04:57:48  Show Profile  Reply with Quote
just one note, the cte name cannot be same as one of the table name inside the cte code

;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



KH
Time is always against us

Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 11/22/2012 :  20:20:54  Show Profile  Reply with Quote

;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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000