calculate dates between rows

Question

Post reply

calculate dates between rows

FilMar

Valued Member

Points: 51
More actions
May 11, 2010 at 5:17 am

#224404

I have a table that contains a historical list of INs and OUTs of articles
Art Status Date
A In 10/10/2009
A Out 15/10/2009
A In 16/10/2009
A Out 20/10/2009
A In 22/10/2009
A Out 23/10/2009
...
B In 11/10/2009
B Out 21/10/2009
...
I need a query that gives the average time (in days) the article is IN (between each In and next Out) and also one that gives the average time the Article is OUT (between each Out and next In)
Many thanks in advance,

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This should do the trick for you:

DECLARE @Art TABLE (

[Art] char(1),

[Status] varchar(3),

[Date] datetime

)

INSERT INTO @Art

SELECT 'A', 'In' , '2009-10-10' UNION ALL

SELECT 'A', 'Out', '2009-10-15' UNION ALL

SELECT 'A', 'In' , '2009-10-16' UNION ALL

SELECT 'A', 'Out', '2009-10-20' UNION ALL

SELECT 'A', 'In' , '2009-10-22' UNION ALL

SELECT 'A', 'Out', '2009-10-23' UNION ALL

SELECT 'B', 'In' , '2009-10-11' UNION ALL

SELECT 'B', 'Out', '2009-10-21'

;WITH Art AS (

SELECT *, Diff = DATEDIFF(day, [Date], (

SELECT TOP 1 [Date]

FROM @Art

WHERE [Art] = A.[Art]

AND [Date] > A.[Date]

AND [Status] = CASE WHEN A.[Status] = 'IN' THEN 'OUT' ELSE 'IN' END

ORDER BY [Date]

) )

FROM @Art AS A

) ,

SummaryArt AS (

SELECT [Art], [Status], AVG(Diff) AS AVG_DIFF

FROM Art

GROUP BY [Art], [Status]

)

SELECT [Art], [In] AS [AVG_Days_In], [Out] AS [AVG_Days_Out]

FROM SummaryArt AS A

PIVOT(MAX(Avg_Diff) FOR [Status] IN ([In],[Out])) AS p

Next time you ask for help, try providing your sample data in a more "consumable" way: in other words, code your sample data as code somebody can copy and paste in SSMS. You can find directions on how to do this reading the article linked in my signature.

Hope this helps

Gianluca

-- Gianluca Sartori

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

There might be some mileage in this too:

SET DATEFORMAT DMY

DROP TABLE #Temp

CREATE TABLE #Temp (Art CHAR(1), [Status] VARCHAR(3), [Date] DATE)

INSERT INTO #Temp (Art, [Status], [Date])

SELECT 'A', 'In', '10/10/2009' UNION ALL

SELECT 'A', 'Out', '15/10/2009' UNION ALL

SELECT 'A', 'In', '16/10/2009' UNION ALL

SELECT 'A', 'Out', '20/10/2009' UNION ALL

SELECT 'A', 'In', '22/10/2009' UNION ALL

SELECT 'A', 'Out', '23/10/2009' UNION ALL

SELECT 'B', 'In', '11/10/2009' UNION ALL

SELECT 'B', 'Out', '21/10/2009'

SELECT i.Art, i.[Date] AS InDate, o.[Date] AS OutDate

FROM (

SELECT Art, [Status], [Date], Eventno = ROW_NUMBER() OVER (PARTITION BY Art ORDER BY [Date])

FROM #Temp

WHERE [Status] = 'In'

) i

LEFT JOIN (

SELECT Art, [Status], [Date], Eventno = ROW_NUMBER() OVER (PARTITION BY Art ORDER BY [Date])

FROM #Temp

WHERE [Status] = 'Out'

) o ON o.Art = i.Art AND o.Eventno = i.Eventno

ORDER BY i.Art, i.[Date], o.[Date]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

ColdCoffee

SSC-Dedicated

Points: 39972

May 11, 2010 at 6:07 am

#1165480

Wow, Gianluca.. cool code.. 😎

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

ColdCoffee (5/11/2010)
Wow, Gianluca.. cool code.. 😎

Thanks, but we still don't know if this is what the OP is after.

Could turn out to be cool... garbage! 😛

-- Gianluca Sartori

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Gianluca Sartori (5/11/2010)
ColdCoffee (5/11/2010)
Wow, Gianluca.. cool code.. 😎
Thanks, but we still don't know if this is what the OP is after.
Could turn out to be cool... garbage! 😛

:-P. Gian, i got one thing here.. Your code "FLOOR"s the AVG.. why dont you make it to use numeric/decimal and the make the AVG look real sir?!!

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Sure it's an option. It depends on business requirements that only the OP knows. Let's wait for him to say.

-- Gianluca Sartori

FilMar Valued Member Points: 51 More actions · Answer 7

Thanks for the quick help but I have to excuse me twice.

First for not posting the sample data in the right way, second while I see now that I asked the question in the wrong forum, I'm still on SQL-Server 2000.

Sorry!

I'll keep this solution by the hand while I think we will upgrade in the future but for the moment it does not help.

Can this be moved or should I ask it again in the right forum?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

hmmm.. yes.. lets wait for FilMar to march on! 😀

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

It can surely be converted in SQL2000 code.

Just get rid of CTEs and PIVOT and it will look like this:

SELECT [Art],

Avg_Days_in = SUM(CASE [Status] WHEN 'In' THEN AVG_DIFF END),

Avg_Days_out = SUM(CASE [Status] WHEN 'Out' THEN AVG_DIFF END)

FROM (

SELECT [Art], [Status], AVG(Diff) AS AVG_DIFF

FROM (

SELECT *, Diff = DATEDIFF(day, [Date], (

SELECT TOP 1 [Date]

FROM @Art

WHERE [Art] = A.[Art]

AND [Date] > A.[Date]

AND [Status] = CASE WHEN A.[Status] = 'IN' THEN 'OUT' ELSE 'IN' END

ORDER BY [Date]

) )

FROM @Art AS A

)

AS Art

GROUP BY [Art], [Status]

) AS SummaryArt

GROUP BY [Art]

-- Gianluca Sartori

FilMar Valued Member Points: 51 More actions · Answer 10

Thanks a lot for the quick answer, I'll try it out as soon as I can.