April 25, 2008 at 2:07 pm
Here is the data from db_size table:
data_id run_date data_size_mb
33112007-01-02 00:00:00.0002977404.13
33272007-01-04 00:00:00.0002952083.63
33352007-01-05 00:00:00.0002920131.50
33512007-01-07 00:00:00.0002921953.13
33752007-01-10 00:00:00.0002874944.88
33592007-01-08 00:00:00.0002870194.63
33832007-01-11 00:00:00.0002880788.81
33992007-01-13 00:00:00.0002886499.13
34312007-01-17 00:00:00.0002908387.75
34472007-01-19 00:00:00.0002916278.94
34552007-01-20 00:00:00.0002933075.88
34712007-01-22 00:00:00.0002946644.69
34952007-01-25 00:00:00.0002965383.31
35032007-01-26 00:00:00.0002971379.56
35112007-01-27 00:00:00.0002972782.44
35192007-01-28 00:00:00.0002981314.06
33032007-01-01 00:00:00.0002979226.31
33192007-01-03 00:00:00.0002973172.19
33432007-01-06 00:00:00.0002913943.56
33672007-01-09 00:00:00.0002865906.56
I am trying to find the difference between the 2 consecutive records i.e data_id 3327 and data_id 3311 for the column data_size_mb.
Obviously the following query is wrong as it is going to find the difference for all the smaller ids. I mean For the 3rd row, I will have difference between 3rd and 1st and 3rd and 2nd. For the 4th row, I will have results between 4th and 1st , 4th and 2nd, 4th and 3rd and increasing with every higher id.
select b.data_size_MB - a.data_size_MB as growth_per_day,
b.data_size_MB,
b.run_date
from db_size a,
db_size b
where a.[data_id] < b.[data_id]
Can it be done using the single statement query? What is the correct syntax? I can write the batch of statements to accomlish this. I am just curious if I can do it using teh single query.
Thanks,
April 25, 2008 at 2:42 pm
I think this will work:
[font="Courier New"]CREATE TABLE #db_size
(
data_Id INT,
run_Date DATETIME,
data_size_mb DECIMAL(15, 2)
)
INSERT INTO #db_size
SELECT
3311, '2007-01-02 00:00:00.000', 2977404.13
UNION
SELECT
3327 ,'2007-01-04 00:00:00.000', 2952083.63
UNION
SELECT
3335 ,'2007-01-05 00:00:00.000', 2920131.50
UNION
SELECT
3351 ,'2007-01-07 00:00:00.000', 2921953.13
UNION
SELECT
3375 ,'2007-01-10 00:00:00.000', 2874944.88
UNION
SELECT
3359 ,'2007-01-08 00:00:00.000',2870194.63
UNION
SELECT
3383 ,'2007-01-11 00:00:00.000', 2880788.81
UNION
SELECT
3399 ,'2007-01-13 00:00:00.000', 2886499.13
UNION
SELECT
3431 ,'2007-01-17 00:00:00.000', 2908387.75
UNION
SELECT
3447 ,'2007-01-19 00:00:00.000', 2916278.94
UNION
SELECT
3455 ,'2007-01-20 00:00:00.000', 2933075.88
UNION
SELECT
3471 ,'2007-01-22 00:00:00.000', 2946644.69
UNION
SELECT
3495 ,'2007-01-25 00:00:00.000', 2965383.31
UNION
SELECT
3503 ,'2007-01-26 00:00:00.000', 2971379.56
UNION
SELECT
3511 ,'2007-01-27 00:00:00.000', 2972782.44
UNION
SELECT
3519 ,'2007-01-28 00:00:00.000', 2981314.06
UNION
SELECT
3303 ,'2007-01-01 00:00:00.000', 2979226.31
UNION
SELECT
3319 ,'2007-01-03 00:00:00.000', 2973172.19
UNION
SELECT
3343 ,'2007-01-06 00:00:00.000', 2913943.56
UNION
SELECT
3367 ,'2007-01-09 00:00:00.000', 2865906.56
;WITH cteDbSize AS
(
SELECT
Row_Number() OVER (ORDER BY data_id) AS rowno,
data_id,
data_size_mb,
run_date
FROM
#db_size
)
SELECT
A.data_id AS a_id,
B.data_id AS b_id,
A.data_size_mb AS a_size,
B.data_size_mb AS b_size,
A.data_size_mb - B.data_size_mb AS a_size_minus_b_size
FROM
cteDbSize A JOIN
cteDbSize B ON
A.rowno = B.rowno-1
DROP TABLE #db_size
[/font]
It orders by data_id assuming that you want to know the next one. Your example data was not in order by data_id, but I think you may have meant it to be.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 25, 2008 at 2:43 pm
Nested selects will solve it. And, there are articles that give you step by step instructions, including caveats regarding performance. So, rather than reinvent the wheel, here are the references (and, if we're lucky, my attempts to activate the links will work, without having to paste into a browser):
this article is the one i was thinking of.
http://www.sqlteam.com/article/joining-to-the-next-sequential-row
SSC link is: http://www.sqlservercentral.com/NewsletterArchive/2008/04/10/731685
doing a search on 'sequential' came up with a long list. excerpts include (not all are of equal relevance):
http://www.sqlservercentral.com/articles/T-SQL/62159/
http://www.sqlservercentral.com/Forums/Topic456438-8-1.aspx
http://www.sqlservercentral.com/articles/Advanced+Querying/casestatement/190/
Enjoy! the later entries will help inspire you to look at the question in different ways but are not exactly on point. but the first article should give you exactly what you need.
April 26, 2008 at 11:45 am
It orders by data_id assuming that you want to know the next one. Your example data was not in order by data_id, but I think you may have meant it to be.
Aye... nicely done, Jack!
--Jeff Moden
Change is inevitable... Change for the better is not.
April 28, 2008 at 1:01 pm
select d2.data_size_mb - d1.data_size_mb,d2.data_size_mb,d2.run_Date
from db_size d1 ,db_size d2
where d1.data_id<d2.data_id and
d2.data_id in (
select min(data_ID) from db_size
where data_id>d1.data_id
)
order by d2.data_ID
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply