Need SQL help

Posted on 2008-11-03
Last Modified: 2010-03-19
The following views and stored procedures create the Outstanding High Risk Recommendations Report. This is a report of all High Risk recommendations that are On Hold or In Process or Both (In process and On Hold), sorted by Assigned Division. Can you please look at them and suggest a way of creating this report just using the views (without stored procedure)? The status will be selected ny the user from the application and input in the view.

CREATE view v_for_reports
SELECT       dbo.IA_recommendation.division
      , dbo.IA_report.title AS [Audit Title]
      , dbo.IA_report.finalized_dt AS [Audit Report Date]
      , dbo.IA_report.type AS [Audit Type]
      , dbo.IA_report.is_confidential_ind AS [Confidential Report]
      , CAST(dbo.IA_report.report_yr AS CHAR(4))
               + '-' + CAST(dbo.IA_report.report_nbr AS CHAR(2)) + '-' + CAST(dbo.IA_recommendation.recommendation_nbr AS CHAR(2))
               AS [Recommendation Number]
      , dbo.IA_recommendation.recommendation
      , dbo.IA_recommendation.risk
      , dbo.IA_recommendation.status
      , IA_response_1.expected_resolution_dt AS [Expected Resolution Date]
      , IA_response_1.response
      , dbo.IA_report.committee_dt AS [Audit Committee Date]
      , dbo.IA_recommendation.completion_dt AS [Date Completed]
      , IA_response_1.creation_dt AS [Date Created]
      , dbo.IA_recommendation.recommendation_id
      , dbo.IA_recommendation.repeat_recommendation AS [Repeat recommendation]

FROM        dbo.IA_report
      inner JOIN dbo.IA_recommendation
      ON dbo.IA_report.report_id = dbo.IA_recommendation.report_id
      inner JOIN dbo.IA_response IA_response_1
      ON dbo.IA_recommendation.recommendation_id = IA_response_1.recommendation_id
WHERE (      IA_response_1.creation_dt =
                   (SELECT MAX(IA_response_2.creation_dt)
                    FROM   dbo.IA_response IA_response_2
                    WHERE IA_response_2.recommendation_id = IA_response_1.recommendation_id))


CREATE view v_outstanding_high_risk_recommendation_report
SELECT       dbo.IA_division_ref.division
      , v_for_reports.[Audit Title]
      , v_for_reports.[Audit Report Date]
      , v_for_reports.[Audit Type]
      , v_for_reports.[Confidential Report]
      , v_for_reports.[Recommendation Number]
      , v_for_reports.repeat_recommendation as [Repeat Recommendation]
      , v_for_reports.recommendation, #tmp_v3.risk
      , v_for_reports.status
      , v_for_reports.[Expected Resolution Date]
      , v_for_reports.response, #tmp_v3.[Audit Committee Date]
      , v_for_reports.[Date Completed]
      , v_for_reports.[Date Created]

FROM        dbo.IA_division_ref
      LEFT OUTER JOIN v_for_reports
      ON dbo.IA_division_ref.division = v_for_reports.division



      @status Varchar(30)
IF @status = 'In Process'
          SELECT *
      FROM v_outstanding_high_risk_recommendation_report
      WHERE risk = 'High'
      AND status = 'In Process'
      order by division
ELSE if @status = 'On Hold'
          SELECT *
      FROM v_outstanding_high_risk_recommendation_report
      WHERE risk = 'High'
      AND status = 'On Holds'
      order by division
ELSE if @status = 'Both'
          SELECT *
      FROM v_outstanding_high_risk_recommendation_report
      WHERE risk = 'High'
      AND status in ('In Process', 'On Hold')
      order by division
Question by:nup
    1 Comment
    LVL 15

    Accepted Solution

    You can't pass the paramerter to the View so...check the following...

    CREATE VIEW Vw_rpt_009
              SELECT *
          FROM v_outstanding_high_risk_recommendation_report
          WHERE risk = 'High'
          AND status in ('In Process', 'On Hold')
          order by division
    select * from Vw_rpt_009 where status = 'In Process'
    select * from Vw_rpt_009 where status = 'On Hold'
    select * from Vw_rpt_009 where status  in ('In Process', 'On Hold')

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now