Solved

SQL Server 2008 Query Issue - How to select just one row

Posted on 2016-08-04
4
44 Views
Last Modified: 2016-08-04
I am trying to bring back just one row in my resultset with a MAX(Permit.CreatedOn).

How can I fix my SP to bring one instead of 3. because every column value is going to be same.

Picture
USE [Fund]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_rpt_GeneralProjectReport]
    @IsLoan bit,
    @ProjectNumber  varchar(20),		
   @Success bit output,
  @Message varchar(1000) output
	 
AS
BEGIN
	BEGIN TRY     
			SELECT 
			      -- Report Top Part
				  Project.ProjectNumber,
			      Project.ApplicantName,
			      Project.SpecificLocation,	
			      ProjectAssignmentInformation.[Description],
			       
			      --Inside the Tabular column
			      ProjectType.[Type],
			      District.DistrictOffice,
			      Division.Name As DistrictName,
			      ProjectStatus.[Status],
			      Project.RevolvingLoanUnit,
			       
			      RiverBasin.Name As RiverBasinName,
			      Permit.PermitNumber,
			      Permit.CreatedOn As NPDESIssueDate,			      
			      Employee.FirstName + ' ' + Employee.LastName AS TSUReviewer,			      
				  ProjectAssignmentInformation.PlanReceiptDate,
				  
				  ProjectAssignmentInformation.EmployerIdentificationNumber,	  
				  DBO.ufn_GeneralProjectReport_GetCountyName(ProjectAssignmentInformation.CountyId) AS MainCounty,
				  DBO.ufn_GeneralProjectReport_GetCountyName(ProjectAssignmentInformation.SecondCountyId) AS SecondCounty,
				  DBO.ufn_GeneralProjectReport_GetCountyName(ProjectAssignmentInformation.ThirdCountyId) AS ThirdCounty,	
			      dbo.ufn_OOAPreAwardStatus_Milestones(@ProjectNumber,'EA/FNSI Public Notice Clearance') AS FactSheetIssued,
			      
				  ProjectPriorityAssignmentSRFSWQIFNPS.ComplianceDate As ComplianceDate,
			      COALESCE(ProjectPriorityAssignmentSRFSWQIFNPS.PPLYear, ProjectPriorityAssignmentDWRF.PPLYear) AS PPLYear,
				  COALESCE(ProjectPriorityAssignmentSRFSWQIFNPS.ContingentOrFuture, ProjectPriorityAssignmentDWRF.ContingentOrFuture) AS ContingentOrFuture,							      ProjectLoanApplication.TargetedIODate AS OOATargetDate,
				  ProjectPriorityListOrderOfApprovalDate.ProjectPriorityListDate  AS OOAActualDate,	
				  		
				  LoanInterestRate.InterestRate,
				  ProjectOrderOfApprovalInformation.RepaymentStartDate,
				  ProjectOrderOfApprovalInformation.RepaymentEndDate,
				  ProjectDisbursementInformation.BankAccountNumber,
				  ProjectDisbursementInformation.BankABANumber,
				  
				  ProjectLoanApplication.AuthorizedRepResolutionDate,
				  ProjectLoanApplication.TargetedConstructionStartDate,
				  ProjectLoanApplication.TargetedConstructionCompletionDate,
				  ProjectLoanApplication.AuthorizingStatuteId,
				  ProjectLoanApplication.BondCeilingAmount,
				  
				  ProjectLoanApplication.TargetedIODate AS IOTargetDate,
				  ProjectCompletionInformation.InitiationOfOperationsActualDate AS IOActualDate, 
				  ----FirmContact.FirstName +' '+ FirmContact.LastName AS 'Engineer Contact',
				  ----FirmContact.FirstName +' '+ FirmContact.LastName AS 'Bond Counsel Contact',
				  ----FirmContact.FirstName +' '+ FirmContact.LastName AS 'Financial Advisor Contact',
				   
				  ----FirmContact.FirstName +' '+ FirmContact.LastName AS 'Accountant Contact',
				  ----FirmContact.FirstName +' '+ FirmContact.LastName AS 'Authorized Rep Contact',
				  ----FirmContact.FirstName +' '+ FirmContact.LastName AS 'Attorney Contact',
				  ----FirmContact.FirstName +' '+ FirmContact.LastName AS 'Miscellaneous Contact',
				  ----FirmContact.FirstName +' '+ FirmContact.LastName AS 'District Engineer Contact',
				  
				  ProjectLoanApplication.Date47004CivilRightsComplianceFormSignedByApplicant,
				   CASE
					  WHEN DATALENGTH(ProjectLoanApplication.CapitalizedInterest) > 0 
							Then 'Yes' 
							Else 'No' 
				   END CapitalizedInterest,	
				  ProjectLoanApplication.DatePartISentToMFA,
				  ProjectLoanApplication.DatePartIMFAApprovalMemo,
				  ProjectLoanApplication.DatePartIReceived,	
				  
				  ProjectLoanApplication.DatePartIIReceived,
				  ProjectLoanApplication.DatePartIIIReceived,
				  CASE
					  WHEN DATALENGTH(ProjectLoanApplication.AllDebarmentFormsSubmitted) > 0 
							Then 'Yes' 
							Else 'No' 
				   END AllDebarmentFormsSubmitted,
				  ProjectCategoryCostInformation.PPLCategorySumAmount, 
				  ProjectCategoryCostInformation.OOACategorySumAmount,  
				  
				  ProjectCategoryCostInformation.FinalCategorySumAmount,
				  COALESCE(ProjectPriorityAssignmentSRFSWQIFNPS.CurrentPopulationServed, ProjectPriorityAssignmentDWRF.CurrentPopulationServed) AS Population,
				  COALESCE(ProjectPriorityAssignmentSRFSWQIFNPS.TotalPoints, ProjectPriorityAssignmentDWRF.TotalPoints) AS TotalPoints,				 
				  CASE
					  WHEN DATALENGTH(ProjectPriorityAssignmentSRFSWQIFNPS.Grandfathered) > 0 
							Then 'Yes' 
							Else 'No' 
				  END Grandfathered,
				  ProjectOrderOfApprovalInformation.LoanAmount,
				  
				  ProjectCompletionInformation.MDEQAdministrativelyCompleteLetterDate,
				  ProjectCompletionInformation.TreasuryLoanAdjustmentLetterDate,
				  ProjectCompletionInformation.FinalLineItemAmount,
				  NULL	--This one will be removed at the end before shelving			
				   
			  FROM Project	
				   INNER JOIN ProjectType
				           ON (ProjectType.ProjectTypeId = Project.ProjectTypeId AND (ProjectType.IsLoan = @IsLoan))	
				   INNER JOIN ProjectStatus
						   ON Project.ProjectStatusId = ProjectStatus.ProjectStatusId 
				   INNER JOIN ProjectAssignmentInformation	   
				 	       ON Project.ProjectId = ProjectAssignmentInformation.ProjectId  
				   INNER JOIN District
				           ON District.DistrictId = Project.DistrictId 
				   INNER JOIN Division
				           ON Division.DivisionId = Project.DivisionId
				   INNER JOIN RiverBasin
				           ON RiverBasin.RiverBasinId = ProjectAssignmentInformation.RiverBasinId	
				    LEFT JOIN ProjectPermit
				           ON ProjectPermit.ProjectId = Project.ProjectId
				    LEFT JOIN Permit
				           ON Permit.PermitId = ProjectPermit.PermitId
				   INNER JOIN Employee
				           ON Employee.EmployeeId = ProjectAssignmentInformation.RateReviewerEmployeeId
				    LEFT JOIN ProjectPriorityAssignmentDWRF    
                           ON Project.ProjectId = ProjectPriorityAssignmentDWRF.ProjectId
                    LEFT JOIN ProjectPriorityAssignmentSRFSWQIFNPS   
                           ON Project.ProjectId = ProjectPriorityAssignmentSRFSWQIFNPS.ProjectId    	 	 
				    LEFT JOIN ProjectLoanApplication	   
			               ON Project.ProjectId = ProjectLoanApplication.ProjectId 
			       INNER JOIN ProjectPriorityListOrderOfApprovalDate	
					       ON ProjectPriorityListOrderOfApprovalDate.ProjectPriorityListOrderOfApprovalDateId
						   =  CASE
									WHEN Project.ProjectTypeId= 5 THEN ProjectPriorityAssignmentDWRF.ProjectPriorityListOrderOfApprovalDateId
									WHEN Project.ProjectTypeId IN ( 3,4) THEN ProjectPriorityAssignmentSRFSWQIFNPS.ProjectPriorityListOrderOfApprovalDateId																							
							   END 
			        LEFT JOIN ProjectOrderOfApprovalInformation
			               ON Project.ProjectId = ProjectOrderOfApprovalInformation.ProjectId 
			        LEFT JOIN LoanInterestRate
			               ON LoanInterestRate.LoanInterestRateId = ProjectOrderOfApprovalInformation.LoanInterestRateId 
			        LEFT JOIN ProjectDisbursementInformation
			               ON ProjectDisbursementInformation.ProjectId = Project.ProjectId
			        LEFT JOIN ProjectCompletionInformation
			               ON ProjectCompletionInformation.ProjectId = Project.ProjectId
	                LEFT JOIN ProjectCategoryCostInformation       
                           ON ProjectCategoryCostInformation.ProjectId = Project.ProjectId 
			          
			    --   INNER JOIN ProjectFirmContact
					  --     ON Project.ProjectId = ProjectFirmContact.ProjectId
				   --INNER JOIN FirmContact 
					  --     ON ProjectFirmContact.FirmContactId = FirmContact.FirmContactId 
				   --INNER JOIN Firm 
					  --     ON FirmContact.FirmId = Firm.FirmId
				   --INNER JOIN ContactType
				   --        ON Firm.ContactTypeId = ContactType.ContactTypeId  
			    -- 
			  
		     WHERE Project.ProjectNumber=@ProjectNumber --AND MAX(Permit.CreatedOn) 
		  
		      
	        SELECT @Success = 1,
		           @Message = 'Successfully retrieved General Project Report data by loans ProjectType'         

	END TRY
	
	BEGIN CATCH
	
		    SELECT @Success = 0,
			       @Message = 'An error occurred retrieving General Project Report data by loans Project Number, the error was:' + ERROR_MESSAGE()
	END CATCH
END

Open in new window

0
Comment
Question by:Subbu G
  • 2
  • 2
4 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
you can add the TOP clause :

			SELECT TOP 1
			      -- Report Top Part
				  Project.ProjectNumber,
			      Project.ApplicantName,
			      Project.SpecificLocation,	
			      ProjectAssignmentInformation.[Description],

Open in new window

0
 

Author Comment

by:Subbu G
Comment Utility
Eric , thanks for your reply. I dont want to show just the first record, I want the one which has the most recent dates on this field (Permit.CreatedOn ) As NPDESIssueDate
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
Comment Utility
just be sure to include the ORDER BY clause:

			SELECT TOP 1
			      -- Report Top Part
				  Project.ProjectNumber,
			      Project.ApplicantName,
			      Project.SpecificLocation,	
			      ProjectAssignmentInformation.[Description],
....
			ORDER BY Permit.CreatedOn DESC

Open in new window

1
 

Author Closing Comment

by:Subbu G
Comment Utility
thanks Eric. This one helps
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

772 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

11 Experts available now in Live!

Get 1:1 Help Now