Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- [Close Date],
- [Prior 6 Mo Date],
- [Opportunity Id],
- [Account Name],
- [Account Number],
- [Total Opportunity Amount],
- AVG([Total Opportunity Amount]) OVER (
- PARTITION BY [Account Number]
- ORDER BY [Close Date]
- RANGE BETWEEN [Prior 6 Mo Date] and [Close Date] --also tried BETWEEN
- unbounded preceding and current row
- )
- FROM #opp_level
- ORDER BY
- [Close Date],
- [Prior 6 Mo Date],
- [Opportunity Id],
- [Account Name],
- [Account Number],
- [Total Opportunity Amount]
- select a.[Close Date],a.[Total Opportunity Amount], avg(b.[Total
- Opportunity Amount]) AS [Prior 6 Mo Avg]
- from #opp_level a
- join #opp_level b
- on b.[Close Date]
- between dateadd(MONTH, -6, a.[Close Date]) and a.[Close Date]
- group by a.[Close Date], a.[Total Opportunity Amount]
- ORDER BY a.[Close Date]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement