Post reply

Need help on creating a view

  • December 22, 2013 at 7:46 am

    #283918

    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

    #1675452

    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

    #1675454

    Have you got your Select statement all ready to go?

  • December 22, 2013 at 12:30 pm

    #1675463

    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

    #1675466

    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

    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)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

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