I'm trying to join two tables together, that only share an "Account Number" as a joining field between them. One table (Table1) is static and contains just one record per account number, but the other joining table (Table2) contains multiple Date records per account number. Using a Temp Table, I'm trying to first pull distinct Date records from Table2 and then join them to Table1 via the Account Number.

    CAST(tlpsf.ACCOUNT_NUMBER AS NCHAR(16)) AS psAccountNumber
    ,tlpsf.APPL_ID AS psApplID
    --,MIN(CONVERT(VARCHAR(10),tlpsf.DATE_PYMT_DUE_FIRST,120)) AS psDatePmtDueFirst
    ,MIN(CAST(tlpsf.DATE_PYMT_DUE_FIRST AS datetime2)) AS psDatePmtDueFirst
    ,tlpsf.NBR_OF_PMTS_THIS_SCHEDULE AS psNbrPmtsThisSchedule
    , CAST(tlpsf.PAYMENT_SCHEDULE_AMOUNT AS NUMERIC(17,2)) AS psPmtScheduleAmt
    , tlpsf.PAYMENT_SCHEDULE_TYPE AS psPmtScheduleType
    , tlpsf.ROW_PROCESSED_THRU AS psRowProcessedThru

INTO #loanpmtschedules
WHERE tlpsf.ROW_PROCESSED_THRU = @strLastofMonth

However, for some reason my Temp table build is still pulling in multiple date records, per account number.


Here is an example of using ROW_NUMBER to assign a row number to each row in Table2 grouped by AccountNumber and ordered by PaymentDate. We are then able to join Table1 with Table2 on AccountNumber where the row number from Table2 is 1 meaning the minimum payment date for each account.

--demo setup
Declare @Table1 table (AccountNumber int)
Declare @Table2 table (AccountNumber int, PaymentDate date, OtherStuff varchar(50))

insert into @Table1 (AccountNumber) values(1)
insert into @Table1 (AccountNumber) values(2)
insert into @Table2 (AccountNumber, PaymentDate, OtherStuff) values(1,'2019-01-01','jan')
insert into @Table2 (AccountNumber, PaymentDate, OtherStuff) values(1,'2019-02-01','feb')
insert into @Table2 (AccountNumber, PaymentDate, OtherStuff) values(1,'2019-02-01','mar')

insert into @Table2 (AccountNumber, PaymentDate, OtherStuff) values(2,'2019-05-01','may')
insert into @Table2 (AccountNumber, PaymentDate, OtherStuff) values(2,'2019-06-01','jun')
insert into @Table2 (AccountNumber, PaymentDate, OtherStuff) values(2,'2019-07-01','jul')

SELECT t1.AccountNumber
FROM @Table1 t1
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY AccountNumber ORDER BY PaymentDate
            ) AS rn
    FROM @Table2
    ) t2
    ON t2.AccountNumber = t1.AccountNumber
WHERE t2.rn = 1

| AccountNumber | PaymentDate | OtherStuff |
| 1             | 2019-01-01  | jan        |
| 2             | 2019-05-01  | may        |
  • Wow, I've definitely never seen that approach before but it worked PERFECT. I have soooo much to learn. THANK YOU! – Mike Jones Nov 14 at 22:06
  • You are welcome. – Scott Hodgin Nov 14 at 22:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.