Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

updated Aggregate column Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 8:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 9:10 AM
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
Post #1565970
Posted Tuesday, April 29, 2014 8:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
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.
Post #1565974
Posted Tuesday, April 29, 2014 9:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 13,455, Visits: 12,318
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)
Post #1566013
Posted Tuesday, April 29, 2014 10:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 9:10 AM
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

Post #1566041
Posted Tuesday, April 29, 2014 12:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 13,455, Visits: 12,318
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)
Post #1566088
Posted Tuesday, April 29, 2014 1:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 9:10 AM
Points: 33, Visits: 80
no I don't seriously have a column called date. This was only an example.

Post #1566111
Posted Tuesday, April 29, 2014 1:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 9:10 AM
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.
Post #1566114
Posted Tuesday, April 29, 2014 1:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 13,455, Visits: 12,318
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)
Post #1566117
Posted Wednesday, April 30, 2014 6:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 9:10 AM
Points: 33, Visits: 80
I found a solution in Microstrategy. Thanks though.
Post #1566353
Posted Wednesday, April 30, 2014 7:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 13,455, Visits: 12,318
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)
Post #1566380
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse