April 29, 2014 at 8:39 am
I am trying to create an aggregate table where the value is a rolling sum. Type a on date 1 is the sum of the values in the main tbl. Type a on date 2 is the sum of values for type a on date 1 and date 2. Is this possible? I have been trying update t sql with sum(case where date <= date) statements but can't get it to run.
create table main_table (type nvarchar(10), date int, datavalues int);
insert into main_table
values('a', '1',3);
insert into main_table
values('b', '1',4)
insert into main_table
values('a', '1',4)
insert into main_table
values('a', '1',3)
insert into main_table
values('a', '2',4)
insert into main_table
values('a', '2',7)
insert into main_table
values('b', '2',5)
insert into main_table
values('b', '2',7)
select * from main_table
a13
b14
a14
a13
a24
a27
b25
b27
create table aggregate_table (type nvarchar(10), date int, datavalues int);
insert into aggregate_table
values('a', '1',10)
insert into aggregate_table
values('b', '1',4)
insert into aggregate_table
values('a', '2',21)
insert into aggregate_table
values('b', '2',16)
select * from aggregate_table
a110
b14
a221
b216
thanks for your help
April 29, 2014 at 8:42 am
Please post some sample data in the form of DDL (CREATE TABLE ...) and DML (INSERT INTO ...) along with the query you have so far and the output you wish to see.
April 29, 2014 at 9:34 am
Don't do this. You are going to fight this battle until this is replaced with another approach. You will never keep your aggregate table accurately in synch with the other table. Even using triggers it will happen at some point that somebody disables the trigger. If you want aggregate data you should calculate it on the fly or if you need it to be persistent because the calculations take some time look at using a computed column.
_______________________________________________________________
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/
April 29, 2014 at 10:17 am
I am looking to create a computed column for on the fly reporting. The query above only serves to show the output of what is needed, not how I will do it.
I was hoping to get help how to calculate the field.
Thanks
April 29, 2014 at 12:14 pm
jeandlauro (4/29/2014)
I am looking to create a computed column for on the fly reporting. The query above only serves to show the output of what is needed, not how I will do it.I was hoping to get help how to calculate the field.
Thanks
A computed column is just that, a column. What you posted is several columns. Also, I don't understand how you come up with those values. It sort of looks like a sum of datavalues when date (do you seriously have a column named date with a datatype of int???) = 1 it is ok but when it is 2 the values in your output don't make sense to me at all.
_______________________________________________________________
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/
April 29, 2014 at 1:35 pm
no I don't seriously have a column called date. This was only an example.
April 29, 2014 at 1:40 pm
date 2 is the rolling sum of date 2 and date 1 by type
rolling(date 1, type a) = (date 1, type a)
rolling(date 2, type a) = (date 1, type a) + (date 2, type a)
i need a look up table with a rolling sum column.
April 29, 2014 at 1:49 pm
jeandlauro (4/29/2014)
date 2 is the rolling sum of date 2 and date 1 by typerolling(date 1, type a) = (date 1, type a)
rolling(date 2, type a) = (date 1, type a) + (date 2, type a)
i need a look up table with a rolling sum column.
You are going to have to explain more clearly what you want here. Are you trying to do a running total here? You don't have any data in this table to use as a sort order.
This article explains one way to achieve a running total. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
I will be happy to help but you need to help me understand your output and the business rules a bit.
_______________________________________________________________
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/
April 30, 2014 at 6:35 am
I found a solution in Microstrategy. Thanks though.
April 30, 2014 at 7:16 am
jeandlauro (4/30/2014)
I found a solution in Microstrategy. Thanks though.
Glad you found something that works. Can you share your solution so that maybe it will others who stumble across this thread in the future.
_______________________________________________________________
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/
April 30, 2014 at 7:44 am
Microstrategy has OLAP functions for running sum, running count, running average.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply