Multiple running total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple running total

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2011-09-17 : 00:09:19

Hi,

anyhow to optimize this?

create table product(record_date date, product_id int, amount int, amount2 int)
insert into product
select
'2011-01-01',1,10,10 union all select
'2011-01-02',1,11,21 union all select
'2011-01-03',1,12,43 union all select
'2011-03-01',1,13,56 union all select
'2011-04-01',1,14,70;


SELECT
	ori.Record_Dt
	ori.Product_ID
	ori.amount
	qtd.amount
	ytd.amount
	mag.amount2/day(getdate())
	ytd.amount2/datepart(dayofyear, getdate())
from product ori
cross apply
(
	select
		sum(amount)amount
	,	sum(amount2)amount2
	from product qtd
	where ori.product_id = qtd.product_id
	and datediff(quarter, 0, ori.record_dt) = datediff(quarter, 0, qtd.record_dt)
	and ori.record_dt >= qtd.record_dt
)qtd
cross apply
(
	select
		sum(amount)amount
	from product ytd
	where ori.product_id = ytd.product_id
	and datediff(year, 0, ori.record_dt) = datediff(year, 0, ytd.record_dt)
	and ori.record_dt >= ytd.record_dt
)ytd
cross apply
(
	select
		sum(amount2)amount2
	from product mtd
	where ori.product_id = mtd.product_id
	and datediff(year, 0, ori.record_dt) = datediff(year, 0, mtd.record_dt)
	and ori.record_dt >= ytd.record_dt
)mtd

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-09-17 : 00:33:20

Is your code working?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-17 : 00:33:42

[code]
SELECT
ori.Record_Dt
ori.Product_ID
ori.amount
agg.amount
agg.yramount
agg.yramount2/day(getdate())
agg.amount2/datepart(dayofyear, getdate())
from product ori
cross apply
(
select
sum(case when datediff(quarter, 0,record_dt) = datediff(quarter, 0, ori.record_dt) and ori.record_dt >= record_dt then amount else 0 end)amount
, sum(case when datediff(quarter, 0,record_dt) = datediff(quarter, 0, ori.record_dt) and ori.record_dt >= record_dt then amount2 else 0 end)amount2,
sum(case when datediff(year, 0,record_dt) = datediff(year, 0, ori.record_dt) and ori.record_dt >= record_dt then amount else 0 end)yramount
, sum(case when datediff(year, 0,record_dt) = datediff(year, 0, ori.record_dt) and ori.record_dt >= record_dt then amount2 else 0 end)yramount2
from product qtd
where ori.product_id = product_id

)agg
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

waterduck
Aged Yak Warrior

982 Posts

Posted - 2011-09-17 : 00:39:23

visakh16,
multiple columns are specified in an aggregated expression containing an oter reference. if an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

i don think we can put main table alias in cross apply columns...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources