September 5, 2013 at 9:12 am
I have an issue where I need to count the cumulative total column wise effectively and could not get a thought of it, I have posted on SQL server central.com but thought if you have an idea on this: I know you have lots to do so this is just when you want to get fresh : or you are more than welcome to avoid it.
I have currently dataset abosulate total) and I need the one in second line:
Opco Region Country Jan Feb Mar
Absolute total OP1 EMEAGB 0.3 1.3 0.7
Cumulative total OP1 EMEAgb 0.3 1.6 2.3
Regards
Meghna
September 5, 2013 at 9:45 am
meghna.bhargav (9/5/2013)
I have an issue where I need to count the cumulative total column wise effectively and could not get a thought of it, I have posted on SQL server central.com but thought if you have an idea on this: I know you have lots to do so this is just when you want to get fresh : or you are more than welcome to avoid it.I have currently dataset abosulate total) and I need the one in second line:
Opco Region Country Jan Feb Mar
Absolute total OP1 EMEAGB 0.3 1.3 0.7
Cumulative total OP1 EMEAgb 0.3 1.6 2.3
Regards
Meghna
There is nowhere near enough information here to provide even a direction. Please try to explain more clearly what you are trying to. Also, please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
September 5, 2013 at 9:53 am
Not a solution, just a clarification, you need the cumulative total adding each month to the previous month like
select opco, region, country, jan as [jan],
jan + feb as [feb],
jan + feb + mar as [mar],
etc...
September 5, 2013 at 9:55 am
Yes you got me right , I need that but in very efficient way.
September 5, 2013 at 10:09 am
If you're not doing any other summation or grouping, I'm not sure you'll find anything more efficient than the straight 'select' in my question. It would only touch the absolute total row once and the cumulative math has to be done somewhere. If there is other summation required, I'd have to know what that was in order to give you a solution.
The more advanced folks here may be able to give you a better solution, so I'll sit back and wait to (once again) learn something.
September 5, 2013 at 10:25 am
meghna.bhargav (9/5/2013)
Yes you got me right , I need that but in very efficient way.
Give us the details to work with and we can help. I am glad David was able to decipher your requirements but without the details there is little we can do here.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
September 6, 2013 at 1:23 am
Ok so I have read the article and also did try to explain which can help me to get the solution:
I have a table :
CREATE table Statistics
(
Status Varchar(10),
Opco Varchar(30),
Region Varchar(30),
Country Varchar(30)
Jan Decimal(3,2),
Feb Decimal(3,2),
Mar Decimal(3,2),
…….
…….
…….
)
Inserting data into the table:
Insert into Statistics
(
‘Absolute total’,
‘OPCO1’,
‘EMEA’,
‘GB’,
‘0.3’,
‘1.3’,
‘0.7’
)
Out put I require is the second row calculating the cumulative totals as shown below:
Absolute total OPCO1 EMEAGB0.3 1.3 0.7
Cumulative total OPCO1 EMEAGB0.3 1.6 2.3
September 6, 2013 at 8:13 am
You might have read the article but you missed the point. The idea is to provide tables and data in a consumable format. That means that the volunteers around here trying to help should be able to hit f5 and have a table populated with data. That way we can spend our time working on your issue instead of setting up the problem.
Here is an example based on what you posted.
if OBJECT_ID('tempdb..#Statistics') is not null
drop table #Statistics
CREATE table #Statistics
(
Status Varchar(30),
Opco Varchar(30),
Region Varchar(30),
Country Varchar(30),
Jan Decimal(3,2),
Feb Decimal(3,2),
Mar Decimal(3,2)
)
Insert #Statistics
select
'Absolute total',
'OPCO1',
'EMEA',
'GB',
0.3,
1.3,
0.7
However, I have to agree with David. I don't think you are going to find much of a more efficient method of this than what he posted already. I can come up with some really convoluted ways of doing this but honestly what he posted is the simplest.
_______________________________________________________________
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/
September 6, 2013 at 8:17 am
I do understand what you mean! But I am in middle of Live issue thinking if i post here, I can get solution.
When you start typing the issue in the message box, it does not take table format nor space formatting as it is in word or outlook.
Anyways, Thanks for taking time on this.
Meghna
September 6, 2013 at 10:50 am
meghna.bhargav (9/6/2013)
I do understand what you mean! But I am in middle of Live issue thinking if i post here, I can get solution.When you start typing the issue in the message box, it does not take table format nor space formatting as it is in word or outlook.
Anyways, Thanks for taking time on this.
Meghna
If you use the IFCode shortcuts (over on the left when posting) it will keep your code cleanly formatted. You should be typing your code in SSMS instead of Word. You can't test your script in there. 😛
I understand you are dealing with a production issue. This is all the more reason to make it easy for people. You will get a LOT more people willing to help when your post is easy to use.
I apologize if you think I am being a pain, I am trying to help you understand the best way to get the assistance that you need.
Are you good now with your issue or do still need some help?
_______________________________________________________________
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/
September 6, 2013 at 10:55 am
Thanks Sean. and I agree to what you said as its important to make people understand what i am trying to communicate.
With the issue, I think i have used what David have suggested directly in the SP but have not yet tested in Live so not sure how will it behave in terms of performance.
Have a good weekend.
Next time when I post issue or suggestion or anything, will definitely take this into account.
Cheers
Meghna
September 6, 2013 at 12:07 pm
meghna.bhargav (9/6/2013)
Thanks Sean. and I agree to what you said as its important to make people understand what i am trying to communicate.With the issue, I think i have used what David have suggested directly in the SP but have not yet tested in Live so not sure how will it behave in terms of performance.
Have a good weekend.
Next time when I post issue or suggestion or anything, will definitely take this into account.
Cheers
Meghna
You are quite welcome. The query could get kind a bit lengthy but the performance shouldn't be an issue. If you have a chance, post and back and let us know how it goes when you do get it live. It is nice to hear how our suggestions work on other systems.
_______________________________________________________________
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/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply