T-SQL (Transact-SQL): Two separate tables. Need the find the date that is >= in Table A based on-CodePudding

Two separate tables. Need the find the date that is >= in Table A based on a date in Table B. Only TransactionCode 59 in Table A should be considered.

From the example tables below my return in table B First_Tran_Date should be "01/22/2022." Table A contains over 35 million records with thousands of AccountNumber's and grows each day.

Need T-SQL to take Table B ChangeDate "01/21/2022" and find the first time Table A shows a TransactionDate on or after that date and only TransactionCode 59 counts. All other TransactionCode dates should not be evaluated for the return.

Table A:

AccountNumber     TransactionDate     TransactionCode
xxxx310           2/3/2022            40
xxxx310           1/19/2022           40
xxxx310           1/22/2022           59
xxxx310           1/10/2022           59
xxxx310           3/15/2022           40
xxxx310           1/25/2022           59
xxxx310           1/30/2022           40
xxxx310           1/31/2022           59
xxxx310           1/31/2022           62
xxxx310           3/8/2022            59

Table B:

Account     ChangeDate     First_Tran_Date     COUNT_OF_DAYS
xxxx310     01/21/2022     **RESULT NEEDED**   (Calculated First_Tran_Date - ChangeDate = COUNT_OF_DAYS)

I have tried the following without getting a correct result:

T-SQL example...

Created a VIEW…

WITH added_row_number AS (

               SELECT

                              *,

                              ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY TransactionDate) AS row_number

 

 

FROM dbo.LoanTransactions

)

 

 

SELECT

               *

FROM added_row_number

 

WHERE

               row_number = 1

               AND TransactionDate >= '2022-03-01'

               AND TransactionCode IN ('59','61','70','77','82')

 

 

Used a SELECT from that VIEW…

 

SELECT

               DISTINCT Account

               ,Prod_CD

               ,OldValue

               ,NewValue

               ,Acct_Open_DT

               ,ChangeDate

               ,LOSVIEW_All_Transactions_From_CORE1.TransactionDate AS First_Tran_Date

               ,LastTransactionDate

 

 

               ,CASE

                              WHEN Prod_CD IN ('L50','L51','L54','L77') THEN DATEDIFF(DAY,ChangeDate,LOSVIEW_All_Transactions_From_CORE1.TransactionDate)

                              ELSE DATEDIFF(DAY,Acct_Open_DT,ChangeDate)

               END AS COUNT_OF_DAYS

 

 

FROM dbo.R_InsuranceCodeChanges

LEFT JOIN

               dbo.LOSVIEW_All_Transactions_From_CORE AS LOSVIEW_All_Transactions_From_CORE1 ON dbo.R_InsuranceCodeChanges.Account = LOSVIEW_All_Transactions_From_CORE1.AccountNumber

 

 

WHERE

               dbo.R_InsuranceCodeChanges.ChangeDate >= '2022-01-01'

               AND dbo.R_InsuranceCodeChanges.NewValue <> '0'

CodePudding user response：

If I understand correctly you want to look up two dates, and also calculate the number of days between them?

Those dates should be the first and last time a row with TransactionCode 59 appears, for a given account, and only including records on or after a given date?

So for the data in your example, the missing date should be 2022-01-22? Then the number of days would be 52 days?

For that I would use OUTER APPLY; which allows you to effectively run a query once for each input row...

SELECT
  *,
  DATEDIFF(DAY, table_a.MinTransactionDate, table_a.MaxTransactionDate)
FROM
  table_b
OUTER APPLY
(
  SELECT
    MIN(TransactionDate) AS MinTransactionDate,
    MAX(TransactionDate) AS MaxTransactionDate
  FROM
    table_a
  WHERE
        AccountNumber    = table_b.Account
    AND TransactionDate >= table_b.ChangeDate
    AND TransactionCode  = 59
)
  AS table_a

CodePudding user response：

Should be an index on tableA.TransactionDate. But I just translated your words to SQL and this is what I got:

select min(TransactionDate) as minDate
from tableA
where TransactionCode = 59
and TransactionDate >= (select max(TransactionDate) from tableB)