[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need SQL help

Posted on 2008-11-03
1
Medium Priority
?
168 Views
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
as
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))

GO

CREATE view v_outstanding_high_risk_recommendation_report
as
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

GO

CREATE PROCEDURE sp_rpt_009

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

Accepted Solution

by:
MohammedU earned 500 total points
ID: 22870917
You can't pass the paramerter to the View so...check the following...

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

830 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