Geek asks and answers

The problem with joining yourself

I have a tblAccInfo table, the table data is below. I need the output as shown 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 tried to join on my own, but failed. Please help me with 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)
+5
source share
3 answers

Try the following:

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 how it works on the web: sqlfiddle

+4
source 
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;
+1
source

not tested - but should give you this idea. (Maybe the more efficient way to do this was just the approach that popped up on 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
0
source

More articles:


All Articles