April 25, 2008 at 2:07 pm
Here is the data from db_size table:
data_id run_date data_size_mb
3311 2007-01-02 00:00:00.000 2977404.13
3327 2007-01-04 00:00:00.000 2952083.63
3335 2007-01-05 00:00:00.000 2920131.50
3351 2007-01-07 00:00:00.000 2921953.13
3375 2007-01-10 00:00:00.000 2874944.88
3359 2007-01-08 00:00:00.000 2870194.63
3383 2007-01-11 00:00:00.000 2880788.81
3399 2007-01-13 00:00:00.000 2886499.13
3431 2007-01-17 00:00:00.000 2908387.75
3447 2007-01-19 00:00:00.000 2916278.94
3455 2007-01-20 00:00:00.000 2933075.88
3471 2007-01-22 00:00:00.000 2946644.69
3495 2007-01-25 00:00:00.000 2965383.31
3503 2007-01-26 00:00:00.000 2971379.56
3511 2007-01-27 00:00:00.000 2972782.44
3519 2007-01-28 00:00:00.000 2981314.06
3303 2007-01-01 00:00:00.000 2979226.31
3319 2007-01-03 00:00:00.000 2973172.19
3343 2007-01-06 00:00:00.000 2913943.56
3367 2007-01-09 00:00:00.000 2865906.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://qa.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://qa.sqlservercentral.com/articles/T-SQL/62159/
http://qa.sqlservercentral.com/Forums/Topic456438-8-1.aspx
http://qa.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
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