incrementing value sql query help

Question

incrementing value sql query help

tr763

Newbie

Points: 3
More actions
September 23, 2014 at 7:32 am

#290221

I have two columns in a table right now. My query is inserting data into this table from another so that non technical users can view this database value via excel. One column shows the date and the other shows a numeric value. This value grows daily and is used to determine when a reseed of the database is needed. I would like to add a third column to this table that shows the difference in growth. For example if 9/19 had a value of 1000 and 9/20 had a value of 1100, the third column would have a value of 100. My plan is to create a column in excel that calculates the number of days until the reseed is required based on average growth. It would be much easier to get this difference in growth from the database than through an excel formula. Any ideas? Our DBA doesn't know how to do this.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

tr763 (9/23/2014)
I have two columns in a table right now. My query is inserting data into this table from another so that non technical users can view this database value via excel. One column shows the date and the other shows a numeric value. This value grows daily and is used to determine when a reseed of the database is needed. I would like to add a third column to this table that shows the difference in growth. For example if 9/19 had a value of 1000 and 9/20 had a value of 1100, the third column would have a value of 100. My plan is to create a column in excel that calculates the number of days until the reseed is required based on average growth. It would be much easier to get this difference in growth from the database than through an excel formula. Any ideas? Our DBA doesn't know how to do this.

That depends. Are you using sql 2014? If so, you should use the LEAD and LAG functions for this. If you are on an older version you will have to do a running total OR a cte.

Here is a link to the running total method.

http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]

Doing this in excel is completely the wrong place.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

tr763 Newbie Points: 3 More actions · Answer 2

thank you for response. He's using a SQL 2008 R2 database

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

Here's a simple sample:

if not object_id('tempdb..#values') is null

drop table #values

create table #values (daily_date datetime, value int)

insert into #values

values('20140810', 20)

, ('20140811',40)

, ('20140812',80)

, ('20140813',70)

, ('20140814',70)

, ('20140815',60)

SELECT

cur.daily_date as 'today'

, prev.value as 'yesterday_value'

, cur.value as 'today_value'

, cur.value - prev.value as 'difference'

FROM #values cur

INNER JOIN #values prev

on cur.daily_date = dateadd(day, 1, prev.daily_date)

if not object_id('tempdb..#values') is null

drop table #values

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

tr763 Newbie Points: 3 More actions · Answer 4

You guys are fast. I"m going to forward him this example and the link. Hopefully he can resolve this one pretty quick.