Self join issue

self-join, sql, sql-server, sql-server-2008, t-sql

Solution

Try this:

SELECT
    T1.PolicyNumber,
    T2.BankAc AS IntialBankAc,
    T2.StorageDate AS IntialSDate,
    T2.VerNum AS InitalVerNum,
    T3.BankAc AS LatestBankAc,
    T3.StorageDate AS LatestSDate,
    T3.Vernum AS LatestVerNum
FROM
(
    SELECT
        PolicyNumber,
        MIN(VerNum) AS MinVerNum,
        MAX(VerNum) AS MaxVerNum
    FROM tblAccInfo
    GROUP BY PolicyNumber
) AS T1
JOIN tblAccInfo AS T2
ON T1.PolicyNumber = T2.PolicyNumber
AND T1.MinVerNum = T2.VerNum
JOIN tblAccInfo AS T3
ON T1.PolicyNumber = T3.PolicyNumber
AND T1.MaxVerNum = T3.VerNum

See it working online: sqlfiddle

Problem

I have a table called tblAccInfo, below is the table data. I need output like below. ``` Input PolicyNumber BankAc StorageDate VerNum 6003210400 123 2012-01-01 1 6003210400 164 2012-01-03 2 6003210400 860 2012-01-05 3 6004317654 301 2012-02-05 1 6004317654 615 2012-03-01 2 6004317654 253 2012-03-12 3 6004317654 887 2012-04-03 4 ``` OUTPUT ``` PolicyNumber IntialBankAc IntialSDate VerNum LatestBankAc LatestSDate VerNum 6003210400 123 2012-01-01 1 860 2012-01-05 3 6004317654 301 2012-02-05 1 887 2012-04-03 4 ``` I have tried with below self join, but did not succeeded. Please help me out in this. ``` Select DISTINCT P.PolicyNumber, P.BankAc [IntialBankAc], P.StorageDate IntialSDate], P.VerNum, P1.BankAc [LatestBankAc], P1.StorageDate [LatestSDate], P1.VerNum FROM tblAccInfo P INNER JOIN tblAccInfo P1 ON P1.PolicyNumber=P.PolicyNumber AND (P.BankAc<>P1.BankAc AND P.StorageDate<>P1.StorageDate AND P.VerNum<>P1.VerNum) ```

Original source