Home SQL server View - loop help (while loop)
Reply: 0

SQL server View - loop help (while loop)

user1430
1#
user1430 Published in July 21, 2018, 9:45 am

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.304321 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO