Require help with creating a view with YTD balances and period

Question

Require help with creating a view with YTD balances and period

SQL_beginner1

Right there with Babe

Points: 796
More actions
October 6, 2017 at 10:08 am

#323924

Hi,
I have to implement a view that has account number, year, period (month), balance, and Year-end balance. We already have a functional view with account number, year, period (month) and balance (view_1). The new column "Year-end balance" will have the year-end balance from period 12, and previous year for all the account numbers. The result should look something like this:
Account_number Year Period Balance Year-end_Balance
1234 2016 12 $120
1234 2017 1 $99 $120
1234 2017 2 $59 $120
1234 2017 3 $63 $120
1234 2017 4 $23 $120
1234 2017 5 $41 $120
1234 2017 6 $10 $120
Existing view:
Account_number Year Period Balance
1234 2016 12 $120
1234 2017 1 $99
1234 2017 2 $59
1234 2017 3 $63
1234 2017 4 $23
1234 2017 5 $41
1234 2017 6 $10
What I've done is created another view (view_2) with only year-end balances (period = 12) for all accounts, and then tried to create a new view (view_3) by joining the two views (left join) on account number, and getting the year-end balance in the new column. BTW, this is a simplified description as the number of columns is more - we have entity, currency, business unit, and a few more columns in view_1.
When I tried to run the script for the view_3, it kept running for 30 minutes. Which leads me to suspect that the join is not efficient.
Would appreciate some help with this - maybe a different approach? We cannot save the data in a table, as the view references tables in a different server/database via linked server, and the underlying data changes, which the view has to reflect in real-time.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Without any details for the tables involved, it's hard to tell, but given that your views select from a Linked Server, that could well be the problem, as any table selected from a linked server has the entire table brought across the network before the WHERE clause is applied. You might be better of using OPENQUERY against that linked server, and join tables from the linked server in a query against that server, where the only data that will traverse the network will be what gets past the WHERE clause. Without your queries, view definitions, table create statements, and sample data with expected results, it's going to be difficult to be of much help.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

SQL_beginner1 Right there with Babe Points: 796 More actions · Answer 2

Steve,

View_1 and View_2 are working fine, and give results within seconds, so I am not sure that the linked server is the issue here. it's the third view with the Year-end_Balance included, which joins the two views, that is taking a long time (I canceled it after 35 minutes).

Perhaps the answer to the first part - how to include year-end-balance - would be helpful. Maybe a different approach than the one I tried.

Thanks,
Amit

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

SQL_beginner1 - Friday, October 6, 2017 11:32 AM
Steve,
View_1 and View_2 are working fine, and give results within seconds, so I am not sure that the linked server is the issue here. it's the third view with the Year-end_Balance included, which joins the two views, that is taking a long time (I canceled it after 35 minutes).
Perhaps the answer to the first part - how to include year-end-balance - would be helpful. Maybe a different approach than the one I tried.
Thanks,
Amit

Without details, all we can do is take shots in the dark.
You say that the first two views work fine, that's great. Adding a third view that joins those two together could very well be the problem as it may result in the other two views becoming inefficient. When querying data across a linked server you need to remember that any joins needed between tables will occur on the local server unless you have written the views to use pass through queries, or you are querying views on the remote server. This means that all the data has to be pulled from the remote server before any joins and filtering occur.
This can cause an issue even if you are pulling data from a single table. I helped fix a process that was doing just that over a linked server.

sgmunson SSC Guru Points: 110640 More actions · Answer 4

SQL_beginner1 - Friday, October 6, 2017 11:32 AM
Steve,
View_1 and View_2 are working fine, and give results within seconds, so I am not sure that the linked server is the issue here. it's the third view with the Year-end_Balance included, which joins the two views, that is taking a long time (I canceled it after 35 minutes).
Perhaps the answer to the first part - how to include year-end-balance - would be helpful. Maybe a different approach than the one I tried.
Thanks,
Amit

Then maybe you could just select those two views into temp tables and run a query joining the temp tables. Unfortunately, that would not be possible within a view, and could only be done using a multi-statement table valued function. However, it might be a lot better than your current alternative.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

SQL_beginner1 Right there with Babe Points: 796 More actions · Answer 5

Lynn and Steve,

Thanks for your replies. I made it work by moving the view2-related filters from the WHERE clause to the JOIN..ON section, and that solved the issue.

Cheers,
Amit

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 6

SQL_beginner1 - Friday, October 13, 2017 9:25 AM
Lynn and Steve,
Thanks for your replies. I made it work by moving the view2-related filters from the WHERE clause to the JOIN..ON section, and that solved the issue.
Cheers,
Amit

Unless you're using OUTER joins, that probably shouldn't have made a difference.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)