Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
DATEDIFF for dates pulled from two separate rows

Answered DATEDIFF for dates pulled from two separate rows

  • Friday, November 23, 2012 2:49 PM
     
     
    Sign In to Vote
    0

    Can someone please kindly help me address the DATEDIFF predicament before me. Please refer to the table below.

    MemberID

    ActivityID

    BorrowedDate

    ReturnedDate

    AA

    994994994

    12/09/2011  16:00:00

    12/09/2011  16:00:00

    AA

    221221221

    04/10/2011  10:00:00

    04/10/2011  10:00:00

    BB

    110021100

    07/09/2011  17:00:00

    07/09/2011  17:00:00

    BB

    555002200

    08/09/2011  12:05:00

    08/09/2011  12:05:00

    This table has two members, member AA & member BB. Records reveal that each has borrowed twice. What I would like is a SQL query which will help me calculate the period in days between the ReturnedDate and the next BorrowedDate following immediately after the preceding ReturnedDate. For example, in simple terms for member AA, DATEDIFF which I’m looking for with literal dates supplied should look like

    select datediff(dd,'12/09/2011  16:30:00','04/10/2011  10:00:00')

    I’m lost for ideas about how I can accomplish this in a dynamic way for more than one million rows.

    Many thanks,

    Mpumelelo

    • Edited by Mpumelelo S Friday, November 23, 2012 2:59 PM
    •  
     

All Replies

  • Friday, November 23, 2012 3:18 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Hello,

    Something like this?

    CREATE TABLE #BorrowReturn
(
MemberID	CHAR(2) NOT NULL,
ActivityID	BIGINT NOT NULL,
BorrowedDate	DATETIME NOT NULL,
ReturnedDate	DATETIME NOT NULL
);
GO

INSERT INTO #BorrowReturn(MemberID, ActivityID, BorrowedDate, ReturnedDate)
VALUES
('AA', 994994994, '12/09/2011  16:00:00', '12/09/2011  16:00:00'),
('AA', 221221221, '04/10/2011  10:00:00', '04/10/2011  10:00:00'),
('BB', 110021100, '07/09/2011  17:00:00', '07/09/2011  17:00:00'),
('BB', 555002200, '08/09/2011  12:05:00', '08/09/2011  12:05:00')
GO

;WITH GetDiffDates
AS
(
SELECT MemberID,ActivityID, BorrowedDate, ReturnedDate, ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY ReturnedDate DESC) AS RN FROM #BorrowReturn
)
SELECT g1.MemberID, g1.ActivityID, g1.BorrowedDate, g1.ReturnedDate, g2.ActivityID, g2.BorrowedDate, g2.ReturnedDate, DATEDIFF(DAY, g2.ReturnedDate, G1.BorrowedDate) AS DaysBetween
FROM GetDiffDates G1
	INNER JOIN GetDiffDates G2
		ON G2.RN = G1.RN+1
		AND g2.MemberID = g1.MemberID
GO

INSERT INTO #BorrowReturn(MemberID, ActivityID, BorrowedDate, ReturnedDate)
VALUES
('AA', 123456789, '01/09/2012', '01/10/2012')
GO

;WITH GetDiffDates
AS
(
SELECT MemberID,ActivityID, BorrowedDate, ReturnedDate, ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY ReturnedDate DESC) AS RN FROM #BorrowReturn
)
SELECT g1.MemberID, g1.ActivityID, g1.BorrowedDate, g1.ReturnedDate, g2.ActivityID, g2.BorrowedDate, g2.ReturnedDate, DATEDIFF(DAY, g2.ReturnedDate, G1.BorrowedDate) AS DaysBetween
FROM GetDiffDates G1
	INNER JOIN GetDiffDates G2
		ON G2.RN = G1.RN+1
		AND g2.MemberID = g1.MemberID
WHERE g1.RN = 1
GO

DROP TABLE #BorrowReturn
GO

    -Sean

    Sean Gallardy | Blog | Twitter

    • Marked As Answer by Mpumelelo S Friday, November 23, 2012 3:45 PM
    •  
     
  • Friday, November 23, 2012 3:23 PM
     
     
    Sign In to Vote
    0

    try below query

    select Memberid, min(BorrowedDate) - max(ReturnedDate) from your table

    Thanks & Regards Prasad DVR

     
  • Friday, November 23, 2012 3:45 PM
     
     
    Sign In to Vote
    0

    Thank you Sean. Your solution has worked.

    Mpumelelo