Advertisement
Guest User

Untitled

a guest
Jul 17th, 2019
60
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.77 KB | None | 0 0
  1. SELECT
  2. [Close Date],
  3. [Prior 6 Mo Date],
  4. [Opportunity Id],
  5. [Account Name],
  6. [Account Number],
  7. [Total Opportunity Amount],
  8. AVG([Total Opportunity Amount]) OVER (
  9. PARTITION BY [Account Number]
  10. ORDER BY [Close Date]
  11. RANGE BETWEEN [Prior 6 Mo Date] and [Close Date] --also tried BETWEEN
  12. unbounded preceding and current row
  13. )
  14. FROM #opp_level
  15. ORDER BY
  16. [Close Date],
  17. [Prior 6 Mo Date],
  18. [Opportunity Id],
  19. [Account Name],
  20. [Account Number],
  21. [Total Opportunity Amount]
  22.  
  23. select a.[Close Date],a.[Total Opportunity Amount], avg(b.[Total
  24. Opportunity Amount]) AS [Prior 6 Mo Avg]
  25. from #opp_level a
  26. join #opp_level b
  27. on b.[Close Date]
  28. between dateadd(MONTH, -6, a.[Close Date]) and a.[Close Date]
  29. group by a.[Close Date], a.[Total Opportunity Amount]
  30. ORDER BY a.[Close Date]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement