December 22, 2013 at 7:46 am
Happy holiday!
I have a question on creating a view:
There is a table called Document and there is a field called NextReviewDate, and off course other fields.
I need a view to show the total number of records (that meet certain criteria) vs. total number of records that has NextReviewDate < getdate()
It's easy to do this in stored procedure but I am not sure if this can be done in a view.
Thanks.
December 22, 2013 at 8:08 am
Well, not sure where to start since there is nothing from which to work, no DDL, sample data, expected results, or what you have tried so far to solve your problem.
December 22, 2013 at 8:11 am
Have you got your Select statement all ready to go?
December 22, 2013 at 12:30 pm
Thanks for replies, I actually found a way to use stored procedure which is much easier for me, especially on this case.
December 22, 2013 at 1:41 pm
halifaxdal (12/22/2013)
Thanks for replies, I actually found a way to use stored procedure which is much easier for me, especially on this case.
To be honest, I wouldn't use either a View or a Stored procedure for this. Instead, I'd use an iTVF (, Inline Table Valued Function, if I could get it down to a single query) of an mTVF (Multi-statment Table Valued Function, if I couldn't get it down to a single query).
The advantage over a View is that you can pass parameters to the function that can really help with performance. Think of it as a "Parameterized View".
The advantage over a Stored Procedure is that you can much more easily use the output for other things than you can from a Stored Procedure. The output from the function can easily be made available to other SELECTs (etc) and can even be joined to simply by including the function in the FROM clause of another statement (SELECT, INSERT, UPDATE, DELETE, MERGE).
I'll also suggest that you've been around here long enough to know that you'll get much better answers if you provide some readily consumable DDL and test data. If you don't actually know how to easily do that, then please spend some time studying the article at the first link in my signature line below under "Helpful Links". A little time spent up-front by you may mean the difference between you waiting minutes for a good answer or, as has happened here, never getting an answer at all.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply