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

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 '<sql code>') 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)....
--<truncated for brevity> ... as total_a_a_yr100
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO