Self join issue

Question

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)

Answer 1

not tested - but should give you the idea. (There may be a more efficient way of doing this - it was just the approach that jumped out at me.)

select initial.policynumber
,initial.initialbankaccoutn
,initial.initialstoragedate
,initial.intialvernum
,final.latestbankaccount
,final.lateststoragedate
,final.latestvernum
from 
(select a.policynumber, a.bankaccount as initialbankaccount, a.storagedate as initialstoragedate, a.vernum as initialvernum
from tblAccInfo a1
    inner join (select min(storagedate) as storagedate, policynumber
               from tblAccInfo 
               group by policynumber) a
               on a.policynumber = a1.policynumber
               and a.storagedate = a1.storagedate) initial
inner join 
(select b.policynumber, b.bankaccount as latestbankaccount, b.storagedate as lateststoragedate, b.vernum as latestvernum
from tblAccInfo b1
    inner join (select min(storagedate) as storagedate, policynumber
               from tblAccInfo 
               group by policynumber) b
               on a.policynumber = b1.policynumber
               and a.storagedate = b1.storagedate) final
on final.policynumber = initial.policynumber

Answer 2

DECLARE @x TABLE 
(
  PolicyNumber VARCHAR(32), 
  BankAc       INT, 
  StorageDate  DATE, 
  VerNum       INT
);

INSERT @x VALUES
('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);

WITH x AS
(
    SELECT PolicyNumber, BankAc, StorageDate, VerNum,
      f = ROW_NUMBER() OVER (PARTITION BY PolicyNumber ORDER BY VerNum),
      l = ROW_NUMBER() OVER (PARTITION BY PolicyNumber ORDER BY VerNum DESC)
    FROM @x
)
SELECT 
    x.PolicyNumber, 
    InitialBankAc   = x.BankAc, 
    InitialSDate    = x.StorageDate,
    InitialVerNum   = x.VerNum,
    LatestBankAc    = x2.BankAc, 
    LatestSDate     = x2.StorageDate,
    LatestVerNum    = x2.VerNum
FROM x INNER JOIN x AS x2
ON x.PolicyNumber = x2.PolicyNumber
WHERE x.f = 1 AND x2.l = 1
ORDER BY x.PolicyNumber;

Answer 3

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