Home SQL server View - loop help (while loop)
 I've been asked to produce a dataset using a view in SQL and have a number of products (eg 'a a','b b','c c') and I want to work out totals over 5 years (1,2,3,4,5) and output the totals as total_a_a_yr1, total_b_b_yr1....(see the below code) Rather than writing out loads of lines of code is there a more efficient way of coding? I thought about creating a procedure but I don't think you can use an EXEC within a view. I may be wrong. A while loop might be the way to go but I'm unsure about using declares in views. Any help would be appreciated. Thanks ,sum(case when product = 'a a' and floor(datediff(dd, date1,date2)/365.25)<1 then amount_received else null end) as total_a_a_yr1 ,sum(case when product = 'a a' and floor(datediff(dd, date1,date2)/365.25)<(2) then amount_received else null end) as total_a_a_yr2 ,sum(case when product = 'a a' and floor(datediff(dd, date1,date2)/365.25)<(3) then amount_received else null end) as total_a_a_yr3 ,sum(case when product = 'a a' and floor(datediff(dd, date1,date2)/365.25)<(4) then amount_received else null end) as total_a_a_yr4 ,sum(case when product = 'a a' and floor(datediff(dd, date1,date2/365.25)<(5) then amount_received else null end) as total_a_a_yr5