July 6, 2010 at 3:33 am
Morning all,
I'm stuck.
I have a table:
int(mb) datetime
1 2010-07-01 09:21
35 2010-07-02 09:11
3 2010-07-03 09:31
33 2010-07-04 09:21
I want to know the increments between each.
diff(mb) datediff
34 1 day
It's a tble that measures file size growth. I don't need exact datediffs, but I want to be able to identify jumps in file size growth.
Can anybody point me in the right direction?
Thanks!!
July 6, 2010 at 4:02 am
This should help:
DECLARE @test-2 TABLE (
size int,
sdate datetime
)
INSERT INTO @test-2
SELECT 1 , '2010-07-01 09:21' UNION ALL
SELECT 35, '2010-07-02 09:11' UNION ALL
SELECT 3 , '2010-07-03 09:31' UNION ALL
SELECT 33, '2010-07-04 09:21'
SELECT DiffSize = A.size - ISNULL(B.size,0),
A.sdate,
Days = DATEDIFF(day, B.sdate, A.sdate)
FROM @test-2 AS A
OUTER APPLY (
SELECT TOP 1 *
FROM @test-2
WHERE sdate < A.sdate
ORDER BY sdate DESC
) AS B
EDITED: I added the calculation for datediff. I didn't notice you needed that also.
-- Gianluca Sartori
July 6, 2010 at 4:16 am
Blimey, thanks very much!! That works a treat. Really appreciate the reply. Have a good day. 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply