Home SQL server View - loop help (while loop)

# SQL server View - loop help (while loop)

Dolphin
1#
Dolphin Published in 2018-01-10 21:32:47Z
 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 
Alan Burstein
2#
Alan Burstein Reply to 2018-01-12 03:52:10Z
 First, the default value for a case statement is NULL so, "ELSE NULL" is not necessary. Second, this formula is probably not doing exactly what you think: floor(datediff(dd, date1,date2)/365.25). What I mean is, if you are operating under the assumption that there is a leap year every four years, you are incorrect. Leap years don't happen on years divisible by 100 UNLESS it can also be divided by 400. I could be misunderstanding what you're trying to do however. You're correct, You can't use dynamic SQL (e.g. EXEC '') in a view. There's very little you can't do with a tally table. Here's how you'd generate your code programatically using a tally table (I'm going to 1 to 100): with yourExpression(ex) as ( select ',sum(case when product = ''''a a'''' and floor(datediff(dd, date1,date2)/365.25)< then amount_received else null end) as total_a_a_yr' ), iTally(N) as ( select top(100) cast(row_number() over (order by (select null)) as varchar(3)) from sys.all_columns ) select stuff(ex,78,0,'('+N+')')+N from iTally cross join yourExpression;  You can copy/paste and execute the query as is; it will return: ,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).... -- ... as total_a_a_yr100 
 You need to login account before you can post.
Processed in 0.305075 second(s) , Gzip On .