datediff query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

datediff query

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-11-21 : 10:51:18

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-21 : 11:02:42

[code]-- 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[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-21 : 11:09:20

;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

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-11-21 : 11:09:54

could you tell me what LAG is please

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-21 : 11:26:30

quote:
Originally posted by masterdineen

could you tell me what LAG is please

A function.

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 11:30:34

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.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2012-11-22 : 04:36:33

Thank you very much Jim and others.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-22 : 04:57:48

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
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-11-22 : 20:20:54

[code]
;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
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources