Subbu G
asked on
SQL Server 2008 Query Issue - How to select just one row
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.
How can I fix my SP to bring one instead of 3. because every column value is going to be same.
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks Eric. This one helps
Open in new window