SQL Server Performance

A Complicated queries

Discussion in 'T-SQL Performance Tuning for Developers' started by tdong, May 25, 2005.

  1. tdong New Member

    Hi Guys
    Thanks 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
  2. ChrisFretwell New Member

    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.
  3. tdong New Member

    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
  4. ChrisFretwell New Member

    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
  5. tdong New Member

    Thanks alot I got it working yes that is what I want the queries.

    May the best cheaters win

Share This Page