SQL Server Performance Forum – Threads Archive
A Complicated queries
Hi GuysThanks in advance.
I have a view return 3 columns date amount series
1/1/2005 20 A
1/1/2005 20 B
1/15/2005 30 B
1/16/2005 40 A
1/30/2005 20 A
etc In my report I need to return those number look just like the view the easy part but i also need to output 1 more colum total for 30 days ahead report
date amount series 30days ahead total for each series
1/1/2005 20 A 80 <—30 days sum of series A
1/1/2005 20 B 50 <—30 days ahead sum series B
1/15/2005 30 B 30 <—30 could be more butaccord for just this example
1/16/2005 40 A 60
1/30/2005 20 A 20
etc
May the best cheaters win
Not sure, but try this select t1.date, t1.amount, t1.series, sum(t2.amount)
from MainTable t1
Join MainTable t2 on t1.series = t2.series and datediff(dd,t1.date,t2.date) <=30 and t1.date < t2.date
group by t1.date, t1.amount, t1.series So the table would join to itself on series, for any date within 30 days of the one being reported. I think.
Excellent it gives me the correct result. Thanks Another question
is it possible to do a 90 days column as well by creating another table ? May the best cheaters win
Not sure what you mean, but if you’re talking query then I think so (added left join since you may not have figures for those time frames)
select t1.date, t1.amount, t1.series, sum(t2.amount) as Days30, sum(t3.amount) as Days90
from MainTable t1
left Join MainTable t2 on t1.series = t2.series and datediff(dd,t1.date,t2.date) <=30 and t1.date < t2.date
left Join MainTable t3 on t1.series = t3.series and datediff(dd,t1.date,t32.date) <=90 and t1.date < t32.date
group by t1.date, t1.amount, t1.series
Thanks alot I got it working yes that is what I want the queries. May the best cheaters win
]]>