Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


updated Aggregate column


updated Aggregate column

Author
Message
jeandlauro
jeandlauro
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 80
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

a   1   3
b   1   4
a   1   4
a   1   3
a   2   4
a   2   7
b   2   5
b   2   7

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

a   1   10
b   1   4
a   2   21
b   2   16

thanks for your help
gbritton1
gbritton1
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 840
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jeandlauro
jeandlauro
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 80
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jeandlauro
jeandlauro
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 80
no I don't seriously have a column called date. This was only an example.
jeandlauro
jeandlauro
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 80
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
jeandlauro (4/29/2014)
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.


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/

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jeandlauro
jeandlauro
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 80
I found a solution in Microstrategy. Thanks though.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search