Calculate differences

Question

Calculate differences

Viewing 11 posts - 16 through 26 (of 26 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Matt Miller (8/24/2009)
You could use a modified version of Jeff Moden's "running total" solution to carry the value around. In high cardinality sets - it should outpace the correlated subquery rather dramatically:
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/%5B/url%5D
edit: sorry - initally posted the wrong article link. this one's the right one!

Hmm, Yes, could update one millions rows in seconds one could. Time I need to set it up. No million row test set, however.

andymackk Valued Member Points: 64 More actions · Answer 2

ooohhh the force is strong in these ones

These Are the droids I am looking for 😀

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Done I am, here is the new code. Work this will in SQL Server 2000.

Edit: Special note there is, clustered index mandatory it is. Work it won't without.

andymackk Valued Member Points: 64 More actions · Answer 4

Lynn,

I have gone with your original code (attached again)because:

1) the Timestamp is always in date order, so that would appear to be ok

2) There is less code and it looks easier :unsure:

Comments:

1) The "Identity" value was a great help - now I can add in to my existing table

2) Never have I come across a subject that makes me feel so Thick so quickly 🙂

3) major respect to all you experts - this aint easy...

Questions:

What significance is the (1,1) when declaring the ID Field?

If I Use the # in the table name - i cant see it

Is it there (But Hidden) - or just a Temp Table?

I can run your code, and it works fine. Am I right in thinking this doesn't create a permanent table?

I would like to create a table to put this data in, so I can run a calculation on the Resulting usage (i.e multiply it by a cost factor) I cant figure out how to create a calulation, because the answers are only displayed in a column with no title.

Sorry if I'm pushing my luck 😛

Many thanks again

Andy

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

andymackk (8/25/2009)
Lynn,
I have gone with your original code (attached again)because:
1) the Timestamp is always in date order, so that would appear to be ok
2) There is less code and it looks easier :unsure:
Comments:
1) The "Identity" value was a great help - now I can add in to my existing table
2) Never have I come across a subject that makes me feel so Thick so quickly 🙂
3) major respect to all you experts - this aint easy...
Questions:
What significance is the (1,1) when declaring the ID Field?
If I Use the # in the table name - i cant see it
Is it there (But Hidden) - or just a Temp Table?
I can run your code, and it works fine. Am I right in thinking this doesn't create a permanent table?
I would like to create a table to put this data in, so I can run a calculation on the Resulting usage (i.e multiply it by a cost factor) I cant figure out how to create a calulation, because the answers are only displayed in a column with no title.
Sorry if I'm pushing my luck 😛
Many thanks again
Andy

What significance is the (1,1) when declaring the ID Field?

This is the seed (1) and increment (1) for the identity column.

If I Use the # in the table name - i cant see it
Is it there (But Hidden) - or just a Temp Table?

The single # indicates this is a local temporary table. You can create a permanent table if you need.

I can run your code, and it works fine. Am I right in thinking this doesn't create a permanent table?

Yes. It is creating a temporary table for purposes of illustration. I could do the same with a perm table as well.

I would like to create a table to put this data in, so I can run a calculation on the Resulting usage (i.e multiply it by a cost factor) I cant figure out how to create a calulation, because the answers are only displayed in a column with no title.

Not quite sure what you are asking here. You can do a calculation in a query. To give it a column name you just need to add a column alias after the calculation. If you need to use the original calculation again, you have to repeat it. It would probably be easier to show you if you provide the necessary details.

One thing to keep in mind with identity collumns. If an insert fails, the column still increments and my original code will break. To avoid that, and if you are using SQL Server 2005, my second version with the CTE and row_number() function may be a safer alternative.

andymackk Valued Member Points: 64 More actions · Answer 6

would it be possible for you to show me how to put the results into a permanent table?

I tried , and it created a table, but it isn't the results that end up in it, just the original readings.

Thanks for the tip about the identity field. Like I say - the real reason I used the first one, was cos i wanted to walk before i could run. Am I right in thinking that the attached file is the one i should targetting?

My Reasoning behind the calculations is just wanting to be able to work out costs etc against the usage. I looked at your example and just got totally snowblind with it all. I just couldn't figure out how to do the calculations on a variable that didn't have a name :hehe:

Your other answers have also helped as well.

Many thanks

Andy

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

andymackk (8/25/2009)
would it be possible for you to show me how to put the results into a permanent table?
I tried , and it created a table, but it isn't the results that end up in it, just the original readings.
Thanks for the tip about the identity field. Like I say - the real reason I used the first one, was cos i wanted to walk before i could run. Am I right in thinking that the attached file is the one i should targetting?
My Reasoning behind the calculations is just wanting to be able to work out costs etc against the usage. I looked at your example and just got totally snowblind with it all. I just couldn't figure out how to do the calculations on a variable that didn't have a name :hehe:
Your other answers have also helped as well.
Many thanks
Andy

If you want the results of the calculation in the table, then yes, I'd go with my last post. A part of me, however, thinks there is more to what you need than what you have provided. It could be I am think more like a utility where you have multiple reading for multiple customers. I'm not sure what your environment is so it is hard to know if you actually have what you need.

andymackk Valued Member Points: 64 More actions · Answer 8

The project I have is a few electricity meters that were already installed in our building and the management just want me to try and extrapolate the data to try and lower their utility charges/ carbon foot print.

I have about 8 different meters attached to different circuits (Aircon, floor sockets, kitchen etc). I have managed to find a utility that polls the meters, and puts the meter readings into SQL. I just made a table for each meter.

As is the want with management, they dont want any hassle or work, they just want a report for how much usage (and associated costs) for any of the meters. I was just going to create a spreadsheet that would summarise it.

If i can do the calculations at the SQL side, i can link Excel straight to the correct data, it will all be transparent to them, and they will just see what they need.

I dont want to bog you down with anything, I very much appreciate the help you have given so far.

I just thought if i could do it with one table, I could replicate it with the others. That was why i wanted to get the results into a table.

Regards

Andy

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

I'll get back to you more this evening if I can. Have things I need to do right now. If not tonight, definately tomorrow evening.

andymackk Valued Member Points: 64 More actions · Answer 10

In the cold light of another day, I think I see what I should have been doing.

I was looking at storing all results in a table so I could access them, but i must be able to run the query from Excel, (Is that just a stored procedure?)

I have now figured how to add in the column headings and also to do a calculation(s) on the results as part of the query, so thats all good.

Ideally, one thing that would be good would be to add all the results for the 8 Meters into one table, giving a matrix, but I get the feeling that may be a bit out of my depth. (would that be an array, or would i use a UNION?)

Not looking for the code - I'll have a go, see what I can learn.

Thanks again for all help (and Patience)

Andy

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Unfortunately, I didn't have time yesterday like I hoped. How are you going to approach this problem? Show me what you have and I'll try to provide some feedback.