Write a Stored Procedure for the two statement below using the information in th

Question

Write a Stored Procedure for the two statement below using the information in the VISIO diagram

1. Snapshot Report: The license applications that have supporting documents which have been reviewed and approved but are currently pending final approval.

2. Report with a Tabular Sub-Report: Currently open application details with a tabular sub report for each application with documents received. Application details include qualifications and qualification status.

Profe ssional CE Provider FEIN. ProviderFirstName FirstName Provider astName ast Name bmits Gender Starte Date Address MainPhone Number Application Enrollment qualifies Application ID Enrollm Enrollm ent StatusID license TypeID Code Renew License ID umber SubmissionDate SectionID has Decision Date Term Qualification has SSN cationID o Application License Type QualificationTypeID has icense Name Section equines requires SectionlD nstructorID Degree Lconana has icense ProviderID CE Course ID allif ion' QualificationTypeID Degree Type License Type License SectionNumber SSN Term School issue Date License TypeID Room Numbe Gradua Expiration Date ion Date Decision Date Approved BylD ExpirationDate Application ID receives Document ID Document Title Submission Date QualificationType ReviewBylD Applicant FirstName ific ionT eID ApplicantLastName QualificationName Document Description Received From Received Date QualificationID Revie WID ApprovalDate Document Status DenialDate ocument Status Received Date DMQAStaff NotReviewedD Approved Date Staff ID Disapproved Date StaffFirst Name dete StaffID Staff astName DepartmentID License RenewallD Date Position ID LoginID InstructorEmployment FEIN Employment Division Instructor struct orlD SSN uctorStart Date CE Course FEIN QualificationTypeID Course Number Course Name Course Hou has

Explanation / Answer

Answer 1)

GO
   -- =============================================
   -- Author:       <Author,,>
   -- Create date: <Create Date,,>
   -- Description:   <Description,,"This SP simply retrive The license applications that have supporting documents which have been
   --                              reviewed and approved but are currently pending final approval">
   -- exec [USP_GetSnapshot_Report]
   -- =============================================
CREATE PROCEDURE [dbo].[USP_GetSnapshot_Report]
          
AS
BEGIN
--retrieve licenece applications
    ---- that have supporting document
       ---- supporting document have been reviewed but currently pending final approval.

       DECLARE @LicenseApps   TABLE(
              ApplicationID INT NOT NULL,
              LicenseID INT NOT NULL,
              LicenseType NVARCHAR(256) NOT NULL,
              StaffID INT NOT NULL)
       --- Retrieve the license applications with the staffID who has assigned on it.
       INSERT INTO @LicenseApps
       (
             ApplicationID,
              LicenseID,
              LicenseType,
              StaffID
       )
         Select
         App.ApplicationID As ApplicationID,
       L.LicenseID As LicenseID,
       L.Name As As LicenseType,
       DMAQ.StaffID As StaffID,
       From Application App
       INNER JOIN LicenceType L ON App.LicenseTypeID = L.LicenseTypeID
       INNER JOIN DMAQAStaff DMAQ ON L.LicenseID = DMAQ.LicenseID
      
      
       --- Final Retrival Of Application
          Select App.ApplicationID As ApplicationID,
                 From @LicenseApps App
                  INNER JOIN DocumentStatus DS ON App.StaffID = DS.StaffID
                  INNER JOIN DocumentID D ON DS.DocumentStatusID = D.ReviewID
                  Where D.ApprovalDate < GETDATE() AND D.ReviewByID is not NULL AND D.ReceivedDate >= GETDATE()

END

Answer 2)

GO
   -- =============================================
   -- Author:       <Author,,>
   -- Create date: <Create Date,,>
   -- Description:   <Description,,"This SP Simply retrieve Currently open application details with a tabular sub report for each
   ---                            application with documents received. Application details include qualifications and qualification status.">
   -- exec [USP_TabularSubReport]
   -- =============================================
CREATE PROCEDURE [dbo].[USP_TabularSubReport]
          
AS
BEGIN
       DECLARE @OpenApps   TABLE(
              ApplicationID INT NOT NULL,
              QualificationID INT NOT NULL,
              QualificationTypeID INT NOT NULL,
              DocumentID INT NOT NULL,
              LicenseID INT NOT NULL,
              StaffID INT NOT NULL,
              )
          

       --- Retrieve all the currently open applications.
       INSERT INTO @OpenApps
       (
              ApplicationID,
              QualificationID,
              QualificationTypeID,
              DocumentID,
              LicenseID,
              StaffID
       )
         Select
          App.ApplicationID As ApplicationID,
          Q.QualificationID As QualificationID,
          Q.QualificationTypeID As QualificationTypeID,
          D.DocumentID As DocumentID,
          L.LicenseID AS LicenseID,
          DMQA.StaffID As StaffID
       From Application App
       INNER JOIN License L on L.ApplicationID = App.ApplicationID
       INNER JOIN DMQAStaff DMQA ON DMQA.LicenseID = L.LicenseID
       INNER JOIN Qualification Q ON App.ApplicationID = Q.ApplicationID
       INNER JOIN Document D ON D.QualificationID = Q.QualificationID
       INNER JOIN DocumentStatus DS ON DS.StaffID = DMQA.StaffID
       Where DS.ApprovedDate < GETDATE() AND DS.ReceivedDate > GETDATE()
      
      
      
      
       --- Final Retrival Of Application
          Select App.ApplicationID As ApplicationID,
                  QT.QualificationName As QualificationStatus
                 D.DocumentTitle As DocumentTitle              
                 From @OpenApps App
                  INNER JOIN DocumentID D ON App.QualificationID = D.QualificationID
                  INNER JOIN QualificationType QT ON QT.QualificationTypeID = App.QualificationTypeID
                  Group BY App.ApplicationID

END

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.