I am getting multiple results in my Max query such as this:
Company PolicyExpDate
CompanyA 1/17/20
CompanyA 1/17/19
I only need the line with the Max date to appear in the report. Here is my query as of now:
SELECT
tblContractor.CompanyName
,tblContractor.BMSIContractorID AS [tblContractor_BMSIContractorID]
,tblInsurance.BMSIContractorID AS [tblInsurance_BMSIContractorID]
,Max(tblInsurance.PolicyExpDate) AS MaxPolicyExpDate
FROM
tblContractor
INNER JOIN tblInsurance
ON tblContractor.BMSIContractorID = tblInsurance.BMSIContractorID
GROUP BY tblContractor.CompanyName, tblContractor.BMSIContractorID, tblInsurance.BMSIContractorID, tblInsurance.PolicyExpDate
I've tried a couple things such as an ON statement after Group By but I'm not thinking clearly on this. Thanks for any help!
;with src
as
(
SELECT tblContractor.CompanyName
,tblContractor.BMSIContractorID AS [tblContractor_BMSIContractorID]
,tblInsurance.BMSIContractorID AS [tblInsurance_BMSIContractorID]
,ROW_NUMBER() OVER (
PARTITION BY CompanyName
ORDER BY tblInsurance.PolicyExpDate desc
) row_num
FROM tblContractor
INNER JOIN tblInsurance
ON tblContractor.BMSIContractorID = tblInsurance.BMSIContractorID
)
select *
from src
where row_num = 1
1 Like
SELECT
tblContractor.CompanyName
,tblContractor.BMSIContractorID AS [tblContractor_BMSIContractorID]
,tblInsurance.BMSIContractorID AS [tblInsurance_BMSIContractorID]
,tblInsurance.PolicyExpDate AS MaxPolicyExpDate
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY BMSIContractorID ORDER BY PolicyExpDate DESC) AS row_num
FROM tblInsurance
) AS tblInsurance
INNER JOIN
tblContractor
ON tblContractor.BMSIContractorID = tblInsurance.BMSIContractorID AND tblInsurance.row_num = 1
WHERE tblInsurance.row_num = 1
2 Likes
Scott, your solution is what I needed, thank you! And thank you Yosiasz as well.
Scott, if I wanted to add two more date columns to the query where I would also be seeking those Max dates what would that query look like?
Such as:
Company PolicyExpDateA PolicyExpDateB PolicyExpDateC
You're welcome!
Give this a shot:
SELECT
tblContractor.CompanyName
,tblContractor.BMSIContractorID AS [tblContractor_BMSIContractorID]
,tblInsurance.BMSIContractorID AS [tblInsurance_BMSIContractorID]
,tblInsurance.PolicyExpDateA
,tblInsurance.PolicyExpDateB
,tblInsurance.PolicyExpDateC
FROM (
SELECT
BMSIContractorID
,MAX(CASE WHEN row_num = 1 THEN PolicyExpDate END) AS PolicyExpDateA
,MAX(CASE WHEN row_num = 2 THEN PolicyExpDate END) AS PolicyExpDateB
,MAX(CASE WHEN row_num = 3 THEN PolicyExpDate END) AS PolicyExpDateC
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY BMSIContractorID ORDER BY PolicyExpDate DESC) AS row_num
FROM tblInsurance
) AS query1
WHERE row_num BETWEEN 1 AND 3
GROUP BY BMSIContractorID
) AS tblInsurance
INNER JOIN
tblContractor
ON tblContractor.BMSIContractorID = tblInsurance.BMSIContractorID
--ORDER BY CompanyName
--ORDER BY tblInsurance.BMSIContractorID
1 Like